Use a string as a form control name?

[fedude]

How can I change this routine to make it more generic? I want to enable a
form textbox (Sxxxx) depending on the value of a form checkbox (Qxxxx).
Basically I need to know how to use a string as a control name.

I tried creating a generic object using

set foo = OLEobject('Q'&'2222').object

but it failed with 'Sub or Function not Defined'

-----------------------------------------------
Sub EnableDisable()
Dim iUniqueNumberAs String

'capture the unique number....NOW WHAT DO I DO WITH IT??
iUniqueNumber= TEXT(Right(Q1062.Name, 4))

If Q1062.Value = True Then
S1062.Enabled = False

Else
S1062.Enabled = True

End If
End Sub

 

[Joel]

You have single quotes instead of double quotes. "Q" and "2222" are strings.
You don't need double quotes around 2222. excel know when you combine
characters and numbers the numbers get converted to strings

"Q" & 2222

for your other question you need to use the OLEObject to refer to a control
item by name. the same thing applies for a checkbox and a textbox. Both are
controls.

 

[fedude]

Joel,

I changed the double quote to single quote to make it readable by the web
reader.

I assume that OLEObject is the parent of all the form controls.

In another thread you suggested that I use OLEObject, but try as I might, I
cannot figure out how to use it Here is my last feeble attempt which fails
on the set statements. I'd appreciate any help.
-----------------------------------------

Private Sub BoxGroup_Click()
Dim player As Integer

MsgBox "Hello from " & BoxGroup.Name
player = Right(BoxGroup.Name, 4)
Set oName = OLEObject("L" & player).Object
Set oScore = OLEObject("S" & player).Object


If BoxGroup.Value = True Then
oName.Tag = oName.Caption
oName.Caption = "Substitute"
oScore.Enabled = False
Else
oName.Caption = oName.Tag
oScore.Enabled = True
oScore.Enabled = False
End If

End Sub

---------------------------------------------------

 

[Joel]

I think you were missing the 's' in OLEOBjects. I'm not sure what l & s are
so this code may not be exactly right.

I went back to the original code that I posted because it seems to be the
solution you are looking for. The group method has its limitation. I don't
know how to test which box triggers the group event. When the Group event
gets trigger I think you will have to process every check box. Lets go back
to the original code.

I'm work through the night in NJ right now. Will check for replies.





Private Sub Q104_Click()
Call common_click("104")
End Sub
Sub common_click(player As String)

Set CheckBox = OLEObjects("Q" & player).Object
Set TextBox = OLEObjects("L" & player).Object

'capture the unique number

TextBox.Value = ""
If CheckBox.Value = True Then
L1062.Tag = L1062.Caption
L1062.Caption = "Substitute"
S1062.Enabled = False
S1062.BackColor = &H8000000B
Else
L1062.Caption = L1062.Tag
S1062.Enabled = True
S1062.BackColor = &H80000005
End If
End Sub