Can I use a variable in a VBA reference?

[Docaroo]

Hi Guys,

I'm writing some VBA subroutines in an access database...

I want to have a routine which disables a box, etc ... but when I call the routine I would like to specifiy the name of the box in the call, is this possible? I've tried myself but get errors but I don't know the correct syntax though - google is a bit dry on this but i'll keep searching there in the meantime ... here's an example:

Private Sub someOtherSub

	Call disableButton (nextRecordButton)

End Sub



Private Sub disableButton (ButtonName as String)

	Me.ButtonName.Enabled = False

End Sub

Several things I think could be wrong... declaring the variable as string is porbably wrong? Although I tried declaring as As Object and that didn't work! I could also have the syntax in the Me. control wrong although I've tried quotes, square brackets and and signs (&) with no luck!

Does anyone know what is required to make this work?

Thanks guys!

 

[wozencl]

Firstly, you're passing a variable to a subroutine - this should be a Function as you'll probably want to return a value to show it's suceeded. Also, if you're going to pass it an Object it should be reflected in the declaration.

Depending on where the button is you should be able to access it using the usual dot notation. E.g. Form1.MyButton.Enabled = False, Sheets(sheetIndex).Cells(1,2).Value = "TEST"

Edit: If you're ever unsure about what to declare a variable as leave it blank, VB is very forgiving in this respect and will usually cast things on the fly.

 

[Docaroo]

....

Hi mate, thanks for the reply - I don't think I've explained enough so i'll have another try:

I'm making an Access form where I want to perform an action on a form object (say textbox or button or whatever).

However, the function I want to perform is going to be long, and I'll have to duplicate it for 6x buttons!!

Instead, I'd like to seperate the function/routine out on it's own and just call the routine for each different button with the button name changing however I can't find a way to make Access accept a variable name in the Me... control!

E.G. this is the test form I'm using to try and get it working:

disable1 is a button that disables a text box

Private Sub diasble1_Click()
	Call disableBox(text1)
End Sub

Private Sub disableBox(boxName As Object/Control?
	Me.boxName.Enabled = False
End Sub

In the disableBox sub... Me.Text1.Enabled = False should be run however I get errors using every type of syntax I can think of! Including Me.Controls(boxName).Enabled = False

Think that's a better way to put it!

 

[SimonCHere]

If you use

Private Sub diasble1_Click()
	Call disableBox me.TextBox1 'the actual object you want to disable.
End Sub

Private Sub disableBox(box As TextBox)
      box.Enabled = False
End Sub

It should work. If you need to use a string then

Private Sub disableBox(boxName As String)
      Me.Controls(boxName).Enabled = False
End Sub

Should work. if it doesn't what's the error?

 

[wozencl]

Ah I see, sorry the initial description made it sound a lot simpler. Are the buttons actually on a form?

 

[Docaroo]

...
It should work. If you need to use a string then

Private Sub disableBox(boxName As String)
      Me.Controls(boxName).Enabled = False
End Sub

Should work. if it doesn't what's the error?

ahhhh BRILLIANT!! That's exactly what I'm trying to do - just didn't know the syntax!!

here's my new working code:

Private Sub disable1_Click()
    Call disableBox("text1")
End Sub

Private Sub enable1_Click()
    Call enableBox("text1")
End Sub

Private Sub disableBox(boxName As String)
    Me.Controls(boxName).Enabled = False
        
End Sub

Private Sub enableBox(boxName As String)
    Me.Controls(boxName).Enabled = True
End Sub

This works perfectly! A button that disables a text box and one that enables it ... and these functions accept a variable String as the box name to disable...

Now I can put this in my main project!

Thanks a lot guys!

 

[SimonCHere]

Glad I could help. Just a thought, you might want to put some error handling in the disable/enable box subs just in case it can't find the control with the string that you pass it.

 

[Docaroo]

Glad I could help. Just a thought, you might want to put some error handling in the disable/enable box subs just in case it can't find the control with the string that you pass it.

Oh don't worry I've got that in there... there's an annoying error that can pop up if Nulls are involved...

I'm just working through all possible choices that affect the buttons to code them to a set amount... it'll work well though!