Quick Excel question for Excel Pros

Fireflash

Hello!
I have the impression that what I want to do in my document isn't possible but I wanna ask around just in case there's some mysterious way to do this.

Without going in details, here's my setup and what I need: There's a database online, with numbered entries. Each entry has it's own webpage. I have a shared excel document in which people will sometimes need to add an numbered entry from the online database. People need to be able to click on that number in the excel document to go directly to that entry in the online database.

What I know I can do: The typical setup for this is using 2 columns. For example the user types the entry # in column F, and there's a formula in column G that uses the number from column F to create an hyperlink directly to that entry in the database. So in the document you see the entry numbers in column F and the hyperlink to that entry in column G. This works fine but it makes an extra "useless column".

What I would like: Have a formula that for example if I type an entry number in column E will create the appropriate hyperlink in the very same cell I typed the number in. Can it be done?


Thanks!

schuss

Where A1 is the cell where someone typed a number:
="Hyperlink text"&A1&"any other hyperlink ending"

I think that should do it.

KafkaAU

To do it in the same cell you type the number in you would need to write some VB code which would create it for you after you type the number in.

SpongeCake

It's not possible to do it in Excel without using VBA or forms (as whatever you type in the cell will overwrite whatever code you'd put in there to do the thing you want to do!)

You could create a simple form which would prompt the user to enter a number and then trigger your hyperlink. Not too complicated if you have experience with forms. You could also write some VBA which would trigger on cell change.

tastydonuts

What you want to do doesn't seem practical, imo. But this macro can do what you want. o_o

Worksheet_Change is the event for when a worksheet, well... changes.

The steps are as follows:

1) Grab the existing value from the origin cell.
2) Generate an url from that value.
3) Return/change the origin cell's value to the url.

Private Sub Worksheet_Change(ByVal Target As Range)

  ' Is the change in row E? (Or any other row. You can specify a cell by saying Target.Row <> X OR Target.Column <> Y)
  ' And so forth.
  
  If Target.Column <> 5 Then
    Exit Sub
  End If
  
  On Error GoTo ErrHandler

  ' Disable events temporarily otherwise the value it will put into the
  ' cell's link is the text you describe the link as...
  
   Application.EnableEvents = False
  
  ' Store what is in there.
  
  Dim tempval As String
      
  tempval = Target.Value
  
  If tempval <> "" Then
  
    Target.Value = "=hyperlink(""http://www.destination.com/myurl.php?databaseID=" & tempval & """,""Link Description!"")"

  Else
  
    Target.Value = tempval
  
  End If
    

  ' Turn events back on.
  Application.EnableEvents = True
  Exit Sub

ErrHandler:
 
  Application.EnableEvents = True
  Exit Sub
    
    

End Sub

Edit: There shouldn't be a semicolon on the link line ("Target.Value..." ) and there isn't when I edit it, mind that.

Edit2: You Need the multiple "s in order to escape the "s in the formula text, too.

edit3: Fixed it so you can actually delete the contents of a cell.

Fireflash

Thanks! I'm not used to using macros much but I'll see what I can do with this!

Dunno why it doesn't seem practical to you though :P

People make some tests/verifications on some things listed in the excel document. If there's an issue they need to create that issue in the database and link it in the excel document. Having them only type the number of the issue to have an hyperlink makes it simple and fast for them. Especially since adding a hyperlink manually in a shared document doesn't seem to work very well. Much faster to just type the number in a cell.

It's a bit nit picky but having the issue number in a column and the link to said issue in a different one is just not as clean :P

tastydonuts

Oh, it's mostly because many users have macros disabled by default so the document won't work properly vs having the 2 columns. Or Excel warns them... then you have people that go "Why does Excel keep asking me about macros?" If everybody using it has em on and all, you're good. but otherwise... :P

edit: Make sure you check that edit. You couldn't just clear the contents of a cell without the event filling in a bad link. I fixed it so it'd let you do that now.