Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to select active workbook worksheet(s) in Excel 2007
Code to select active workbook worksheet(s) in Excel 2007
Need help I have limited experience in VBA and have been looking online and in the Excel 2007 books I have but can not seem to locate/grasp what I need. I requi Module to run automatically when workbook is started. Dropdown to provide selections: (1) workbook book to be used normally or (2) a dropdown that will allow opening, editing, and closing of any/all of active workbook worksheets. Option (2) to remain available while workbook is open. Workbook will have up to 30 sheets, maybe more. Thanks in advance to the €śgroup€ť Rick |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to select active workbook worksheet(s) in Excel 2007
First you will need to setup a Userform with a ComboBox in it. Then put this
in the workbooks Open_Event: ' this will call the userform to show Private Sub Workbook_Open() UserForm1.Show End Sub Then put this in the Userforms Intialize Event: ' this will load all the userforms in the workbook into the combobox Private Sub UserForm_Initialize() Dim wks As Worksheet ' load worksheet names into combobox For Each wks In Worksheets ComboBox1.AddItem wks.Name Next wks End Sub Then put this in the ComboBox1 Change Event: ' when you change the combobox it will hide/unhide worksheets Private Sub ComboBox1_Change() Dim wks As Worksheet Sheets(ComboBox1.Value).Visible = xlSheetVisible For Each wks In Worksheets If wks.Name < ComboBox1.Value Then wks.Visible = xlSheetVeryHidden End If Next wks End Sub Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "RickLM" wrote: Code to select active workbook worksheet(s) in Excel 2007 Need help I have limited experience in VBA and have been looking online and in the Excel 2007 books I have but can not seem to locate/grasp what I need. I requi Module to run automatically when workbook is started. Dropdown to provide selections: (1) workbook book to be used normally or (2) a dropdown that will allow opening, editing, and closing of any/all of active workbook worksheets. Option (2) to remain available while workbook is open. Workbook will have up to 30 sheets, maybe more. Thanks in advance to the €śgroup€ť Rick |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to select active workbook worksheet(s) in Excel 2007
Ryan
Thx for help but as I said I have very little background in VBA (just enough to get me into trouble, Im 62 so this stuff is REAL new to me). I got dropdown to open and select any single worksheet. Have following questions, if you have time. 1) Select €śAll€ť so workbook runs normally 2) Select and show more than one worksheet but not all 2) Cant edit selected worksheet without closing combobox 3) After selecting a worksheet can ComboBox be hidden and then unhidden for selection of different worksheet? 3) If ComboBox is closed how to run it again without closing/reopening workbook? Ryan see by your profile you are in training€¦ so hope this is not too mundane for you Thx again and Happy Holidays Rick -- RickLM "Ryan H" wrote: First you will need to setup a Userform with a ComboBox in it. Then put this in the workbooks Open_Event: ' this will call the userform to show Private Sub Workbook_Open() UserForm1.Show End Sub Then put this in the Userforms Intialize Event: ' this will load all the userforms in the workbook into the combobox Private Sub UserForm_Initialize() Dim wks As Worksheet ' load worksheet names into combobox For Each wks In Worksheets ComboBox1.AddItem wks.Name Next wks End Sub Then put this in the ComboBox1 Change Event: ' when you change the combobox it will hide/unhide worksheets Private Sub ComboBox1_Change() Dim wks As Worksheet Sheets(ComboBox1.Value).Visible = xlSheetVisible For Each wks In Worksheets If wks.Name < ComboBox1.Value Then wks.Visible = xlSheetVeryHidden End If Next wks End Sub Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "RickLM" wrote: Code to select active workbook worksheet(s) in Excel 2007 Need help I have limited experience in VBA and have been looking online and in the Excel 2007 books I have but can not seem to locate/grasp what I need. I requi Module to run automatically when workbook is started. Dropdown to provide selections: (1) workbook book to be used normally or (2) a dropdown that will allow opening, editing, and closing of any/all of active workbook worksheets. Option (2) to remain available while workbook is open. Workbook will have up to 30 sheets, maybe more. Thanks in advance to the €śgroup€ť Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Office 2007 Excel Macro to save Active Worksheet to HTML by name | Excel Programming | |||
how to select cells from a non active worksheet | Excel Programming | |||
how to select multiple charts on a worksheet in excel 2007 | Excel Programming | |||
Select Case for Active Worksheet syntax. | Excel Programming | |||
select all arrows or lines on active worksheet - an example | Excel Programming |