Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Embedded Combobox (Form Control)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Embedded Combobox (Form Control)
Sorry... that is an ActiveX control embedded on the sheet.
"Shane" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Embedded Combobox (Form Control)
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? "Shane" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Embedded Combobox (Form Control)
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 wrote: 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? "Shane" wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Embedded Combobox (Form Control)
Thank you so much!
"Dave Peterson" wrote: 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 wrote: 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? "Shane" wrote: 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 -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
form control combobox | Excel Discussion (Misc queries) | |||
Looking for a Form Control that combines ComboBox and CheckBoxes | Excel Programming | |||
Disable and enable dropdown combobox(Form Control) | Excel Discussion (Misc queries) | |||
Disable Dropdownlist (Combobox - "Form Control") | Excel Programming | |||
Dim CTL as MSForms.Control always sets as Control/Combobox | Excel Programming |