Spawn Add Printer Wizard from Hyperlink

[Ben Johnson]

Hi,

I use a printer spreadsheet to track all of the printers we're currently
using on site (about 40 of them). Every time I image a new machine I use a
checklist that includes a hyperlink to open up my printer spreadsheet so that
I can see which printer is appropriate to the user I'm prepping a machine for.

I want to create a hyperlink in the printer spreadsheet that will spawn the
Add Printer wizard, to save me a few mouse clicks/keystrokes - if it's
possible. I found information about spawning the wizard on a couple of web
sites, so my hyperlink currently looks like this:
c:\windows\system32\RunDLL32.EXE SHELL32.DLL,SHHelpShortcuts_RunDLL AddPrinter

"RunDLL32.EXE SHELL32.DLL,SHHelpShortcuts_RunDLL AddPrinter" is apparently
the string you can use to spawn the Add Printer wizard and it works if you
paste it directly into the Run dialog box. But when I click my hyperlink in
the spreadsheet I get the following error:
"Cannot open the specified file"

If I don't prefix the command string that spawns the wizard with
"C:\windows\system32" then the hyperlink automatically causes Excel to look
for RUNDLL32.EXE in the directory where the spreadsheet is stored.

Any help much appreciated.

Cheers,
Ben.

 

[NickHK]

Ben,
Unless there's a better way, try:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Shell "RunDLL32.EXE SHELL32.DLL,SHHelpShortcuts_RunDLL AddPrinter"
End Sub

NickHK

 

[Ben Johnson]

Hi Nick,

Thanks for the suggestion. Unfortunately, programming Excel isn't my forte
and I'm not sure how to implement what you've suggested.

I've copied and pasted the Private Sub from below into the worksheet object
in the VBA window, but I'm not sure what to do to point the Hyperlink to the
code. In Word I just made a MacroButton, but I can't see an equivalent in
Excel.

Cheers,
Ben.

 

[NickHK]

Ben,
You said you wanted a hyper link.
If not just add a button from Control Toolbox and add the code.

If you want a hyperlink, its address can be the active workbook

NickHK

 

[Ben Johnson]

Thanks Nick, that worked a treat. Much appreciated.