I don’t know if this is the proper place for a question like I have, but if it isn’t, perhaps one of the forum moderators will remove it.
I have a series of Excel programs that run from a central Menu program. One of the features of the Menu program is that it allows the user to enter values to certain variables, variables such as different Sales Tax rates. Each of the called programs will then import from this Menu program, the information they need to run. I have the code to import this information, and if I manually run the macro that does this, it works, so I know this part of the program is working fine. My problem is that I need to have this information imported automatically as soon as the called program is loaded, and this I haven’t been able to accomplish. If this transfer of data didn’t occur until the user entered the first bit of data, while not ideal, would still be workable. With this thought in mind, I created the following formula in an out of the way cell. First, I tried having the formula call the macro that controlled the data transfer, however that didn’t work. Then I changed the formula to run a Function. The Function would then call a Macro. Everything in the macro works, up until the moment when it tries to import data from the Menu. The formula I’m using is:
=IF(SUM(B1:B5)>0,DataTransFunc(),"")
[PLEASE NOTE - For simplicity sake, while trying to figure out how to accomplish my goal, I have placed this formula on the same sheet and just a few columns to the right of the ‘B’ column. This saves having to worry about specifying the sheet that is involved and risk accidently making a typing error in that part of the formula. I’ll do that once I can get this to work properly.]
The DataTransFunc I have is as follows:
Function DataTransFunc()
Call ImportData 'ImportData is the Macro that should import the data
End Function
The macro I have that will do the data transfer is as follows:
Sub ImportData()
‘Here is where I have the actual VBA code to import the data. It works
'great if I run this macro manually.
End Sub
I’m wondering, is there any reserved word that the ‘ImportData’ subroutine needs to be called to automatically start? If so, what? I can easily change what I have here, that wouldn’t be any problem.
There must be some way to accomplish this, so I sure will appreciate any help you may be able to offer. If you know of some way, would it be possible for you to give me a sample of the correct code? You may reply through this forum or contact me direct.
If you are able to offer a solution to my problem, and if you are an author, I'll be so grateful that I may go and read one of your stories, maybe even give a ‘5’ vote. No, I’m just kidding. I would definitely read your story. Thank you in advance for any help you may offer.
Active Ink Slinger
If you're feeling bored during this Covid-19 epidemic I’d like to suggest
you take a peek at a story I collaborated with SueBrasil, a brilliant author.
It's about a mistake in judgment a lady makes concerning a friend, based
on the hurtful words of someone that only thinks of himself. Will that
conniving person succeed in ruining a beautiful friendship, or will she see
through his lies? It's gradually creeping up towards the 30,000 mark
and we’d love any votes or hearing whatever comments you may wish
to make. It is listed in my profile under ‘FAVOURITES’ as Apologize.
www.lushstories.com/stories/first-time/apologize.aspx
you take a peek at a story I collaborated with SueBrasil, a brilliant author.
It's about a mistake in judgment a lady makes concerning a friend, based
on the hurtful words of someone that only thinks of himself. Will that
conniving person succeed in ruining a beautiful friendship, or will she see
through his lies? It's gradually creeping up towards the 30,000 mark
and we’d love any votes or hearing whatever comments you may wish
to make. It is listed in my profile under ‘FAVOURITES’ as Apologize.
www.lushstories.com/stories/first-time/apologize.aspx
Good luck.