VBA Call function

[brenny]

hey everybody
Ok i dont wanna do a simple call function, waht i wanna do is have the call from a string.

example

call (add string here)

what i have is in cell A1 a first name and B1 a surname

A1 = Joe
B1 = Blow

mind you there are over 300 names in the sheet.

in cell C1 is a button with a macro linking it to a file, that works also.

Ive got the find function working (search via surname), and i have alittle form come up when excel finds the name that you has "use file" and "close" on it.

When the user clicks close, the form goes, but when the user clicks use file i want it to call the macro for joe blow to open the file. The file is

macro name is BlowJoe

so you search Joe Blow, click 'use file' excel calls the macro BlowJoe.

i can get the surname and first name into strings, but cant get it to call from a string.

code so far is

Sub userfile_click() 
     
    Dim fname As String 'first name
    Dim sname As String 'surname
    Dim wname As String 'whole name (macro name)
     
    sname = activecell 
    activecell.offset(0,1).activate 
    fname = activecell 
    wname = sname & fname 
     
    Call wname 'this is where im lost, cant get it to call from a string, can you do it?
     
    End 'to close the form
End Sub 

thanx for you help everybody, hope this isnt to hard

Regards Always
Brenny

[Dave Hawley]

Hi Brenny

Welcome to ozgrid

Please use the code tags when posting code. You agreed to this when joining

[brenny]

sorry, hope this is better
brenny

[acw]

Hi

What does the macro BlowJoe actually do??? If it just opens a particular file name then why not open the file in the current macro?

If it opens a specific file, and performs some actions on it and those actions are the same on every file opened, only the file name is different, then why not call a macro and pass it the required file name???


Tony

[brenny]

the macro Joe blow opens the the file blowjoe.xls (joe blow is just a mock name for this).

Sorry stupid me forgot to mention each macro name is different, each macro is named by the employees surnmae and first name so joe blows macro name would look like BlowJoe()

I dont want to open it in the current macro, because there are 300 names on the sheet and 300 excel files (one for each employee).

Next to each name I have a button which opens the users respective file, which is fine and works. however, im fussy.

When i click the search button, i type in the surname of the employee, it searches col A till theres a match then stop. Then i get the prompt (which is done on a form in VBA) find next, use file, close. if the user clicks find next, it searchs for the next name in the list, if the user clicks close the form closes and the user works as normal, however if the user clicks use file, i want it to call the macro for the user that the name is on. thats why i want to call the macro for the user from a string. does that make sence, i hope it does?

If it was as simple as as modifying a few things in the open file function id have no probs, but each employee doesnt work at the same location, they work at different centres, and we have each file saved in thir respective centre.

A file name may look like the one below

BlowJoe (add centre name here) (a).xls

depending on the centre hes at will be stored in that gile.

(a) is a naming convention we use, which is irrelevant to this.

hope this makes it eaiser to understand what im trying to do

A macro would look like

Sub BlowJoe() 
     
    Workbooks.Open Filename:= _ 
    "c:\employee files\<centre they work at>\<Job>\BlowJoe.xls" 'this is an example of how we work our namings and all
     
End Sub 

Regards Always
Brenny

[brenny]

actually I just reread what you typed a few times, can you tell me how to pass a file name from one macro to another macro? If you just give me an example code, ill be able to work out what it does. hope thats ok.

Regards Always
Brenny

[rbrhodes]

Hi,

Re: passing a filename (as a variable)

 'Declared wname as public
Public wname As String 
Sub userfile_click() 
     
    Dim fname As String 'first name
    Dim sname As String 'surname
     'Took wname out of here
     
     
     'Changed .activate code to .offfset code
    sname = ActiveCell 
    fname = ActiveCell.Offset(0, 1) 
    wname = sname & fname & ".xls" 
     
     'Call fileopen sub
    Call getfile 
     
    End 'to close the form
End Sub 
 
Sub getfile() 
     
     'Uses wname from first sub
    Workbooks.Open FileName:="c:\My documents\" & wname 
     
End Sub 

Cheers,

dr

[acw]

Or you can go

Sub userfile_click() 
     
    Dim fname As String 'first name
    Dim sname As String 'surname
     'Took wname out of here
     
     
     'Changed .activate code to .offfset code
    sname = ActiveCell 
    fname = ActiveCell.Offset(0, 1) 
    wname = sname & fname & ".xls" 
     
     'Call fileopen sub
    Call getfile(wname) 
     
    End 'to close the form
End Sub 
 
Sub getfile(nametoopen) 
     
     'Uses wname from first sub
    Workbooks.Open FileName:="c:\My documents\" & nametoopen 
     
End Sub 

Difference is that you don't have a global variable.


Tony