| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
open worksheet on first unlocked cell
Hi there
I have a form that opens automatically when the file is open. It has a combo box with a list of the worksheets that when one is selected the worksheet opens. I used the following code: Private Sub ComboBox1_Change() Sheets(ComboBox1.Text).Select End Sub Private Sub UserForm_Activate() For Each Sheet In ActiveWorkbook.Sheets ComboBox1.AddItem (Sheet.Name) Next End Sub It works great-- but the active cell is just the last cell used. I want it to open with the first unlocked cell active. Can this be done, if so, how? -- Thank-you! Ruth |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
open worksheet on first unlocked cell
Before your end sub select the cell
Activesheet.Cells(1,1).select equalling cells(row,column) so the above selects cell A1 -- -John Please rate when your question is answered to help us and others know what is helpful. "Ruth" wrote: Hi there I have a form that opens automatically when the file is open. It has a combo box with a list of the worksheets that when one is selected the worksheet opens. I used the following code: Private Sub ComboBox1_Change() Sheets(ComboBox1.Text).Select End Sub Private Sub UserForm_Activate() For Each Sheet In ActiveWorkbook.Sheets ComboBox1.AddItem (Sheet.Name) Next End Sub It works great-- but the active cell is just the last cell used. I want it to open with the first unlocked cell active. Can this be done, if so, how? -- Thank-you! Ruth |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
open worksheet on first unlocked cell
you may also use a range
ActiveSheet.Range("K1").Select -- -John Please rate when your question is answered to help us and others know what is helpful. "Ruth" wrote: Hi there I have a form that opens automatically when the file is open. It has a combo box with a list of the worksheets that when one is selected the worksheet opens. I used the following code: Private Sub ComboBox1_Change() Sheets(ComboBox1.Text).Select End Sub Private Sub UserForm_Activate() For Each Sheet In ActiveWorkbook.Sheets ComboBox1.AddItem (Sheet.Name) Next End Sub It works great-- but the active cell is just the last cell used. I want it to open with the first unlocked cell active. Can this be done, if so, how? -- Thank-you! Ruth |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
open worksheet on first unlocked cell
Ruth,
I assume by 'Unlocked' you mean the 'Locked' checkmark has been removed with Format|Cells - protection tab. If so try this Private Sub ComboBox1_Change() For Each c In Sheets(ComboBox1.Text).UsedRange If c.Locked = False Then Sheets(ComboBox1.Text).Range(c.Address).Select Exit For End If Next End Sub Mike "Ruth" wrote: Hi there I have a form that opens automatically when the file is open. It has a combo box with a list of the worksheets that when one is selected the worksheet opens. I used the following code: Private Sub ComboBox1_Change() Sheets(ComboBox1.Text).Select End Sub Private Sub UserForm_Activate() For Each Sheet In ActiveWorkbook.Sheets ComboBox1.AddItem (Sheet.Name) Next End Sub It works great-- but the active cell is just the last cell used. I want it to open with the first unlocked cell active. Can this be done, if so, how? -- Thank-you! Ruth |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
open worksheet on first unlocked cell
Thank-you! That worked perfectly.
-- Thank-you! Ruth "Mike H" wrote: Ruth, I assume by 'Unlocked' you mean the 'Locked' checkmark has been removed with Format|Cells - protection tab. If so try this Private Sub ComboBox1_Change() For Each c In Sheets(ComboBox1.Text).UsedRange If c.Locked = False Then Sheets(ComboBox1.Text).Range(c.Address).Select Exit For End If Next End Sub Mike "Ruth" wrote: Hi there I have a form that opens automatically when the file is open. It has a combo box with a list of the worksheets that when one is selected the worksheet opens. I used the following code: Private Sub ComboBox1_Change() Sheets(ComboBox1.Text).Select End Sub Private Sub UserForm_Activate() For Each Sheet In ActiveWorkbook.Sheets ComboBox1.AddItem (Sheet.Name) Next End Sub It works great-- but the active cell is just the last cell used. I want it to open with the first unlocked cell active. Can this be done, if so, how? -- Thank-you! Ruth |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
open worksheet on first unlocked cell
Thank-you!
-- Thank-you! Ruth "John Bundy" wrote: you may also use a range ActiveSheet.Range("K1").Select -- -John Please rate when your question is answered to help us and others know what is helpful. "Ruth" wrote: Hi there I have a form that opens automatically when the file is open. It has a combo box with a list of the worksheets that when one is selected the worksheet opens. I used the following code: Private Sub ComboBox1_Change() Sheets(ComboBox1.Text).Select End Sub Private Sub UserForm_Activate() For Each Sheet In ActiveWorkbook.Sheets ComboBox1.AddItem (Sheet.Name) Next End Sub It works great-- but the active cell is just the last cell used. I want it to open with the first unlocked cell active. Can this be done, if so, how? -- Thank-you! Ruth |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Htperlink in an unlocked cell but protected worksheet | Excel Worksheet Functions | |||
| Need to add cell comments in unlocked cell on protected worksheet | Excel Discussion (Misc queries) | |||
| Delete contents of unlocked cells in another worksheet | Excel Discussion (Misc queries) | |||
| How do you shade in an unlocked cell on a protected worksheet? | Excel Discussion (Misc queries) | |||
| How can I merge unlocked cells in a worksheet that has been protec | Excel Discussion (Misc queries) | |||
Linear Mode
