Convert =FORMULA.GOTO?() to VBA 1

[jassy]

I'm new to VBA, trying to learn more by converting old macro.

The function "=FORMULA.GOTO?()" in Excel 4.0 will pause the dialog box; you have choice to select any range name listed to goto.

What will be the equivalent function/command in VBA?

 

[SkipVought]

hi, jassy,

Try this..

Application.Goto Reference:=Worksheets("Sheet1").Range("A154"), scroll:=True

Just check out Goto in VBA help

Skip,
metzgsk@voughtaircraft.com

 

[jassy]

Skip,

Thanks to your response. I've tried the command, but it only goto the specified range ("A154&quot. I'm looking for a way to let user have choice to pick any range name to goto.

This is what I'm trying to achieve: use the "Go To" command in Excel, a dialog box will open with all range name available in the workbook. The user can select any range name to goto without typing the reference or change the VBA command line every time.

Jassy

 

[acron]

You could try the RefEdit control on your userform, which is designed for the purpose of selecting ranges. If you are using XL97, do not place the RefEdit control in a frame, as that in my experience causes problems.

Or you could use the Input box as follows (It might be easiest)

SelectRange = Application.InputBox("Please Select a range ", "Select a Range", Type:=8).

Setting the Type = 8 allows it to act as a range selector.

A.C.

 

[jassy]

Hi, Acron,

The SelectRange = Application.InputBox("Please Select a range ", "Select a Range", Type:=8) only goto the range name as typed by user.

I'm looking for a way to list all (hundreds) range names for user to search, select and goto. Please help again. Thanks.

 

[SkipVought]

Hi,

There is a Names Collection. This will get you all the names for example...

For Each Name in ActiveWorkbook.Names
   MsgBox Name.Name
Next

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[acron]

Sorry about that.

How about

rngGoTo = Application.Dialogs(xlDialogFormulaGoto).Show

that shows excels normal GoTo dialog box which should show all named ranges. If one is selected, that range becomes the selection.

A.C.

 

[jassy]

Acron, Thanks to your help. It works just as I want to be.

Skip, Thanks to your help too.

Jassy