Activation combobox problem.

  • Thread starter Thread starter chris
  • Start date Start date
C

chris

I have (from the sheet toolbar: view - toolbars - VB)
placed some comboboxes on a sheet. I wrote this code to
fill the boxes when the workbook is opened. My problem is
that when I open the file, the combobox don't fire off. I
have tried worksheet activation(won't fire off), change
(when i click outside the combobox, selection disappears)
and selectionchange(same as last). When I create a form
and put in comboboxes and write similar code, it works in
the activation, but it will not work for combobox on a
sheet. There aren't to many choices for a worksheet so I
don't have a clue of what to do to make this code fire off
when I open this file. Can anybody tell me what I am
doing wrong? Please let me know what I can do to make
this code fire off when I open the file so the user can
make a selection.



Sub Worksheet_Activate()


Me.Location.AddItem "IC187"
Me.Location.AddItem "IC207"
Me.Location.AddItem "KYD20"
Me.Location.AddItem "KY889"
Me.Location.AddItem "KY400"
Me.Location.AddItem "IC461"


Me.ValPlan.AddItem ""
Me.ValPlan.AddItem "n/a"
Me.ValPlan.AddItem "To Techwriter for creation"
Me.ValPlan.AddItem "To Techwriter for revision"
Me.ValPlan.AddItem "To Validation Coordinator for review"
Me.ValPlan.AddItem "To CSQC for pre-approval"
Me.ValPlan.AddItem "To Validation Coordinator for signoff"
Me.ValPlan.AddItem "To System Custodian for signoff"
Me.ValPlan.AddItem "To System Owner for signoff"
Me.ValPlan.AddItem "To CSQC for signoff"
Me.ValPlan.AddItem "Lab working on it"
Me.ValPlan.AddItem "APPROVED"

.........
 
Hi Chris

Worksheet_activate fires when you click the sheet tab after spending a great time in
another sheet within the same workbook. Workbook_open (in the ThisWorkbook module, explore
its possibilities) fires when the file is opened. To use that, move all code and replace
"Me" With "Sheets(1)" or wherever the thing resides.

As for userforms, you should imo use the Userform_Initialize event instead, it's the
"build me" event.

See Chip's page http://www.cpearson.com/excel/events.htm for a great list of events.
 
Back
Top