populating combobox

[d0wnt0wn]

after much work and tons of help from you good folks i have finally managed to make a combobox populate all of my scenarios..... this may be the greatest tool i have for my estimate sheets.......

one little quirk tho... before the list will populate i have to click on another worksheet tab... when i click back the list is there... is there any way around this???? so that it populates as soon as the sheet is open... generally this is the first thing the estimator selects upon opening

 

[Smitty]

Without seeing how you're populating the Combo Box, this is a stab, but maybe you can put your event in the Worksheet_Activate event.

Smitty

 

[HalfAce]

I'm guessing the ComboBox gets loaded by the sheet activation event, and it's on sheet1 (or whatever sheet the workbook is opening on).
If this is true, you could put this in your Workbook Open event. (Code goes into the ThisWorkbook module...)

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Sheets("Sheet2").Activate
Sheets("Sheet1").Activate
Application.ScreenUpdating = True
End Sub

If your ComboBox is not on sheet1, simply replace sheet1 in the code with whatever sheet it's on.

Was that the problem?
Dan

 

[Zack Barresse]

Hi,

Can you let us know what kind of ComboBox it is? How it is currently populated?


(Hiya Smitty!)

 

[d0wnt0wn]

halface i put your code in and made adjustments as follows but i get an ambiguous name error..... i know it is because i have two workbook open sub but i do not know how to incorporate them both.


rivate Sub Workbook_Open()
ARBUTUS.show
End Sub

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Sheets("shingle estimates").Activate
Sheets("flat estimates").Activate
Application.ScreenUpdating = True
End Sub



penny... want to thank you again for all of your help on Friday... I didnt send you that email because i worked like a bugger on this and managed to make a form that will enter materials and price...

fire... it is a control combobox and here is the code i used



Private Sub ComboBox1_Change()
On Error Resume Next
ActiveSheet.Scenarios(ComboBox1.Text).show
End Sub




Private Sub Worksheet_Activate()
Dim i As Integer
ComboBox1.Clear
For i = 1 To ActiveSheet.Scenarios.Count
ComboBox1.AddItem ActiveSheet.Scenarios(i).Name
Next
ComboBox1.Text = "bsb 2 ply"
End Sub

 

[HalfAce]

Well, Assuming your combobox is on the "flat estimates" sheet, you could incorporate them both like so:

Private Sub Workbook_Open() 
Application.ScreenUpdating = False 
Sheets("shingle estimates").Activate 
Sheets("flat estimates").Activate
Application.ScreenUpdating = True 
ARBUTUS.show 
End Sub

Does this help?
Dan

 

[Smitty]

This may do it for you:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
        Sheets("shingle estimates").Activate
        Sheets("flat estimates").Activate
        ARBUTUS.Show
        
        ComboBox1.Clear
        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> ActiveSheet.Scenarios.Count
            ComboBox1.AddItem ActiveSheet.Scenarios(i).Name
        <SPAN style="color:#00007F">Next</SPAN>
            ComboBox1.Text = "bsb 2 ply"
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Smitty

(Heya fellas!)

 

[Zack Barresse]

Okay, my 2¢, Dan's solution would probably be the easiest to incorporate in what you have currently. I would probably opt for something like Smitty's. I like to have things compartmentalized and in one place. The only thing I might change, and it would be for more of an asthetic view, would be ...


<font face=Tahoma New>        ComboBox1.Clear
        ComboBox1.AddItem "--Choose Scenario--"
        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Sheets("flat estimates").Scenarios.Count
            ComboBox1.AddItem Sheets("flat estimates").Scenarios(i).Name
        <SPAN style="color:#00007F">Next</SPAN>
        ComboBox1.ListIndex = 0</FONT>


Okay, shuttin' up now.

 

[d0wnt0wn]

penny just tried your... seems the box populates but i got runtime error 424 object required at this line ComboBox1.Clear

 

[Smitty]

I was assuming that the ComboBox was on the ARBUTUS form.

If it's not then you either need to call that form or delete that portion of code.

Smitty