EXCEL MACRO HELP
 

So i'm new at working with macros.
I have an excel sheet that allows a user to select a state and a city from
that state. Each city has a website that is identified to it. The goal is
to be able to click a button that will open the hyperlinked website for a
choosen city. At the moment I have a macro that is assigned to a button and
opens the hyperlink and imports the data, but the website will not change
when I change the city. Any suggestions?

As clarification, the website I want to be opened is run through a lookup
with a hyperlink, so by changing the city, the website changes and you can
click on the link and it will open up the website. So that portion works
correctly.

--
THANKS
~Steph

EXCEL MACRO HELP
 

On Jun 17, 10:56*am, Steph wrote:
So i'm new at working with macros.
I have an excel sheet that allows a user to select a state and a city from
that state. *Each city has a website that is identified to it. *The goal is
to be able to click a button that will open the hyperlinked website for a
choosen city. *At the moment I have a macro that is assigned to a button and
opens the hyperlink and imports the data, but the website will not change
when I change the city. *Any suggestions?

As clarification, the website I want to be opened is run through a lookup
with a hyperlink, so by changing the city, the website changes and you can
click on the link and it will open up the website. *So that portion works
correctly.

--
THANKS
~Steph

Your hyperlink isn't changing because it's assuming the text is all
that's changing... you may want to consider using the santax below...

Sub CommandButton1_Click()

ActiveWorkbook.FollowHyperlink = Range("A1").Value

End Sub

Range A1 would contain the URL of the webaddress your trying to get
to. This way your not dependant on the URL constantly being refreshed.
I suppose you could have the URL change in range A1 if you combined a
few cells...

Range A1 Formula
="Http://www.MyCoolCity.Com/"&A2&"/"&A2&".htm"

Range A2 = City Name either by a Vlookup statement or by the end user
typing something in.

Your message is some what confusing so I hope that this helps you out.
You can also send me your workbook

EXCEL MACRO HELP
 

--
THANKS
~Steph


"Shaka215" wrote:

On Jun 17, 10:56 am, Steph wrote:
So i'm new at working with macros.
I have an excel sheet that allows a user to select a state and a city from
that state. Each city has a website that is identified to it. The goal is
to be able to click a button that will open the hyperlinked website for a
choosen city. At the moment I have a macro that is assigned to a button and
opens the hyperlink and imports the data, but the website will not change
when I change the city. Any suggestions?

As clarification, the website I want to be opened is run through a lookup
with a hyperlink, so by changing the city, the website changes and you can
click on the link and it will open up the website. So that portion works
correctly.

--
THANKS
~Steph

Your hyperlink isn't changing because it's assuming the text is all
that's changing... you may want to consider using the santax below...

Sub CommandButton1_Click()

ActiveWorkbook.FollowHyperlink = Range("A1").Value

End Sub

Range A1 would contain the URL of the webaddress your trying to get
to. This way your not dependant on the URL constantly being refreshed.
I suppose you could have the URL change in range A1 if you combined a
few cells...

Range A1 Formula
="Http://www.MyCoolCity.Com/"&A2&"/"&A2&".htm"

Range A2 = City Name either by a Vlookup statement or by the end user
typing something in.

Your message is some what confusing so I hope that this helps you out.
You can also send me your workbook

Thanks for the help, but I don't think it is working correctly still due to
the fact that my hyperlink I want it to follow is first a VLOOKUP command.

Here is what my macro looks like that works correctly except not changing
the web address when the city is changed.

Sub data_finder()
'
' data_finder Macro
'

'
Range("G3").Select
Workbooks.Open Filename:= _
"http://www.columbus.com"
ActiveWindow.Visible = False
Windows("724339TY.csv").Visible = True
Cells.Select
Selection.Copy
Windows("Dataorganization.xlsm").Activate
Sheets("Data").Select
Cells.Select
ActiveSheet.Paste
Range("B5").Select
Sheets("Instructions").Select
Windows("724339TY.csv").Activate
ActiveWindow.Close
Sheets("Instructions").Select
End Sub

G3 is the position of the web address which looks like...
=HYPERLINK(LOOKUP(D3,O3:Q15,3,FALSE))

All values in the Q column are web address, but they are not hyperlinked
until after the LOOKUP command has run.

Does this make more sense?