Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Convert Simple Excel 4.0 Macro to VBA

0

Hello -

=FORMULA.GOTO(DEREF(Start!$B$1))

= Return

I have the above 4.0 macro that is assigned to each of the team names that appear on the top of each page of the Games and First Five tabs of the attached workbook (As you scroll down you will notice that there are 80 team names in each - so 80 Macros per tab..The  Macros for the Games tab are saved in the Macro Tab in Columns A + B.

The Ganmes tab shows each team's last 10 games. However, elsewhere in the Workbook is a section for each team's results for the entire year. That section starts with a cell that ihas the team's name in it. I have made that single cell a defined name. 

I have assigned a transparent Macro object to each Team name in the Games and First Five tabs. So if a user want to see more than just the last 10 games they can hover over the team's name and the mouse changes to a hand. They click on the team name and the Macro will take them to the defined name cell that matches the team name they jsut click on where they will see that team's Year to Date stats and not just the last 10.

I would like to convert these 4.0 Macros to VBA. I assume that would also be one VBA macro for each of these teams headings.

After this spreadsheet opens, it will automatically hide row and column headings and fthe formula bar. Hit CTRL+Y to unhide all and CTRL + M to hide alll again.

Thank you in advance for your help.

Answer
Discuss

Answers

0
Selected Answer

Marko

I suggest you don't try to convert that code to VBA but use a simpler method (which will still allow you to force users to run the file with your other VBA macros enabled).

In the attached files, I've removed the (Excel 4.0) Macros sheet and made a couple of examples of using Excel's Hyperlink function- click in either of the orange cells "Braves" or "Braves Away") on sheet Games and you'll jump to those places in sheet MLB.

This is how I did those two examples in the Games sheet:

Firstly I realised that your Excel 4.0 code seemed to be linked to transparent shapes above cells like F2. I right clicked to select one, then used Ctrl+A to select all and Delete to remove them.

Cell F2 has formula:

=Start!B1

so shows "Braves". Others do likewise.

I right-clicked F2 then clicked Link at the bottom of the sub-menu.

On the left hand side of the resultant Insert Hyperlink requestor, under "Link to:", I clicked "Place in This Document".  Then in "Type the cell reference", I pasted:

MLB!DZ13

(meaning cell DZ13 in sheet MLB) then clicked OK.

That put the link in F2 but the unfortunately default hyperlinks are blue (so very hard to see against a blue fill!). I set the font to white (and the fill to orange, just for illustration). You could also choose to remove the underline from the hypelink (as you would with regular text) but most people are accustomed to seeing underscored hyperlinks.

For cell T2 ("Braves Away"), the hyperlink formula is just

MLB!EW13

and I could use the Format Painter to copy the font colour/ fill from cell F2.

This seems to work well but your task would be to add similar hyperlinks, as above (and changing the address in MLB to suit).

Hope this fixes things for you. If so, please remember to mark this Answer as Selected.

Discuss

Discussion

Once again - Thank you for your time and effort with this issue. It is very helpful!
marko14 (rep: 8) Aug 5, '23 at 12:31 pm
Great. Thanks for selecting my Answer, Marko. 
John_Ru (rep: 6297) Aug 5, '23 at 4:06 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login