another VBA snafu

[juster21]

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.

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

 

[Joe Was]

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]

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]

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]

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

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

 

[juster21]

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]

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.