Form Button vs Command Button
 

Excel 2003

I have a Form button with the following code in a Module. I want to use a
Command button so I can change the color of the button. When I run the same
code in the command button in the worksheet module I get a "Select method of
Range Class failed" error.

What's wrong?

Private Sub CommandButton10_Click()

ActiveWorkbook.Unprotect Password:="scott"
Sheets("Records").Visible = True
Sheets("AddRecords").Visible = False
Sheets("Records").Activate
Sheets("Records").Unprotect Password:="scott"
Range("B:B,E:G,I:N,P:W").Select
Range("W1").Activate
Selection.EntireColumn.Hidden = True
Range("C5").Select
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect contents:=True, userInterfaceOnly:=True 'Added
Descend code here
Range("A4:W505").Sort Key1:=Range("A4"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Records").Protect Password:="scott"
ActiveWorkbook.Protect Password:="scott"
End Sub

Form Button vs Command Button
 

Code in a standard module defaults to the active sheet. Code in a sheet
default to the sheet that it is in regardless of which sheet is active.

Private Sub CommandButton10_Click()

ThisWorkbook.Unprotect Password:="scott"
Sheets("AddRecords").Visible = False
with Sheets("Records")
.Visible = True
.Activate
.Unprotect Password:="scott"
.Range("B:B,E:G,I:N,P:W").EntireColumn.Hidden = True
.Range("C5").Select
.EnableAutoFilter = True
.Protect contents:=True, userInterfaceOnly:=True 'Added
'Descend code here
.Range("A4:W505").Sort Key1:=.Range("A4"), Order1:=xlDescending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
.Protect Password:="scott"
end with
ThisWorkbook.Protect Password:="scott"
End Sub
--
HTH...

Jim Thomlinson


"Gizmo" wrote:

Excel 2003

I have a Form button with the following code in a Module. I want to use a
Command button so I can change the color of the button. When I run the same
code in the command button in the worksheet module I get a "Select method of
Range Class failed" error.

What's wrong?

Private Sub CommandButton10_Click()

ActiveWorkbook.Unprotect Password:="scott"
Sheets("Records").Visible = True
Sheets("AddRecords").Visible = False
Sheets("Records").Activate
Sheets("Records").Unprotect Password:="scott"
Range("B:B,E:G,I:N,P:W").Select
Range("W1").Activate
Selection.EntireColumn.Hidden = True
Range("C5").Select
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect contents:=True, userInterfaceOnly:=True 'Added
Descend code here
Range("A4:W505").Sort Key1:=Range("A4"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Records").Protect Password:="scott"
ActiveWorkbook.Protect Password:="scott"
End Sub

Form Button vs Command Button
 

That did it. Thanks Jim

"Jim Thomlinson" wrote:

Code in a standard module defaults to the active sheet. Code in a sheet
default to the sheet that it is in regardless of which sheet is active.

Private Sub CommandButton10_Click()

ThisWorkbook.Unprotect Password:="scott"
Sheets("AddRecords").Visible = False
with Sheets("Records")
.Visible = True
.Activate
.Unprotect Password:="scott"
.Range("B:B,E:G,I:N,P:W").EntireColumn.Hidden = True
.Range("C5").Select
.EnableAutoFilter = True
.Protect contents:=True, userInterfaceOnly:=True 'Added
'Descend code here
.Range("A4:W505").Sort Key1:=.Range("A4"), Order1:=xlDescending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
.Protect Password:="scott"
end with
ThisWorkbook.Protect Password:="scott"
End Sub
--
HTH...

Jim Thomlinson


"Gizmo" wrote:

Excel 2003

I have a Form button with the following code in a Module. I want to use a
Command button so I can change the color of the button. When I run the same
code in the command button in the worksheet module I get a "Select method of
Range Class failed" error.

What's wrong?

Private Sub CommandButton10_Click()

ActiveWorkbook.Unprotect Password:="scott"
Sheets("Records").Visible = True
Sheets("AddRecords").Visible = False
Sheets("Records").Activate
Sheets("Records").Unprotect Password:="scott"
Range("B:B,E:G,I:N,P:W").Select
Range("W1").Activate
Selection.EntireColumn.Hidden = True
Range("C5").Select
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect contents:=True, userInterfaceOnly:=True 'Added
Descend code here
Range("A4:W505").Sort Key1:=Range("A4"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Records").Protect Password:="scott"
ActiveWorkbook.Protect Password:="scott"
End Sub