another VBA snafu

juster21

Well-known Member
Joined
Jun 3, 2005
Messages
867
I am trying to set the values on a form to the generic value. This may just be a syntax error but I can't figure it out.

Code:
Private Sub Workbook_Open()
Dim wks As Excel.Worksheets
Dim rng As Range

    'cboAetnaID.Value = wks("Info").rng("A2")
    'lstPriority.ListIndex = 0 'Worksheets("Info").Range("C2")
    'txtEffdate.Text = "##/##/##"
    cboFunction.Value = wks("info").rng("D2")
    cboSystem.Value = wks("Info").rng("E2")
    cboJobTitle.Value = wks("Info").rng("F2")
    cboJobTitle.Value = Worksheets("Info").Range("F2")
  
End Sub

I get a variable not defined error on "cboFunction.Value = wks("info").rng("D2")"
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
You have a few problems!

If "cob-Whatever" is a variable then you do not need the "Value"
If "cob-Whatever" is a Named Range then is is:

ActiveSheet.Range("cob-whatever").Value = Worksheets("Info").Range("F2").Value

cboJobTitle.Value = wks("Info").rng("F2")
cboJobTitle.Value = Worksheets("Info").Range("F2")

are both the same!
 

juster21

Well-known Member
Joined
Jun 3, 2005
Messages
867
not sure I understand what you mean. so, let's say I have a combo list box which pulls its values from A2:A10 on the sheet named Info. When the sheet is opened I want the default to be the A2 value every time. What do i need to change in my code to do this? Thanks again!!
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You have declared 2 object variables but you haven't assigned them an object with the Set keyword. What's wrong with?

cboFunction.Value = Worksheets("info").Range("D2")
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539

ADVERTISEMENT

This will erase the contents of the combox and replace it with a cell value:

ComboBox1.Text = Sheets("Sheet1").Range("B12").Text
 

juster21

Well-known Member
Joined
Jun 3, 2005
Messages
867
How about this....I have a reset button which sets ALL combo boxes to the generic value. How would I "click" this button when the workbook is opened?
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Andrew, may I expound upon the point you made?

Juster, if you select View | Locals Window from the VBE menu and step through the code using the F8 key, you will see wks and rng listed as declared variables, but both have nothing as their values; because, as Andrew points out, you have not Set them to anything. You can just use the code Andrew (and later Joe) posted, i.e. name the worksheets collection and the range object directly.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,111
Messages
5,570,257
Members
412,313
Latest member
pauloalex
Top