Identify Comboboxes with Strings?

C

CB Hamlyn

I have 42 comboboxes on a page. 14 RoomNames, 14 CarpetColors and 14
CarpetWeights.

I need to set some defaults, or blank out the colors/weights when someone
changes the room name. Under each of the cboRoom_X_Name_Change I have the
following code:

Private Sub cboOSN_CarpetRoom1Name_Change()
If Len(cboOSN_CarpetRoom1Name.Value) <> 0 Then
Range("DLVF_CarpetR1") = cboOSN_CarpetRoom1Name.Value
If Len(cboOSN_CarpetRoom1Weight.Value) = 0 Then
Range("DLVF_CarpetW1") = "40oz"
End If
Else
Range("DLVF_CarpetR1") = ""
Range("DLVF_CarpetC1") = ""
Range("DLVF_CarpetW1") = ""
End If
End Sub

I'd like to be able to have one procedure to do this work and simply pass
the number of the control to use. I'm trying something like this:

Private Sub cboOSN_CarpetRoom1Name_Change()
Call ChangeCarpetRoomName(1)
End Sub

Private Sub ChangeCarpetRoomName(NewCode)
Dim ctlcboName As Object
Dim ctlcboWeight As Object
ctlcboName = Controls("cboOSN_CarpetRoom1Name")
ctlcboWeight = "cboOSN_CarpetRoom" & NewCode & "Weight"
If Len(ctlcboName.Value) <> 0 Then
Range("DLVF_CarpetR" & NewCode) = ctlcboName.Value
If Len(ctlcboWeight.Value) = 0 Then
Range("DLVF_CarpetW" & NewCode) = "40oz"
End If
Else
Range("DLVF_CarpetR" & NewCode) = ""
Range("DLVF_CarpetC" & NewCode) = ""
Range("DLVF_CarpetW" & NewCode) = ""
End If
End Sub

This doesn't even come close to working, but I hope you can see what I'm
trying to do and can offer some help.

I used to program in FoxPro and they had a cool feature where you could
build a string like this:
strControlName = "cboCarpetRoom" & intControlNumber & "Name"
then do something like this:
&strControlName.Value = "Living Room"
Where the "&" told FoxPro to remove the quotes around the value of
strControlName and use it as an object. Does VB have anything like this?

Thanks for any help.
CB Hamlyn
 
B

Bob Phillips

CB,

Something like this?

I couldn't work it fully through as I have no idea what NewCode is

Private Sub cboOSN_CarpetRoom1Name_Change()
Call ChangeCarpetRoomName(Me.cboOSN_CarpetRoom1Name)
End Sub

Private Sub ChangeCarpetRoomName(cbo As msforms.ComboBox)
Dim ctlcboName As Object
Dim ctlcboWeight As Object
Set ctlcboName = ActiveSheet.OLEObjects(cbo.Name)
Set ctlcboWeight = "cboOSN_CarpetRoom" & NewCode & "Weight"
If Len(ctlcboName.Value) <> 0 Then
Range("DLVF_CarpetR" & NewCode) = ctlcboName.Object.Value
If Len(ctlcboWeight.Value) = 0 Then
Range("DLVF_CarpetW" & NewCode) = "40oz"
End If
Else
Range("DLVF_CarpetR" & NewCode) = ""
Range("DLVF_CarpetC" & NewCode) = ""
Range("DLVF_CarpetW" & NewCode) = ""
End If
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
C

CB Hamlyn

Thanks, I think I can work it from what you've given me.

I went through the help but had no idea what I was even looking for.
Controls? Objects? Comboboxes? You know?

NewCode is the number of the control. So for cboCarpetRoom12Name, NewCode
would be "12". This is how I address the several Cell Range Names
("DLVF_CarpetR12" as an example).

So where there's 2 different comboboxes being addressed (one for Name and
one for Weight) I'll need to pass three variables? cboCarpetRoomName,
cboCarpetRoomWeight and NewCode)?

Thank you very much for the super quick reply :)
CB Hamlyn
 
B

Bob Phillips

CB Hamlyn said:
Thanks, I think I can work it from what you've given me.

I went through the help but had no idea what I was even looking for.
Controls? Objects? Comboboxes? You know?
Click to expand...

The difficult part with worksheet controls is understanding that we have two
objects not one. There is an OLEObject object which as a container for the
ComboBox object. Thus combobox doesn't have a Value property, but the
combobox.object does.

NewCode is the number of the control. So for cboCarpetRoom12Name, NewCode
would be "12". This is how I address the several Cell Range Names
("DLVF_CarpetR12" as an example).
Click to expand...

Of coursze, I got rid of it so it is no surprise I couldn't find it in my
code ( said:
So where there's 2 different comboboxes being addressed (one for Name and
one for Weight) I'll need to pass three variables? cboCarpetRoomName,
cboCarpetRoomWeight and NewCode)?
Click to expand...

That is probaly the best way, as they seem to be inter-related.
Thank you very much for the super quick reply :)
CB Hamlyn
Click to expand...
It's a pleasure. Good luck, and post back if you get stuck again.
 
Top