# Solved: Excel VBA With TextBox and CheckBox



## SolarDiablo (Oct 1, 2007)

I have a list of check boxes in UserForm1.

I'd like to be able to look at the value of all of the text boxes using a loop. The problem is, I can't figure out how to use a variable to change from CheckBox1 to CheckBox2.

The code would be something like this:

For N = 1 to 20
If CheckBoxN.Value = "True" Then
ActiveCell.Value = TextBoxN.Value
ActiveCell.Offset(1,0).Select
End IF
Next N

Any ideas?
Thanks!
SolarDiablo


----------



## Zack Barresse (Jul 25, 2004)

Hi there,

Why don't you tell us why you're trying to loop first. This is fairly easy, but depending on what you're actually trying to do, there may be a better way. Such as, if you're trying to find which one is checked within a group as only one should be checked, radio buttons are what you should be using.


----------



## SolarDiablo (Oct 1, 2007)

I am using the form to enter data into the spreadsheet. The worksheet is protected so that operators can not change data already entered.

If CheckBox10 is selected, the data in three TextBoxes next to CheckBox10 will be entered into the spreadsheet.

All 26 boxes could be selected. Attached is a word document that gives a visual of what the UserForm looks like.

Thanks!

SD


----------



## Zack Barresse (Jul 25, 2004)

Are these forms textboxes or controls toolbox checkboxes? If this is Excel, why are you posting in Word? Can you not post the Excel file, or a sample of it? If you're needing the checkboxes, we don't need to see the data (unless it is relevant). We'll also need to know the names of the checkboxes along with what logic goes with what (i.e. if checkbox 1 is checked, what happens; if checkbox 2 is checked, what happens; etc).


----------



## SolarDiablo (Oct 1, 2007)

They are forms. The Word Document is what the operators fill out. The data will then be entered into the spreadsheet.

Here is the file. The button on the first sheet activates the UserFile. The code I want to replace with the loop is in the Case Select area.

Thanks!


----------



## SolarDiablo (Oct 1, 2007)

I just corrected some errors (typos). Attached is the corrected spreadsheet.

Gracias!


----------



## Zack Barresse (Jul 25, 2004)

I'm sorry, you've totally lost me. You show a Word file with the checkboxes which you want to run code on, then you show an Excel file. Something is missing here. Can you explain the entire process of the files you've posted, then restate your original question in the terms you've specified. The code you posted is from the Excel object model, but you've not told us what it is for (only scratched the surface).


----------



## Rollin_Again (Sep 4, 2003)

You can't use a variable name to refer to a control. Since VBA doesn't allow Control Arrays you must loop through each control on the form and evaluate each one.

Here is something to get you started. Just add the code to your button click event instead of it's own procedure. The code can be modified so that the *IF* statement can use a counter variable in conjunction with the control name to determine what action to take with each specific checkbox. I am also still a little confused like Zack as to what the relationship is between the Word doc and the Excel file is. Are you saying that the Word doc is completed seperately and then passed to someone else who will manually key the info into the Excel form??


```
Sub CheckBoxes()

For Each vControl In UserForm1.Controls

If TypeName(vControl) = "CheckBox" Then
MsgBox (vControl.Name & " Value Is " & vControl.Value)
End If

Next

End Sub
```
Regards,
Rollin


----------



## SolarDiablo (Oct 1, 2007)

1. Inspectors on the production floor fill out the Word document (hard copy) and give it to a data entry clerk.
2. The data entry clerk opens the Excel file that i posted.
3. The data entry clerk clicks on the button on the first worksheet opening the UserForm.
4. The data entry clerk enters the data into the form
5. The data entry clerk pushes the "Transfer Data" button which runs the VBA that moves the data to the appropriate tables.

I know this would be much easier to do in Access, but the people I'm trying to help here are timid and want nothing to do with it. So I'm trying to accomplish the same thing in Excel.

Thanks


----------



## SolarDiablo (Oct 1, 2007)

Rollin_Again said:


> I am also still a little confused like Zack as to what the relationship is between the Word doc and the Excel file is. Are you saying that the Word doc is completed seperately and then passed to someone else who will manually key the info into the Excel form??


Rollin'

Yes. The Word Doc is a hard copy file that is used on the floor and then passed to another individual for keying in.

Your code is very helpful! I was able to modify it to accomplish what I was trying to do. THANK YOU!!!!


----------

