Error selecting range from userform

[Trefor]

In the code behind a userform I have tried to clear the contents of some
cells, but I get a 1001 error.


With ActiveWorkBook.Sheets(LoadSheet).Range(Cells(RowSel, "B"),
Cells(RowSel_TLA , "BA"))
.ClearContents
End With

The above works from a normal module, but will not run from a worksheet or a
userform. If there something else I can do to achieve the same thing?

 

[Chip Pearson]

The problem is that the "Cells" reference doesn't refer to the sheet
LoadSheet. If refers to whatever worksheet happens to be active. If
LoadSheet isn't active, the code will fail. Try instead,

With ActiveWorkbook.Sheets(LoadSheet)
.Range(.Cells(RowSel, "B"), _
.Cells(RowSel_TLA, "BA")).ClearContents
End With

Here, the Cells references are adorned with a period, which makes them
"roll up" through the LoadSheet rather than the ActiveSheet.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Trefor]

Chip,

Silly me. Many thanks.

--
Trefor

The problem is that the "Cells" reference doesn't refer to the sheet
LoadSheet. If refers to whatever worksheet happens to be active. If
LoadSheet isn't active, the code will fail. Try instead,

With ActiveWorkbook.Sheets(LoadSheet)
.Range(.Cells(RowSel, "B"), _
.Cells(RowSel_TLA, "BA")).ClearContents
End With

Here, the Cells references are adorned with a period, which makes them
"roll up" through the LoadSheet rather than the ActiveSheet.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)