MS Excel-Need macro to pause, allow user input, then resume again 1

[Acquaman]

Hello! I just found out about this web site. I am so impressed. People helping people. It does't get any better than that!
Here's my problem...I recorded a macro in Excell (2000) that creates a Hyperlink to another file and copies information from that file back to the original file. The macro functions as recorded. Now I need to edit the macro to allow the user to select the file name from the Hyperlink pop up menu, and then resume automatically or when the user clicks O.K.
If you have an answer for me it will be sincerely appreciated. Thank You!

 

[PBAPaul]

Acquaman

A quick and dirty solution might be to put a request for input to the user.

The code would be as shown below:

Ans = InputBox("Enter something here", "HYPERLINK", "Default Hyperlink&quot
If Ans = "" Then Exit Sub

The string variable 'Ans' asks for an input via the Input Box. The box has a prompt of "Enter something here", a title of "HYPERLINK" and then a default value which can be your default Hyperlink eg "

http://www.microsoft.com"

If the default is acceptable then Ans is returned with the default Hyperlink or the user can change the Hyperlink or can hit the Cancel button.

If you need to give the user a list of hyperlinks to choose from then you could either create a userform with a list box containing the Hyperlinks or use the inbuilt Hyperlink box.

Hope that this helps

Paul

 

[Acquaman]

Dear PBAPaul,
That sounds excellent! I am off to try it. Thanks!!
Sincerely,
Acquaman (EdAcquaro@aol.com)

 

[Acquaman]

I tried the Ans= code and am having some success but, a list of Hyperlinks, preferably the list in the Hyperlink pop-up menu box (is this considered the inbuilt box?) is what I am really after (and all of it's interactive capability).

Again, any help will be most appreciated.
Thank you!
Acquaman

 

[Nikita6003]

Acquaman - you'll have to build your own userform if you need anything non-standard for user-input - the form'll allow you to add a combobox, build the combobox's list in code or set its ListSource to a range of cells, then add an OK and Cancel commandbutton & you've got your own version of the good ole Inputbox

Cheers
Nikki

 

[PBAPaul]

Acquaman - I agree with Nikki, it is probable that you will need to construct your own userform.

A possible scenario for you is as follows:

1 In your workbook, create a list of all your hyperlinks
2 Create userform with a list box or combo box, an OK button and a Cancel Button
3 Set the list box's ListSource to your list of hyperlinks
Show the userform
4 if the OK button is pressed then the Hyperlink selected is the value of the list ie userform.listbox.value
5Then use your code to open that hyperlink and do what is necessary
6You could then update your Hyperlink list with details about when, how, if successful etc

A little bit of work but nothing too difficult. You will probably have to play around a little.

Good luck

Paul