Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use a string as a form control name?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use a string as a form control name?
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use a string as a form control name?
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use a string as a form control name?
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 "fedude" wrote: 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2007 Form Control/ActiveX Control font difference | Excel Discussion (Misc queries) | |||
How to show User Form when form name is in string? | Excel Programming | |||
Tool Tip Text for Form control/ Active-X control | Excel Programming | |||
passing control value from one form to another form | Excel Programming | |||
Separate String into string + value form | Excel Programming |