Cell Value is substituted for value in command

[bruce6230]

I have created a spreadsheet that allows me to click a button that
launches an RDP session from a computer listed in the spreadsheet. I
want to be able to highlight a cell with a computer name in it and
populate the value where servername appears now. In that way the user
only needs to choose the computer name and click the RDP button.

Private Sub CommandButton1_Click()
Call RDP_Click
End Sub

Sub RDP_Click()
Shell "c:\windows\system32\mstsc /v:servername /f"
End Sub

How do I modify the line that starts with Shell so that the user
launches the session with the value of the highlighted cell in the
spreadsheet. In other words, the highlighted cell replaces the
"servername" value above.

 

[vezerid]

Shell "c:\windows\system32\mstsc /v:" & Range("A1").value &" /f"

HTH
Kostis Vezerides

 

[bruce6230]

I want the value to be Range ("Highlighted Cell"). What do I put in
place of A1 so that the active cell represents that value?

 

[vezerid]

Shell "c:\windows\system32\mstsc /v:" & Selection.value &" /f"

Also, since a selection could conceivably be more than one cells, you
could guard against this with either:

Shell "c:\windows\system32\mstsc /v:" & Selection.Cells(1,1).value &"
/f"

or

Shell "c:\windows\system32\mstsc /v:" & ActiveCell.value &" /f"

Write back if you want some more elaborate code for this, possibly
using an IF

Kostis

 

[bruce6230]

The solution you proposed is tailor made. Thank you. I have two more
tweaks I would like to make. When I click on the RDP button it launches
the RDP session correctly but the session starts out minimized on the
task bar. Is there a way to bring the RDP session to the front? Second,
I would like to add a screen tip explaining what the button does.

 

[vezerid]

From the documentation:
---------------------------------------------
Shell(pathname[,windowstyle])

windowstyle Optional. Variant (Integer) corresponding to the style of
the window in which the program is to be run. If windowstyle is
omitted, the program is started minimized with focus. On the Macintosh
(System 7.0 or later), windowstyle only determines whether or not the
application gets the focus when it is run.

The windowstyle named argument has these values:

Constant Value Description
vbHide 0 Window is hidden and focus is passed to the hidden window. The
vbHide constant is not applicable on Macintosh platforms.
vbNormalFocus 1 Window has focus and is restored to its original size
and position.
vbMinimizedFocus 2 Window is displayed as an icon with focus.
vbMaximizedFocus 3 Window is maximized with focus.
vbNormalNoFocus 4 Window is restored to its most recent size and
position. The currently active window remains active.
vbMinimizedNoFocus 6 Window is displayed as an icon. The currently
active window remains active.
----------------------------------

Thus,

Shell "c:\windows\system32\mstsc /v:" & ActiveCell.value &" /f",
vbNormalFocus

Regarding the screen tip: You can simply type directly on Excel. Also
you can consider a cell comment (Insert|Comment). If you have the
luxury to resize the cell so that it is adequately larger than the
button, then when the user points the mouse over the button he also
points it over the cell and the comment appears. There is no other
automation I can think of presently.

HTH
Kostis Vezerides

 

[bruce6230]

That worked perfectly. Thanks for the help.