Execute multiple commands in Excel Macro By Changing One Number

Question

Context: I have 10 text boxes (ID1 To ID10) in a user form. The userform will also have a clear button, which will allow the user to clear all the values previously entered in the text box. For that I have inserted the below mentioned command in the Clear Button.

I have multiple commands with the same nomenclature except the number which varies with every text box. I wish to enter one command which will change number and execute all the commands.

Simple example given below: I wish to only put one command instead of the folloing:

Private Sub btnClear_Click()

'Empty ID1
ID1.Value = ""

'Empty ID2
ID2.Value = ""

'Empty ID3
ID3.Value = ""

. . . and so on till .

'Empty ID10
ID10.Value = ""

End Sub

I know there is a solution to this, but since I am new cannot find on google using the correct key words. Sorry if this already exists. Any help will be appreciated. Thank you.

What object type are `ID1`, `ID2`, `ID3`, and `ID4`? – YowE3K Apr 18 '17 at 06:31 — YowE3K, Apr 18 '17 at 06:31
@YowE3K - They are text boxes, I have refined my question. – Kaustav Apr 18 '17 at 10:54 — Kaustav, Apr 18 '17 at 10:54

score 0 · Answer 1 · answered Apr 18 '17 at 06:42

0

Do you mean something like this?

For i=1 to 4
    str="ID" & i & ".Value = ''"
    Evaluate(str)
Next

answered Apr 18 '17 at 06:42

ClintB

509
3
6

1

(a) you don't need the brackets around `str` in the syntax you have used - you aren't getting `Evaluate` to return a value (b) If you change it to return a value from `Evaluate` (e.g. `x = Evaluate(str)`) the return value will be an error condition because `"ID1.Value = ''"` is not a valid formula. (c) If it was a valid formula, it would be testing whether the left-hand side was `=` to the right-hand side, thus returning a logical value - it wouldn't be executing an assignment statement. – YowE3K Apr 18 '17 at 07:41
My mistake I thought it worked the same as MS Access Eval. Been a while since i used it. – ClintB Apr 18 '17 at 08:03
1

`Evaluate` and `Eval` work very similarly - neither give access to variables/objects within VBA - so you can't use them to perform assignments, or even to say something like `y = 5: x=Eval("y*6")` – YowE3K Apr 18 '17 at 08:20
@YowE3K, - It says arguement not possible. Also i have refined my question, if that helps you understand my question better. Thank you. – Kaustav Apr 18 '17 at 10:50
@ClintB - It says arguement not possible. Also i have refined my question, if that helps you understand my question better. Thank you. – Kaustav Apr 18 '17 at 10:52
@YowE3K - Thanks so much. Also, if I want to unselect option boxes when I press clear, what command to use then. Option Boxes are OB1,OB2, OB3....OB10. – Kaustav Apr 18 '17 at 11:09

YowE3K · Accepted Answer · 2017-04-18T11:31:14.117

0

If these are TextBoxes on a UserForm, you will be able to do the following from a macro in the UserForm's code module:

Private Sub btnClear_Click()
    Dim i As Long
    For i = 1 To 10
        Me.Controls("ID" & i).Value = ""
    Next 
End Sub

It's not possible to dynamically generate a variable and/or object name but, because the Controls collection can be indexed by the "name" of the control, it gives an alternative way of getting at the objects.

Adding in code for "option boxes" (OptionButtons? `CheckBoxes?) mentioned in comments, and changing the TextBox names to end with a "C":

Private Sub btnClear_Click()
    Dim i As Long
    For i = 1 To 10
        Me.Controls("ID" & i & "C").Value = ""
        Me.Controls("OB" & i).Value = False
    Next 
End Sub

edited Apr 18 '17 at 11:31

answered Apr 18 '17 at 10:54

YowE3K

23,852
7
26
40

Thanks so much. Also, if I want to unselect option boxes when I press clear, what command to use then. Option Boxes are OB1,OB2, OB3....OB10. – Kaustav Apr 18 '17 at 11:07
@Kaustav - answer edited to include code for "option boxes". – YowE3K Apr 18 '17 at 11:14
Okay, I got the option boxes too. But one more doubt. What if the name of my text box is ID1C, ID2C, ID3C. Its not working then. Me.Controls("ID" & i&"C")....is not working. – Kaustav Apr 18 '17 at 11:15
@Kaustav - The value being passed as the index of the collection is a string, which can be created in whatever way you need. If you need `ID1C`, `ID2C`, etc, then you can create a string of `"ID" & i & "C"`. Just saw the edit to your comment `Me.Controls("ID" & i&"C")` won't work because you don't have a space between `i` and `&`, which makes the compiler think you are trying to use the variable `i&`. (See http://stackoverflow.com/q/4958189/6535336 for what a variable `i&` means.) – YowE3K Apr 18 '17 at 11:18

Execute multiple commands in Excel Macro By Changing One Number

2 Answers2