Why can't I select the cell?

  • Thread starter Thread starter Julian Milano
  • Start date Start date
J

Julian Milano

Dim ws As Worksheet
.....
Private Sub ProtectAllWS()
For Each ws In Worksheets
ws.Cells(1, 1).Select
ws.Protect Password:="yes"
Next ws
End Sub

It fails on the line "ws.Cells(1, 1).Select" with the error "Select method
of range class failed". Why?

I tried "sheets(1).cells(1,1).select" and it works fine.

OK, so what I found was that the sheet had to be visible/active before the
code above would work. How do I cahnge the code so that I do not have to
select the worksheet first before setting [A1] as the selection?
 
Hi
just delete this line. No need for selecting the sheet first. That is
the code
Private Sub ProtectAllWS()
Dim ws as worksheet
For Each ws In Worksheets
ws.Protect Password:="yes"
Next ws
End Sub

should work just fine (I just added a declaration for your variable)
 
Frank,

The variable is already declared, see my code. Furthermore, I need to have
cell A1 select on each sheet, without having to actually select the
worksheet beforehand.

--

Julian Milano


Frank Kabel said:
Hi
just delete this line. No need for selecting the sheet first. That is
the code
Private Sub ProtectAllWS()
Dim ws as worksheet
For Each ws In Worksheets
ws.Protect Password:="yes"
Next ws
End Sub

should work just fine (I just added a declaration for your variable)

--
Regards
Frank Kabel
Frankfurt, Germany

Julian Milano said:
Dim ws As Worksheet
....
Private Sub ProtectAllWS()
For Each ws In Worksheets
ws.Cells(1, 1).Select
ws.Protect Password:="yes"
Next ws
End Sub

It fails on the line "ws.Cells(1, 1).Select" with the error "Select method
of range class failed". Why?

I tried "sheets(1).cells(1,1).select" and it works fine.

OK, so what I found was that the sheet had to be visible/active before the
code above would work. How do I cahnge the code so that I do not have to
select the worksheet first before setting [A1] as the selection?
Click to expand...
Click to expand...
 
Hi
sorry I missed your declaration. Though I would put in within the
module range and not outside the module as a global variable.

But why do you want to select cell A1 in your code. If you only want to
protect all sheets this is not required to select the cell before you
do your protection

--
Regards
Frank Kabel
Frankfurt, Germany

Julian Milano said:
Frank,

The variable is already declared, see my code. Furthermore, I need to have
cell A1 select on each sheet, without having to actually select the
worksheet beforehand.

--

Julian Milano


Frank Kabel said:
Hi
just delete this line. No need for selecting the sheet first. That is
the code
Private Sub ProtectAllWS()
Dim ws as worksheet
For Each ws In Worksheets
ws.Protect Password:="yes"
Next ws
End Sub

should work just fine (I just added a declaration for your variable)

--
Regards
Frank Kabel
Frankfurt, Germany

Julian Milano said:
Dim ws As Worksheet
....
Private Sub ProtectAllWS()
For Each ws In Worksheets
ws.Cells(1, 1).Select
ws.Protect Password:="yes"
Next ws
End Sub

It fails on the line "ws.Cells(1, 1).Select" with the error
Click to expand...
"Select
method
of range class failed". Why?

I tried "sheets(1).cells(1,1).select" and it works fine.

OK, so what I found was that the sheet had to be visible/active before the
code above would work. How do I cahnge the code so that I do not
Click to expand...
have
to
select the worksheet first before setting [A1] as the selection?
Click to expand...
Click to expand...
Click to expand...
 
Hi Julian

You can't do that unless you activate each sheet. And worse, you can have
multiple windows containing the same sheet, and each window can have a
different active cell.

--
HTH. Best wishes Harald
Followup to newsgroup only please

Julian Milano said:
Frank,

The variable is already declared, see my code. Furthermore, I need to have
cell A1 select on each sheet, without having to actually select the
worksheet beforehand.

--

Julian Milano


Frank Kabel said:
Hi
just delete this line. No need for selecting the sheet first. That is
the code
Private Sub ProtectAllWS()
Dim ws as worksheet
For Each ws In Worksheets
ws.Protect Password:="yes"
Next ws
End Sub

should work just fine (I just added a declaration for your variable)

--
Regards
Frank Kabel
Frankfurt, Germany

Julian Milano said:
Dim ws As Worksheet
....
Private Sub ProtectAllWS()
For Each ws In Worksheets
ws.Cells(1, 1).Select
ws.Protect Password:="yes"
Next ws
End Sub

It fails on the line "ws.Cells(1, 1).Select" with the error "Select method
of range class failed". Why?

I tried "sheets(1).cells(1,1).select" and it works fine.

OK, so what I found was that the sheet had to be visible/active before the
code above would work. How do I cahnge the code so that I do not have to
select the worksheet first before setting [A1] as the selection?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top