Excel VBA, Variable Object Name

[gman1979]

Hi Folks,
Hope someone out there will knwo the answer to this, i'm help referenced and googled out my head with this one.

i wasnt to create a loop to change object properties in my userform, the object type is always the same, i.e. a textbox, but the name is variant, i.e. textbox1, textbox2, textbox3 and so on

i've tried the fllowing (p.s. i don't know how to insert the code correctly on this page, so I apologise in advance)

RepeatMe()
Dim BNa as string
Dim BNo as intiger
Dim iName as variant
BNa = "TextBox"
BNo =1
iName = BNa & BNo
do while BNo < 25
iName.enable = true
iName.visible = true
BNo = BNo + 1
loop
end sub

i keep getting the following error, when i try this code "Object Required" i dopn't know if what i am trying to do is possible, i hope it is, any help would be brilliant. I'm using windows xp sp2 with xl2003

thanks in advance guy's

Gman

 

[xenou]

How many textboxes do you have?

 

[TFCJamieFay]

Hi,

Is it because integer is spelt wrong?!

Jay

 

[Norie]

Try something like this.

Me.Controls(iName).Enabled = True

 

[xenou]

Hi,

Is it because integer is spelt wrong?!

Yes, you should have received a compile error...


EDIT: [Deleted] Oops...Norie is on the right track...identify the control by its name.

 

[AlexG788]

I've come across this before... what i do is pass the object to another procedure.... ie,

You can pass an object to a procedure from a calling procedure like you would a variable.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

For example, if you have multiple combo boxes, and either one can call this procedure, you can state:

Public Sub ComboBox1_Change()<o></o>
Call AddItemsToObject(ComboBox1)<o></o>
End Sub<o></o>
----------------------------<o></o>
Public Sub ComboBox2_Change()<o></o>
Call AddItemsToObject(Combobox2)<o></o>
End Sub<o></o>
----------------------------<o></o>
Public Sub AddItemsToObject(NewCmbo as ComboBox)<o></o>
With NewCombo<o></o>
.Enter Code Here<o></o>
End With<o></o>
End Sub<o></o>
<o> </o>
This method allows you to channel both combobox’s into one object, depending on
which box is selected.
<o></o>

 

[gman1979]

Thanks guys,

all your help has been greatly appreciated, I used Nories' example and everything worked a treat, below is the final code that I settled on which achieved what I was trying to do (just in case this helps anyone else looking in on this thread).

Thanks again guy's, code is below:

Private Sub CommandButton1_Click()
'Define objects and values
Dim BNa As String
Dim BNo As Integer
Dim iName As Variant
BNa = "TextBox"
BNo = 1
iName = a & b
i = 1
'define 'dome'
dome:
'do while and conditions, in this case do while i is less than 21
Do While i < 21
'code to execute until condition is met
iName = BNa & BNo
Me.Controls(iName).SetFocus
Me.Controls(iName).BackColor = &H80000001
Me.Controls(iName).ForeColor = &H80000005
Me.Controls(iName).Value = iName
i = i + 1
BNo = BNo + 1
'end loop
Loop
Me.CommandButton1.SetFocus
End Sub


ps integer is spelt that way in excel vba
pps anyone tell me how to post code correctly in my threads

Thanks again guy's


Gman

 

[Norie]

Gman

In your original code you had 'intiger'.

Now I don't know if I'm missing something but declaring variables inside wild animals might be a bit dodgy.

As to posting the code 'correctly', take a look at the link in my sig.

 

[NDeanMeyer]

The following only works if it's already loaded:

' Declarations
Dim TargetForm As Object

' Scan for FormName (if Loaded)
For Each TargetForm In VBA.UserForms
If TargetForm.Name = FormName Then ' Found it
Goto Done
End If
Next TargetForm
Set TargetForm = Nothing ' Didn't find it
Done: