Identify Comboboxes with Strings?

[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

 

[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)

 

[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

 

[Bob Phillips]

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?

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).

Of coursze, I got rid of it so it is no surprise I couldn't find it in my

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)?

That is probaly the best way, as they seem to be inter-related.

Thank you very much for the super quick reply
CB Hamlyn

It's a pleasure. Good luck, and post back if you get stuck again.

 

[Bob Phillips]

CB,

Thinking about this some more, we could use an index like so

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

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


--

HTH

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

 

[CB Hamlyn]

I'll read up on indexes... that might be exactly what I'm looking for.

I should have mentioned this before but these comboboxes are on a userform.
Something you said in your last post made me think you might think these are
comboboxes directly on the worksheet, which isn't the case. Does that make
a difference to the code below?

Thanks again
CB Hamlyn

 

[Bob Phillips]

Tons of difference. It's actually a lot simpler with userforms (we donn't
have the object container to worry about<vbg>)

Private Sub cboOSN_CarpetRoom1Name_Change()
ChangeCarpetRoomName 1
End Sub

Private Sub ChangeCarpetRoomName(idx As Long)
Dim ctlcboName As ComboBox
Dim ctlcboWeight As ComboBox
Set ctlcboName = Me.Controls("cboOSN_CarpetRoom" & idx & "Name")
Set ctlcboWeight = Me.Controls("cboOSN_CarpetRoom" & idx & "Weight")
If Len(ctlcboName.Value) <> 0 Then
Range("DLVF_CarpetR" & idx) = ctlcboName.Value
If Len(ctlcboWeight.Value) = 0 Then
Range("DLVF_CarpetW" & idx) = "40oz"
End If
Else
Range("DLVF_CarpetR" & idx) = ""
Range("DLVF_CarpetC" & idx) = ""
Range("DLVF_CarpetW" & idx) = ""
End If
End Sub


--

HTH

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

 

[CB Hamlyn]

There it is! Thank you very much. This is perfect.

CB Hamlyn