Hyperlink from one cell to another in 2010

[lawitcher1]

I have a worksheet that I want to click on a cell (highlighted in grey) at the top of the sheet (named: 10, 12, 15, 17, 20, 70, 40, 60, 80, 180 & Back to Log (back to log refers to the station log sheet)) and have the cursor go to the area in the same sheet that references the band, and in the case of the "Back to Log" go to the Station Log sheet. I have searched the forum, and found a couple of references, but still do not understand (step by step) the instructions on how to make a hyperlink. Please see attached

[NoSparks]

Larry, I had posted a reply here and then removed it. It makes no sense to me to do what's already been done.
You need to figure out where you're going with this or you'll be trying to re-invent the wheel over and over again.

Obviously this Excel program is to your liking or you would have abandoned it.

I've gone to the Kelly Klaas page and downloaded the .xls file so I can see the macros.
File was created in 2001 and was last updated in 2008, says do anything you want with it.

I loaded it into Excel 2010, filled in your stuff on the first sheet, copied your posted Band Plan info to the .xls Band Plan sheet, put the Worksheet_Change code onto the Station Log sheet, made a couple of alterations in the code where the file name was hard coded then saved as "ham log II 2010.xlsm"

See if this is something you can work with, it needs some tuning in the filter and print stuff but will cross that bridge when you get to it.

edit: Dec 2 edited file

[NoSparks]

I'm posting it here as it pertains to the file I posted in this thread and is a VBA macro issue.
This is in response to your thread at viewtopic.php?f=2&t=7261

I discovered that after entering a few Call Signs to see if things worked properly, and deleting them I had to copy a unused cell and paste it where I had deleted the cells in order to work again.

This is bothering me, I can't comprehend this at all.
What is happening or not happening the way you expect it to? or want it to?

Be aware the macro is (currently) only fired by a single cell changing value. Deleting or copying to more than one cell at a time will not fire the macro.

[NoSparks]

A cell change can be used to initiate a macro to do almost anything.
The contents of a cell being deleted is a change.
It's ridiculous to copy and paste a blank cell into a blank cell, something else is in play here.

Please tell me what you do that causes you to say it doesn't work properly.

What would like to happen when a duplicate is deleted?

[lawitcher1]

When I delete the cells that are duplicated, the information ithe sheet still acts as if the information is still there.

[NoSparks]

As the worksheet change event can only pertain to one cell you'll need to delete the column A cells one at a time with the delete key. I've added a little highlighting to the duplicate. Test this out and let me know if it's more to your liking.

Delete the entire Station Log module Worksheet_Change code and replace it with this.
Just click on SELECT ALL then copy and paste.

Code: Select all


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
   
'limit checking to col A below row 6
If Target.Column <> 1 Or Target.Row < 7 Or Target.Count <> 1 Then Exit Sub

'establish range to check for duplicate
Set rng = Range("A7:A" & Cells(Rows.Count, 1).End(xlUp).Row)

'prevent this event from calling itself
Application.EnableEvents = False

'if target is not empty
If Target.Value <> "" Then
'count how many times target appears in rng
    If Application.WorksheetFunction.CountIf(rng, Target.Value) = 1 Then
        'it's targets first occurrance, put date/time into adjacent cell
        Target.Offset(0, 1).Value = Now
        'remove any existing cell fill color
        Target.Interior.ColorIndex = 0
    Else
        'it's a duplicate
        'put date/time in adjacent cell
        Target.Offset(0, 1).Value = Now
        'indicate it's a duplicate
        Range("A3").Value = Target.Value
        'color the just entered duplicates cell
        Target.Interior.ColorIndex = 36
    End If
'if target is empty ie:been deleted
Else
    'remove date/time
    Target.Offset(0, 1).Value = ""
    'clear duplicate indication
    Range("A3").Value = ""
    'remove cell color
    Target.Interior.ColorIndex = 0
End If

'make sure events are re-enabled
Application.EnableEvents = True

End Sub


[lawitcher1]

Now, THAT is nice. What a treat to have the changes you made. The code works Perfectly. I have learned so much in the last few sessions. THANK YOU!

[NoSparks]

That's great, glad I could help.

How you making out with the UTC time thing?
If you need assistance with this you should probably post it as a separate topic.