Embedded Combobox (Form Control)

[Shane]

On a sheet I have an embedded from control that, upon clicking a
commandbutton, I want the value of the combobox copied to another sheet.
Currently, the code below has "Empty" as the value of the all of the
comboboxes (named "CBCell" even though the properties window shows the text
and value as my selection. What am I missing in the following code?

Sub Button120_Click()
Dim WS As Worksheet
Set WS = ThisWorkbook.Sheets("Q-Investment Rebalancing")
Sheets("Client Agenda").Visible = True
Sheets("Client Agenda").Range("CAOverallRange").Value = ""
With Sheets("Client Agenda")
.Range("CAopt1").Value = WS.Range("c18").Value
.Range("CAopt2").Value = WS.Range("c19").Value
.Range("CAopt3").Value = CBCell20
.Range("CAopt4").Value = CBCell21
.Range("CAopt5").Value = CBCell22
.Range("CAopt6").Value = CBCell23
.Range("CAopt7").Value = CBCell24
.Range("CAopt8").Value = CBCell25
.Range("CAopt9").Value = CBCell26
.Range("CAopt10").Value = CBCell27
.Range("CAopt11").Value = CBCell28
.Range("CAopt12").Value = CBCell29
.Range("CAopt13").Value = WS.Range("C30").Value
End With
Sheets("Q-Investment Rebalancing").Select
End Sub

 

[Shane]

Sorry... that is an ActiveX control embedded on the sheet.

 

[Shane]

While playing around on this issue, I noticed that I could link the combobox
to the cell which it is on top of and then copy THAT cell. Is that the best
idea, or does someone know of a better way to do it?

 

[Dave Peterson]

You could use the linked cell, but you don't need to.

You could pick up the value from the combobox with something like:

..Range("CAopt3").Value = WS.OLEObjects("CBCell20").Object.Value

(CBCell## are the names of the comboboxes, right?)

In fact, since you used nice names, you could loop through the ranges and
comboboxes with something like:

Option Explicit
Sub Button120_Click()
Dim WS As Worksheet
Dim iCtr As Long
Set WS = ThisWorkbook.Sheets("Q-Investment Rebalancing")
Sheets("Client Agenda").Visible = True
Sheets("Client Agenda").Range("CAOverallRange").Value = ""
With Sheets("Client Agenda")
.Range("CAopt1").Value = WS.Range("c18").Value
.Range("CAopt2").Value = WS.Range("c19").Value
For iCtr = 3 To 12
.Range("CAopt" & iCtr).Value _
= WS.OLEObjects("CBCell" & iCtr + 17).Object.Value
Next iCtr
.Range("CAopt13").Value = WS.Range("C30").Value
End With
Sheets("Q-Investment Rebalancing").Select
End Sub

ps. If the name of the worksheet holding the button and the comboboxes is
"Q-Investment rebalancing", you don't need the WS variable.

You can use VBA's Me keyword. Me refers to the object that owns the code--in
this case, the worksheet.

Option Explicit
Sub Button120_Click()

Dim iCtr As Long

With Sheets("Client Agenda")
.Visible = True
.Range("CAOverallRange").Value = ""

.Range("CAopt1").Value = Me.Range("c18").Value
.Range("CAopt2").Value = Me.Range("c19").Value
For iCtr = 3 To 12
.Range("CAopt" & iCtr).Value _
= Me.OLEObjects("CBCell" & iCtr + 17).Object.Value
Next iCtr
.Range("CAopt13").Value = Me.Range("C30").Value
End With
me.select
End Sub

And there isn't any reason for your code to make that worksheet visible--unless
you want to show the user. (And I'm not sure what worksheet is getting
selected...)

 

[Shane]

Thank you so much!

You could use the linked cell, but you don't need to.

You could pick up the value from the combobox with something like:

..Range("CAopt3").Value = WS.OLEObjects("CBCell20").Object.Value

(CBCell## are the names of the comboboxes, right?)

In fact, since you used nice names, you could loop through the ranges and
comboboxes with something like:

Option Explicit
Sub Button120_Click()
Dim WS As Worksheet
Dim iCtr As Long
Set WS = ThisWorkbook.Sheets("Q-Investment Rebalancing")
Sheets("Client Agenda").Visible = True
Sheets("Client Agenda").Range("CAOverallRange").Value = ""
With Sheets("Client Agenda")
.Range("CAopt1").Value = WS.Range("c18").Value
.Range("CAopt2").Value = WS.Range("c19").Value
For iCtr = 3 To 12
.Range("CAopt" & iCtr).Value _
= WS.OLEObjects("CBCell" & iCtr + 17).Object.Value
Next iCtr
.Range("CAopt13").Value = WS.Range("C30").Value
End With
Sheets("Q-Investment Rebalancing").Select
End Sub

ps. If the name of the worksheet holding the button and the comboboxes is
"Q-Investment rebalancing", you don't need the WS variable.

You can use VBA's Me keyword. Me refers to the object that owns the code--in
this case, the worksheet.

Option Explicit
Sub Button120_Click()

Dim iCtr As Long

With Sheets("Client Agenda")
.Visible = True
.Range("CAOverallRange").Value = ""

.Range("CAopt1").Value = Me.Range("c18").Value
.Range("CAopt2").Value = Me.Range("c19").Value
For iCtr = 3 To 12
.Range("CAopt" & iCtr).Value _
= Me.OLEObjects("CBCell" & iCtr + 17).Object.Value
Next iCtr
.Range("CAopt13").Value = Me.Range("C30").Value
End With
me.select
End Sub

And there isn't any reason for your code to make that worksheet visible--unless
you want to show the user. (And I'm not sure what worksheet is getting
selected...)