Hyperlink Cell Reference

[ryan_law2000]

Is there a way that under "Type the Cell Reference" that you can make it automatically change to the cell the hyperlink is on?

The reason why i ask this is because I have hyperlinks linking to there current cell but once i delete a row above that... the cell refernece doesnt change therefore changing the cell reference to the cell above it

 

[ryan_law2000]

All of colum A from 5 down has a hyperlink opening a userform (in this userform when completed, it will delete the active row)

Example:

A6 has a hyperlink with a cell reference of A6 (which is needed)
So if A5 is clicked and finished that row will be deleted, therfore bringing A6 into the A5 spot but NOT changing the Cell Refernce of A6 to A5...
I need these cell refernces to match the current cell of the hyperlink
I hope this explanes it better, any help?

 

[Norie]

ryan

Why are you using a hyperlink to open a userform?

In fact how are you actually doing that?

 

[ryan_law2000]

It took a while but Here are the codes:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
UserForm1.Show
End Sub


And I go from there, I figure that a hyperlink is easier because im going to have about 500 rows in this form... Which brings up another problem
IS there a fast way to bring the hyperlinks and there correct cell refernce down through out 500 rows?
I wondering if maybe under cell refernce there could be a formula there like "curent cell" or something with that kind of idea

 

[Norie]

ryan

Couldn't you just use an event like SelectionChange?

 

[ryan_law2000]

No idea what that means lol sorry
can you be more specific?

 

[Norie]

ryan

If you can be more specific so can I?

What is the purpose of the userform and why the need for hyperlinks?

Let's say you want the userform to appear when the user clicks in any cell in A1:A500 and display, in a textbox, the value from the worksheet.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Intersect(Range("A1:A500"), Target) Is Nothing Then Exit Sub
    
    UserForm1.TextBox1 = Target.Value
    UserForm1.Show
    
End Sub

 

[ryan_law2000]

WOW that works WAY BETTER! only one problem now
When I have text in the cell that is clicked example "REMOVE"
then "REMOVE" shows up in the userform under the first text box... can i fix this?

 

[Norie]

ryan

For that just remove this line.

    UserForm1.TextBox1 = Target.Value

The reason I included it was to show how you can transfer data based on the row selected to the userform.

Isn't that along the lines of what you ultimately want to do?

 

[ryan_law2000]

Never mind all got rid of the
UserForm1.TextBox1 = Target.Value

and it works absolutly perfect, thanks so much Norie