Jump to content

Any Excel MVPs here?


2Far
 Share

Recommended Posts

VBA/Macro silliness.

Assigned a macro to a Hyperlink (it's own PITA) in the second column of a row, first column is intentionally left blank per 2Far's ancient & divine guidelines.

I want to add a subsequent row with copy/pasting the hyperlink and other cell formulas, but the hyperlink won't index to the new cell and if I remove the $ signs from the cell address, the hyperlink doesn't work at all.

Hyperlink macro:

Quote

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Select Case Target.Range.Address
        Case "$B$135"       'If I change this to "B135", the macro doesn't work
        Call GoToEnd
        End Select
         
End Sub

'The Hyperlink is in Cell B135

Sub GoToEnd finds the next empty cell in the row. I type in the new info in the next 5 cells in the row.

image.png.67c266a339cfb9b3a44ffe1db339ba0c.png

"New Test" is the Hyperlink

"Last Name", "First Name" & "Employee" are all entered manually

"Project #", "Location", "Date", "Reason", "Result" are pulled from the last 5 filled cells in the row

A different macro would add a new row & copy/paste the first cell & the last 5 cells into the new row. The formulas in the last 5 cells all index properly with the new row.

I really want to avoid having to write a new Private macro every time I add a new employee and this would not be user friendly to anyone else I sent it to.

 

Link to comment
Share on other sites



I was a Microsoft Access MVP for 3 years. Therefore I think this could be a job better fitted for Access than Excel. Are there any other complex calculations on the spreadsheet making Excel the better choice?

  • Heart 1
Link to comment
Share on other sites

16 minutes ago, Thaddeus Kosciuszko said:

If someone can read the OP and make sense of it, they are definitely on drugs, so you fire them.

I understood the question.   I retired before they figured out I was on drugs. 

I'll ponder this for a while.  I need to come down from my high, or get better drugs...  before I can answer.

  • Heart 1
  • Hugs 1
Link to comment
Share on other sites

OK...   One way to do this, considering you want 'easy' for other users, is to use a macro to add a new employee.

Select the cell for the link for the new employee link.    Use 'record a macro' and create the link for the new employee.  Keep recording if you have other 'steps' that can be automated for a new employee.  Save that macro.

The next step.  You need Excel to find the row for the new employee.  That code is added to your new macro, of course before the code that creates the link.

There was a simple way in a macro for Excel to find an empty cell in a column of data.  You selected range (or column) in a macro and Excel would find the end of the data in that range.  (I can't remember, exactly how now... I used the Google to figure that out a while ago).   I'd use that for Excel to find the first empty row.  Then I'd use that cell location as a variable.  Then you can use the variable as a way to 'build' the correct text for the new customer macro in the link.   Use variables in the code that creates the link.  That will create a 'custom' link for each new employee. 

Then add a "New Employee" button on the spreadsheet, and link your new macro to the button. 

 

Link to comment
Share on other sites

2 minutes ago, Bikeguy said:

OK...   One way to do this, considering you want 'easy' for other users, is to use a macro to add a new employee.

Select the cell for the link for the new employee link.    Use 'record a macro' and create the link for the new employee.  Keep recording if you have other 'steps' that can be automated for a new employee.  Save that macro.

The next step.  You need Excel to find the row for the new employee.  That code is added to your new macro, of course before the code that creates the link.

There was a simple way in a macro for Excel to find an empty cell in a row.  You selected range (or column) in a macro and Excel would find the end of the data in that range.  (I can't remember, exactly how now... I used the Google to figure that out a while ago).   I'd use that for Excel to find the first empty row.  Then I'd use that cell location as a variable.  Then you can use the variable as a way to 'build' the correct text for the new customer macro in the link.   Use variables in the code that creates the link.  That will create a 'custom' link for each new employee. 

Then add a "New Employee" button on the spreadsheet, and link your new macro to the button. 

 

 

Yeah, what he said.

 

 

Link to comment
Share on other sites

29 minutes ago, Bikeguy said:

OK...   One way to do this, considering you want 'easy' for other users, is to use a macro to add a new employee.

Select the cell for the link for the new employee link.    Use 'record a macro' and create the link for the new employee.  Keep recording if you have other 'steps' that can be automated for a new employee.  Save that macro.

The next step.  You need Excel to find the row for the new employee.  That code is added to your new macro, of course before the code that creates the link.

There was a simple way in a macro for Excel to find an empty cell in a column of data.  You selected range (or column) in a macro and Excel would find the end of the data in that range.  (I can't remember, exactly how now... I used the Google to figure that out a while ago).   I'd use that for Excel to find the first empty row.  Then I'd use that cell location as a variable.  Then you can use the variable as a way to 'build' the correct text for the new customer macro in the link.   Use variables in the code that creates the link.  That will create a 'custom' link for each new employee. 

Then add a "New Employee" button on the spreadsheet, and link your new macro to the button. 

 

Yeah, I got all that, easy peasy (it's still a little ugly).

Sub AddNewEmployee2()
'
    Application.Goto Reference:="NewRowFormulas"
    Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
    Application.Goto Reference:="NewRowFormulas"
    Selection.Copy
    ActiveCell.Offset(-1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Application.CutCopyMode = False
End Sub

 

The problem I have is that the Hyperlink-to-Macro ("New Test" in column B won't index to the the newly inserted row via Copy/Paste.

Link to comment
Share on other sites

8 minutes ago, Square Wheels said:

I didn't pretend to read or understand the problem, but I find I get hyperfocused on trying to force my original plan to work.  

Maybe look for a different solution?

Maybe. It isn't mission critical for the sheet to provide the necessary information. Just a whole lot easier to update. I have a similar sheet keep track of equipment, but I only hae to add a row 2-3 time per year. Not the big a deal to copy/past/adjust the macros.

Link to comment
Share on other sites

23 hours ago, 2Far said:

The problem I have is that the Hyperlink-to-Macro ("New Test" in column B won't index to the the newly inserted row via Copy/Paste.

OK... I've been busy with other stuff...

I'm assuming (99% sure I'm correct) when a user clicks on your link to run the macro, that cell becomes the active cell in the spreadsheet.  

For your link.  Use code that is not cell specific, use variables like this.  Then the position of the cell with the link doesn't matter.

Sub Test()
Dim Rnum As Integer, Cnum As Integer

Cnum = ActiveCell.Column
Rnum = ActiveCell.row

'In the sub CallToEnd use this to select the correct cell location.


ActiveSheet.Cells(Rnum, Cnum).Select

End Sub
 

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...