PDA

View Full Version : Help with Macro and Object Variable



RTR97
08-03-2012, 08:58 AM
Hello All,

I am developing a UserForm in VBA for Excel that allows a user to make a yes/no selection via a radio button which then activates a ComboBox and allows the user to select a number from 1 To 20, which directly makes TextBoxes appear corresponding to the number they select. The problem I am having is I keep getting a runtime error 91 "object variable or With block variable not set". I have a general understanding but only enough to make me dangerous. I would be grateful for input any of you could provide. Thank you.

If btnNo.Enabled = True Then
Dim ctrl As Control
For mytext = 1 To 20
ctrl = "txtMinBin." & mytxt
ctrl.Visible = False
ctrl = "txtMaxBin." & mytxt
ctrl.Visible = False
ctrl = "lblMassBin." & mytxt
ctrl.Visible = False
Next
End If
End Sub

Bob Phillips
08-03-2012, 09:10 AM
If they all have meaningful names, how do you know which textbox aligns to the value 1, or 2, or whatever?

RTR97
08-03-2012, 09:16 AM
If they all have meaningful names, how do you know which textbox aligns to the value 1, or 2, or whatever?

Well the initial code I posted is supposed to replace this:
'Setting textboxes for Minimum Bin Value to invisible
txtMinBin1.Visible = False
txtMinBin2.Visible = False
txtMinBin3.Visible = False
txtMinBin4.Visible = False
txtMinBin5.Visible = False
txtMinBin6.Visible = False
txtMinBin7.Visible = False
txtMinBin8.Visible = False
txtMinBin9.Visible = False
txtMinBin10.Visible = False
txtMinBin11.Visible = False
txtMinBin12.Visible = False
txtMinBin13.Visible = False
txtMinBin14.Visible = False
txtMinBin15.Visible = False
txtMinBin16.Visible = False
txtMinBin17.Visible = False
txtMinBin18.Visible = False
txtMinBin19.Visible = False
txtMinBin20.Visible = False
' Setting the textboxes for Maximum Bin Value to invisible
txtMaxBin1.Visible = False
txtMaxBin2.Visible = False
txtMaxBin3.Visible = False
txtMaxBin4.Visible = False
txtMaxBin5.Visible = False
txtMaxBin6.Visible = False
txtMaxBin7.Visible = False
txtMaxBin8.Visible = False
txtMaxBin9.Visible = False
txtMaxBin10.Visible = False
txtMaxBin11.Visible = False
txtMaxBin12.Visible = False
txtMaxBin13.Visible = False
txtMaxBin14.Visible = False
txtMaxBin15.Visible = False
txtMaxBin16.Visible = False
txtMaxBin17.Visible = False
txtMaxBin18.Visible = False
txtMaxBin19.Visible = False
txtMaxBin20.Visible = False
'Setting the labels to invisible
lblMinBinVal.Visible = False
lblMaxBinVal.Visible = False
lblMinBinVal1.Visible = False
lblMaxBinVal1.Visible = False
lblMassBin1.Visible = False
lblMassBin2.Visible = False
lblMassBin3.Visible = False
lblMassBin4.Visible = False
lblMassBin5.Visible = False
lblMassBin6.Visible = False
lblMassBin7.Visible = False
lblMassBin8.Visible = False
lblMassBin9.Visible = False
lblMassBin10.Visible = False
lblMassBin11.Visible = False
lblMassBin12.Visible = False
lblMassBin13.Visible = False
lblMassBin14.Visible = False
lblMassBin15.Visible = False
lblMassBin16.Visible = False
lblMassBin17.Visible = False
lblMassBin18.Visible = False
lblMassBin19.Visible = False
lblMassBin20.Visible = False
'Setting the combobox and its label to invisible
ComboBox1.Visible = False
lblComboBox1.Visible = False
'Sets the combobox index back to none when "no" button is selected
ComboBox1.ListIndex = -1
End If
End Sub

Bob Phillips
08-03-2012, 12:21 PM
Uumh? That was supposed to enlighten us? did nothing for me I am afraid.

mikerickson
08-03-2012, 12:59 PM
I think the syntax you are looking for is
Set ctrl = Me.Controls("txtMinBin" & mytxt)

Tinbendr
08-03-2012, 01:54 PM
For mytext = 1 To 20
Me.Controls("txtMinBin" & mytext).Visible = False
Me.Controls("txtMaxBin" & mytext).Visible = False
Me.Controls("lblMassBin" & mytext).Visible = False
Next

RTR97
08-06-2012, 09:18 AM
For mytext = 1 To 20
Me.Controls("txtMinBin" & mytext).Visible = False
Me.Controls("txtMaxBin" & mytext).Visible = False
Me.Controls("lblMassBin" & mytext).Visible = False
Next

Thanks Tinbendr,

This worked flawlessly!

My only question is could you explain why I have to declare "ctrl" As Control in order for this to work. The reason I ask is because "ctrl" isn't used in the statement except in this line. For example:

Dim ctrl As Control
For mytext = 1 To 1
Me.Controls("txtMinBin" & mytext).Visible = True
Me.Controls("txtMaxBin" & mytext).Visible = True
Me.Controls("lblMassBin" & mytext).Visible = True
Me.Controls("lblMinBinVal").Visible = True
Me.Controls("lblMaxBinVal").Visible = True

Next

Bob Phillips
08-06-2012, 12:07 PM
It's not in David's code, it is not needed.

RTR97
08-06-2012, 12:36 PM
It's not in David's code, it is not needed.

That's what I thought, thanks. Misunderstanding on my part.