Replace or override sub code

[Annichka]

Hi! so I'm very new with Vba in excel. i have a code that sends specific data (from excel files) to an access database. i wrote a code that loops through several hundred files and sends this information. The problem is to manually update these files, it is sent with a button. I need to override the current sub:

Public Sub Send_Record_Click()
MsgBox ("Not Functional at present")
End Sub

with a code that connects to another sub in Module 1.
Originally i was thinking to just replace the module with the Send_Record_Click() with an identical one that has the right updated version, but this is a code on a sheet with a several other codes and not a module.
Another idea was to run a If Then Sub that would look at this sub and if it had this msgbox code it would replace it with the call SendToAccess sub I have in Module 1.
Any other ideas and code samples are very much welcome if this is even possible to do.

 

[Joe4]

Welcome to the Board!

Your question seems a bit confusing to me, but I am thinking you can break these different options out into their own Subs, and then based on some decision in your main sub, decide which one to call, i.e.

If [COLOR=#0000ff][I]some condition[/I][/COLOR] then
    Call Sub1
Else
    Call Sub2
End If

If that doesn't help you out, we are going to need more clarification/details of your situation.

 

[Annichka]

thank you! ok so thats not quite what i needed. The code im writing loops through about 700 files and sends specific cells in an excel workbook into a database. Unfortunately to manually send it you double click a button that goes to that Sub Send_Record_Click(). but because these files were created before the database, the double click just pops up with the msgbox. I need some help in order to write a sub that will help automatically change the Send_Record_Click() sub from a msg box to a call module.

 

[MARK858]

but this is a code on a sheet with a several other codes and not a module

Is the code worksheet event code or a regular sub?

 

[Annichka]

its an event code I believe.

 

[Joe4]

Without seeing all the code involved and working through an actual example, I find it very confusing and am not very clear on exactly how you expect this to work.
This line of note is confusing:

Unfortunately to manually send it you double click a button that goes to that Sub Send_Record_Click(). but because these files were created before the database, the double click just pops up with the msgbox. I need some help in order to write a sub that will help automatically change the Send_Record_Click() sub from a msg box to a call module.

What do you mean that the "files were created before the database"? Where, why, how is the database being created?
What exactly is the determinging the decision on what to do? Do you want to check for the existence of the database first?

 

[mdmilner]

i so had to re-read this to finally understand the question.

I think your issue is, some of the workbooks were created first (and they have the "not functional" message in them). Later, you changed the process and added a code module into it that had a function call in it to do what you needed.

So, first, I don't think you can do it the way you are proposing.

You need to re-write your controlling process (that loops through the list of 700 files) so that it doesn't need to make calls into code modules inside each of the 700 workbooks. Instead, have the source loop/control code do it. That's without seeing exactly what you're doing but very generally if you're just opening a file then grabbing values from a specific worksheet/cell location, you can automate that centrally.

 

[Annichka]

mdmilner,
okay thank you so much! that was basically the issue. So thats what i was coming to the conclusion of. I dont think its possible to do what i was trying to.
Thank you again for your help!