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

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

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,022
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,546
Messages
5,838,029
Members
430,526
Latest member
NiceGuyWithExcel2007

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top