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

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

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

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

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

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