Easy question, I think

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Judge Platt - 30 Nov 2007 21:41 GMT

I want to make a form button click a cell that has a hyperlink in it. Range
("B4").Select simply moves my cursor to that cell, but doesn't activate the
hyperlink. How do I activate the hyperlink?

Reply to this Message

Ron Coderre - 30 Nov 2007 21:59 GMT

If cell B4 actually contains a hyperlink
try something like this:

Sub GoToHyperlink()
[B4].Hyperlinks(1).Follow
End Sub

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

>I want to make a form button click a cell that has a hyperlink in it.
>Range
> ("B4").Select simply moves my cursor to that cell, but doesn't activate
> the
> hyperlink. How do I activate the hyperlink?

Reply to this Message

Judge Platt - 30 Nov 2007 22:13 GMT

Thanks Ron. I think that's along the right track, except that the hyperlink
in B4 is created through the Excel formula HYPERLINK as opposed to attaching
a hyperlink. When I run that, I get: "Run-time error '9': subscript out of
range"

> If cell B4 actually contains a hyperlink
> try something like this:
[quoted text clipped - 17 lines]
> > the
> > hyperlink. How do I activate the hyperlink?

Reply to this Message

Ron Coderre - 30 Nov 2007 22:32 GMT

There may be another way....but, this is what I came up with:

Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hWnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Sub GoToHyperlinkFuncURL()
Dim strURL As String
Dim strSource As String

strSource = [B4].Formula

strURL = Range(Mid(Left(strSource, InStr(1, strSource, ",") - 1), _
InStr(1, strSource, "(") + 1))

ShellExecute _
hWnd:=0, _
lpOperation:=vbNullString, _
lpFile:=strURL, _
lpParameters:=vbNullString, _
lpDirectory:=vbNullString, _
nShowCmd:=5
End Sub

Is that something you can work with?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

> Thanks Ron. I think that's along the right track, except that the
> hyperlink
[quoted text clipped - 25 lines]
>> > the
>> > hyperlink. How do I activate the hyperlink?

Reply to this Message

Ron Coderre - 30 Nov 2007 22:40 GMT

I should mention....I assumed the HYPERLINK function referenced other cells
that contained the URL and the Dislayed Text.

If your formula actually contains the URL, the code can be shortened.

-------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

> There may be another way....but, this is what I came up with:
>
[quoted text clipped - 64 lines]
>>> > the
>>> > hyperlink. How do I activate the hyperlink?

Reply to this Message

Judge Platt - 04 Dec 2007 19:27 GMT

Yes. Thanks, Ron.

> There may be another way....but, this is what I came up with:
>
[quoted text clipped - 63 lines]
> >> > the
> >> > hyperlink. How do I activate the hyperlink?

Reply to this Message

hupjack - 23 Mar 2008 22:46 GMT

Hi Ron,

I found this post while searching for some Excel VBA help for some tools I'm
making to improve a few processes at my work, and it comes really close to
answering my question.

I've taken a basic C programming course back 5 years ago as part of my
mechanical engineering degree and I know basic HTML. So I'm basically a
programming novice hacking my way through some VBA in excel.

I took a couple screen captures so you can see what I'm trying to accomplish

http://vbahelptwo032308.notlong.com
http://vbahelp032308.notlong.com

As you can see, I've put some URLs sitting way out there at column BI in
this spreadsheet I've made.

In cell BI14, I've got a URL formed from the following excel command
="https://na1.salesforce.com/search/SearchResults?searchType=1&sbstr="&CUSTOMER_NAME

If my customer name was "demo" the results showing up in that cell are
https://na1.salesforce.com/search/SearchResults?searchType=1&sbstr=demo
which is precisely the URL needed to perform a salesforce search on that
fake customer name.

your first suggestion to the original poster almost worked for me.

> Private Sub CommandButton1_Click()
> [BI14].Hyperlinks(1).Follow
> End Sub

Perhaps this wasn't the right way to "implement it", but I just stuck the
Hyperlinks(1).Follow command, in as the action for the CommandButton1_Click
code that automatically got placed when I created the button.

The result when the button was clicked.... the "customer name" defined in
my excel spreadsheet at Cell B3 doesn't get passed with the URL, so it just
launches a web browser with the first part of the URL and without a search
term https://na1.salesforce.com/search/SearchResults?searchType=1&sbstr=

So... How do I pass the *output* from my formula at BI14 that tacks the
customer name on the end of that URL, so that when I click a button, it
launches a web browser with that full URL with the search term?

I think I need a little more VBA hand holding to implement your second
suggestion. I wasn't sure where to declare that function. Should I create a
new "module" with your quoted code? and then where / what do I reference
with the button I create? Previously I made a button that invoked a certain
macro I had written, but in design mode, after creating a new button, I don't
even see in its properties how to point to a specific macro. There must be
different ways to create different types of buttons.

An excel VBA for dummies book might be in my future.
http://www.j-walk.com/ss/ any recommendations? Excel 2003 power programming
with VBA? or the dummies book, or a different one completely?

If anybody wants extra credit, I've got another (likely very simple) VBA
question regarding this same tool I'm working on over here.
http://community.salesforce.com/sforce/board/message?board.id=Excel_Connector&me
ssage.id=806#M806

Thanks in advance for any help anybody can offer!
-Ethan (in San Diego)

> There may be another way....but, this is what I came up with:
>
[quoted text clipped - 63 lines]
> >> > the
> >> > hyperlink. How do I activate the hyperlink?

Reply to this Message