Why does the select method fail on my range object

[andrew.ward]

Hi All,

I have created a userform with a single refedit control (i.e. RefEdit1) and a single command button (i.e. CommandButton1). I also use code (see the end of the message) in the form.

The select method works when the user uses refedit to select a range from the active worksheet. But, why does the select method fail when the user changes to another sheet and selects some cells on that sheet?

What can I do to stop it failing?

Thanks for your help.

Andrew


'code in the userform
Private Sub CommandButton1_Click()

Dim SelRange As Range

'Get the user defined address, or reference, from the RefEdit control.

Set SelRange = Range(RefEdit1.Value)

SelRange.Select


'Unload the userform.

Unload Me

End Sub

[Mark O'Brien]

Let me get this straight. Let's say that Sheet1 is the activesheet. You want this code to select a cell on another sheet, for example, Sheet2?

If this is the case, the reason this fails is because you need to activate Sheet2 before trying to select a range on that sheet.

Without looking too hard at what you need to do, you're to have to find a way to do something like:

Sheets("Sheet2").Activate

HTH

[Ivan F Moala]

Try;

Private Sub CommandButton1_Click()

Dim SelRange As Range



'Get the user defined address, or reference, from the RefEdit control.

Set SelRange = Range(RefEdit1.Text)



Application.Goto SelRange



'Unload the userform.

Unload Me

End Sub

[andrew.ward]

Thanks Mark and Ivan. The "GOTO" method works. I hadn't realised that Select only works for the current worksheet (the help isn't explicit on that point).

Thanks very much for your help.

Andrew