lcpx
03-30-2006, 01:46 AM
I am developing a report which has 2 worksheets. One is called Menu and another one is ?REPORT?. Basically we have several buttons in the MENU worksheet, when user click on the button, the data will be populated to the REPORT sheet.
Everything is fine when Buttons and Report are in the same worksheet. But if I put the button in a separate worksheet, when I click the button, it just returns error says: Method ?Range? of object ?_WorkSheet? failed and hightlight the line ws.Range(Cells(2, 7), Cells(row, 7)).delete.
Private Sub CommandButton2_Click()
Application.DisplayAlerts = False
Dim row As Long
Dim ws As Excel.Worksheet
Set ws = Worksheets("REPORT")
row = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Worksheets("REPORT").Select
Worksheets("REPORT").Activate
If row<= 2 Then
row = 2
End If
ws.Range(Cells(2, 1), Cells(row, 7)).delete
Call PopulateData
Application.DisplayAlerts = True
ActiveWindow.FreezePanes = False
Worksheets("REPORT").Range("A3").Activate
ActiveWindow.FreezePanes = True
End Sub
So I have to use another way to do it. I put exactly same code to a new sub in the module.
Public Sub DeleteData()
Application.DisplayAlerts = False
Dim row As Long
Dim ws As Excel.Worksheet
Set ws = Worksheets("REPORT")
row = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Worksheets("REPORT").Select
Worksheets("REPORT").Activate
If row<= 2 Then
row = 2
End If
ws.Range(Cells(2, 1), Cells(row, 7)).delete
Call PopulateData
Application.DisplayAlerts = True
ActiveWindow.FreezePanes = False
Worksheets("REPORT").Range("A3").Activate
ActiveWindow.FreezePanes = True
End Sub
Then call this SUB in report page
Private Sub CommandButton2_Click()
Call DeleteData
Call PopulateData
ActiveWindow.FreezePanes = False
Worksheets("REPORT").Range("A3").Activate
ActiveWindow.FreezePanes = True
End Sub
It works fine, but I just don?t understand why my first solution doesn?t work. Why I have to put the code in a separate SUB and call it in the worksheet. What?s the difference?
Thanks very much for your time and help!
Best regards,
Pete
Everything is fine when Buttons and Report are in the same worksheet. But if I put the button in a separate worksheet, when I click the button, it just returns error says: Method ?Range? of object ?_WorkSheet? failed and hightlight the line ws.Range(Cells(2, 7), Cells(row, 7)).delete.
Private Sub CommandButton2_Click()
Application.DisplayAlerts = False
Dim row As Long
Dim ws As Excel.Worksheet
Set ws = Worksheets("REPORT")
row = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Worksheets("REPORT").Select
Worksheets("REPORT").Activate
If row<= 2 Then
row = 2
End If
ws.Range(Cells(2, 1), Cells(row, 7)).delete
Call PopulateData
Application.DisplayAlerts = True
ActiveWindow.FreezePanes = False
Worksheets("REPORT").Range("A3").Activate
ActiveWindow.FreezePanes = True
End Sub
So I have to use another way to do it. I put exactly same code to a new sub in the module.
Public Sub DeleteData()
Application.DisplayAlerts = False
Dim row As Long
Dim ws As Excel.Worksheet
Set ws = Worksheets("REPORT")
row = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Worksheets("REPORT").Select
Worksheets("REPORT").Activate
If row<= 2 Then
row = 2
End If
ws.Range(Cells(2, 1), Cells(row, 7)).delete
Call PopulateData
Application.DisplayAlerts = True
ActiveWindow.FreezePanes = False
Worksheets("REPORT").Range("A3").Activate
ActiveWindow.FreezePanes = True
End Sub
Then call this SUB in report page
Private Sub CommandButton2_Click()
Call DeleteData
Call PopulateData
ActiveWindow.FreezePanes = False
Worksheets("REPORT").Range("A3").Activate
ActiveWindow.FreezePanes = True
End Sub
It works fine, but I just don?t understand why my first solution doesn?t work. Why I have to put the code in a separate SUB and call it in the worksheet. What?s the difference?
Thanks very much for your time and help!
Best regards,
Pete