Error selecting range from userform
 

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(RowSe l, "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?

--
Trefor

Error selecting range from userform
 

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)



On Thu, 6 Nov 2008 07:22:00 -0800, Trefor wrote:

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(RowSe l, "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?

Error selecting range from userform
 

Chip,

Silly me. Many thanks.

--
Trefor


"Chip Pearson" wrote:


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)



On Thu, 6 Nov 2008 07:22:00 -0800, Trefor wrote:

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(RowSe l, "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?