Excel 2007 Macro Help (Buttons)

[Chris Littlewood]

Hi I was wondering if someone could help me.

I am creating a fairly simple excel data sheet to help out in work. I'll explain what I am trying to get it to do, then you might get the idea of where I am going wrong and hopefully point me in the right direction.

I deal will a lot of return parts from one manufacturer and at present we have to look up each part number of there on line site to see what it is.

So I have set up a spreadsheet with the first column containing the common search parameter replicated in each cell:

Example: http://h20141.www2.hp.com/hpparts/Se...earchCriteria=

And the next column has the barcode part numbers scanned in

Example: A9739-69008

In the following Colum I placed the formula =HYPERLINK(A1&""&B1) then copied and pasted in all the cells in the column.

The combined result gives me a hyperlink to that parts information:
http://h20141.www2.hp.com/hpparts/Se...ia=A9739-69008


Now although this works great as a hyperlink to each part description, it looks untidy.

What I want to do is create a button that can be pressed on each row than can do the same thing withoutlooking so untidy.

I tried to create a macro for a button on the first row with the following code:

Sub HP_link_2()
ActiveWorkbook.FollowHyperlink Address:= Range("C1"),
NewWindow:=True
End Sub

But it’s nowhere near right.

Can someone help please?

[.paul.]

you could add a button, which would open the parts site with the search criteria being the activecell value:

Private Sub CommandButton1_Click()
    Dim partsSiteURL As String
    partsSiteURL = "http://h20141.www2.hp.com/hpparts/SearchCriteria="
    ActiveWorkbook.FollowHyperlink Address:=partsSiteURL & ActiveCell.Value
End Sub

then you'd just need a column with the barcode part numbers.

[Chris Littlewood]

Thanks for your reply.

Sorry for being completely stupid and no doubt you will laugh at this question.

If the active cell is for example D1, what whould the exact code look like that I would have to put in to the button macro.

I have tried a few variations with no luck

[.paul.]

you'd use the code i posted in post #2.

to run it you'd select cell D1 for example, then click the button...