Bit of VBA help, please?

[picklefactory]

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?

 

[scifibum]

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:

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]

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]

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