Bit of VBA help, please?

picklefactory

Active Member
Joined
Jan 28, 2005
Messages
412
Hi folks. Wonder if anyone can help? I have 2 individual bits of code that do a couple of simple filters and navigation jumps, no problem there, they work just fine. What I then have, is a macro that runs both of them one after the other with a button on the sheet. No problem there either so far, it all works fine up to this point. My problem is that the macro with the button recognises the initial 2 functions with a specific reference to the original workbook I created them in, but I am using that workbook as a master for various projects and saving the entire workbook under a new filepath each time I use the master. Unfortunately, the references to the 2 bits of code are keeping the original label so when I try and run the button macro with the new filepath, it fails to find them. Here is my button macro.

Sub Machinedatafilterandjump()
'
' Machinedatafilterandjump Macro
' Filter plant no's and jump to current tools
'

'
Application.Run "'Project Workbook a.xls'!Filter"
Application.Run "'Project Workbook a.xls'!Title"
End Sub

What I need is for the applications 'Filter' and 'Title' to migrate with the workbook under it's new filepath, whatever that may happen to be.

Does that make any sense to anyone?
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

scifibum

Well-known Member
Joined
Jul 22, 2004
Messages
503
Hi,

I think you can simply avoid using the Run method. VBA will run a macro if you just supply it's name as a command (if the procedure name is visible in the current scope).

Here's what I mean:

Code:
Sub Machinedatafilterandjump() 
' 
' Machinedatafilterandjump Macro 
' Filter plant no's and jump to current tools 
' 

' 
    Filter
    Title
End Sub

This should work if your two macros are in standard code modules and aren't declared with the "Private" statement - or if they are "private" it will work as long as the macro above is in the same module with the two macros it is calling.

Does that help?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Some little comment:

avoid using terms like Filter, Title, Name, Item, Time, since they are used (needed) by VBA itself: that's why the first character changes in an uppercase automatically!

click in such aword within your code and press F1
the Help will appear on that item
sometimes this specific word is needed by VBA itself, that's why we don't use it

test this:
type: name = "excel"
pressing ENTER it will change in Name = "excel"

you can do the same with filter, title, item, ...

you see ?

regards,
Erik
 

picklefactory

Active Member
Joined
Jan 28, 2005
Messages
412
Thanks a bunch

Cheers fella's, it's easy when you know how, isn't it? That works a treat, usual sound advice I always find on this forum.
Thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,466
Members
414,069
Latest member
StudExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top