how do i use this code please

[d0wnt0wn]

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 = "Select Scenario"
End Sub

Private Sub ComboBox1_Click()
On Error Resume Next
ActiveSheet.Scenarios(ComboBox1.Text).Show
End Sub


where do i put the pieces of code and how do i link a combobox to it... sorry for my stupidity

 

[dimbasco]

Am not an expert at VB.. but here's what you probavbly want to do..

Go to Tools > Macro > VB Editor
Paste the code. Save.

or

Tools > Macro> Record New Macro - (Name Macro)
Stop Recording Macro.
Tools> Macro> MAcros> Edit
Delete all.
Paste.

When you create a combo box, right click on it and assign macro.

 

[d0wnt0wn]

that didnt work for me i tried that but no macro appears

 

[Andrew Poulsom]

Right click the sheet tab an choose View Code. Paste the code into the window on the right. Press Alt+F11 to return to your worksheet.

 

[d0wnt0wn]

sorry i have tried every thing you guys have suggested but nothing i do seems to work......

a) am i supposed to put both subs in the same worksheet tab??


b) I still dont get how to link my combo box to the list


no new macros are made for me to link to

 

[d0wnt0wn]

sorry for the bump but i am so close to finishing this workbook and I am very anxious to get this solved because if i cant i will have to redo about 50 scenarios using =index or something..... my VB is very limited but I am sure this cant be a hard solution... i just need to know where to put the code... and how i link the combobox to the code

 

[alibax]

Hi!... Here is what you do.

(1) Load your spreadsheet (Excel)
(2) In the Excel go to View, Toolbars and then sect choose Control Toolbox, a little windows should pop out.
(3) Select Combo Box in the Control Toolbox and insert it into your sheet.
(4) Double click on the Combo box, and it should take you to the VBA console.
(5) You should see two lines like this below

Private Sub ComboBox1_Change()

End Sub

(6) Paste this code " On Error Resume Next
ActiveSheet.Scenarios(ComboBox1.Text).Show " without speach marks between those two lines.

(7) You may select " Worksheet" and then past another code or to make it easier; after your first code "combox1 code " below End sub; Paste this code:

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 = "Select Scenario"
End Sub


I hope that was bit helpful.....

PS. Lets us know...

 

[d0wnt0wn]

ok this is where im at..... i did everything that you asked... i now have a combobox on my sheet..... but.... 2 things happen to me now..... one if i click on the combobox it keeps bringing up the edit box around it..... then if i manage to get that to stop happening.... when i click on the combo box its blank... and i cant right click on it or anything....ugggggggg

still dont know how i get my list of scenarios to fill the combobox

 

[d0wnt0wn]

ok i have been mucking around with this quite a bit and i have at least been able to do this....

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
ActiveSheet.Scenarios("bsb 2 ply").Show
Else
ActiveSheet.Scenarios("t180").Show

End If
End Sub

this will activate two scenarios individually upon unchecking or checking a checkbox.... but i have 50 scenarios and a list box or combo box would be the ideal solution

 

[d0wnt0wn]

ok i ust figured out if i edit my code so the combobox text says bsb 2 ply and then type that into my combobox.. that scenario will appear.... first big problem is.... how do i get that bsb 2 ply to stay in the combobox list???? then how do i add more scenarios to the code ie combobox1.Text = "t180" as well

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