Default value for ActiveX combo box when opening spreadsheet

[jeff_cp]

I have a spreadsheet with an ActiveX combo box in cell B5 that is linked to cell C5. I have a named range on another worksheet called "Parts_Entered" and my ListFillRange is pointed to it.

When I select a value from the drop down list in B5, I'm populating a date range in cells B6 and B7 based off the value in C5. I'd like the spreadsheet to open with cell B5 defaulted to "Today". Currently, cells B6 and B7 default to the correct dates so it's defaulting to "Today" (with a value of 0 in cell C5) but it's not showing the word "Today" in cell B5 when I open the spreadsheet.

Please help . Thanks!

 

[DanteAmor]

I have a spreadsheet with an ActiveX

Put the following code in the events of that sheet.

Private Sub Worksheet_Activate()
  ActiveSheet.ComboBox1.Value = "Today"
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[jeff_cp]

Thanks Dante but that didn't work. I followed your instructions, saved and reopened the spreadsheet and same thing...cell B5 is blank. I also tried setting the value to 0 instead of "Today" and that didn't work either.

 

[DanteAmor]

You just have to change sheets and return to the sheet that has the combobox.
By the way, is your combobox called "Combobox1"?

 

[DanteAmor]

If you want this to happen when you open the book then remove the above code and put the following code but in the events of the book.

Private Sub Workbook_Open()
  Sheets("Sheet1").ComboBox1.Value = "Today"
End Sub

Change Sheet1 to the name of the sheet containing the combo

ThisWorkbook EVENT

- Open the VB Editor (press Alt + F11).
- Over in the Project Explorer, double click on ThisWorkbook.
- In the white panel that then appears, paste the above code.

 

[jeff_cp]

Yeah I renamed my ComboBox1 to Parts_Entered but I adjusted your first code to accommodate for that. I tried your second code for ThisWorkbook but get a debug error when I open the spreadsheet. The error says:

Run-time error '-2147352571 (80020005)':
Could not set the Value property. Type mismatch.

I already have the following code in Private Sub Workbook_Open() so it uses an ODBC connection name of CHECKMATE when you open the report:

Private Sub Workbook_Open()
Dim TheConnectionName As String
For Each objWBConnect In ThisWorkbook.Connections
TheConnectionName = objWBConnect.Name
ThisWorkbook.Connections.Item(TheConnectionName).ODBCConnection.Connection = "ODBC;DSN=CHECKMATE"
Next objWBConnect
End Sub

So I added your line of code with my modifications like this:

Private Sub Workbook_Open()
Dim TheConnectionName As String
For Each objWBConnect In ThisWorkbook.Connections
TheConnectionName = objWBConnect.Name
ThisWorkbook.Connections.Item(TheConnectionName).ODBCConnection.Connection = "ODBC;DSN=CHECKMATE"
Next objWBConnect

Sheets("Inventory_Detail").Parts_Entered.Value = "Today"

End Sub

 

[DanteAmor]

I have a spreadsheet with an ActiveX combo

What is the name of that combo?
You can check it in the box name:

That name goes here:

Sheets("Inventory_Detail").ComboName.Value = "Today"

 

[jeff_cp]

My combobox is named Parts_Entered. That's why I used Sheets("Inventory_Detail").Parts_Entered.Value = "Today" in the code you provided. However, the formula box shows the following when I select the combobox:

=EMBED("Forms.ComboBox.1","")

 

[DanteAmor]

I updated my sheet and my combo with your names and I have no problems.
You could only run this part in your event:

Private Sub Workbook_Open()
Sheets("Inventory_Detail").Parts_Entered.Value = "Today"
End Sub

 

[jeff_cp]

Yeah I can't get it to work for some reason on mine. I commented out everything in ThisWorkbook but the following but I get the debug error every time I open the spreadsheet:

Private Sub Workbook_Open()
Sheets("Inventory_Detail").Parts_Entered.Value = "Today"
End Sub

I'll do some digging and see what I can figure out. Thanks.