Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: VBA Call function

  1. #1
    Join Date
    28th July 2005
    Posts
    10

    VBA Call function

    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

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718

    Re: VBA Call function

    Hi Brenny

    Welcome to ozgrid

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

  3. #3
    Join Date
    28th July 2005
    Posts
    10

    Re: VBA Call function

    sorry, hope this is better
    brenny

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    6th May 2005
    Posts
    1,036

    Re: VBA Call function

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    28th July 2005
    Posts
    10

    Re: VBA Call function

    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

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    28th July 2005
    Posts
    10

    Re: VBA Call function

    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    24th December 2004
    Location
    Nanaimo, Vancouver Island, British Columbia, Canada
    Posts
    2,464

    Re: VBA Call function

    Hi,

    Re: passing a filename (as a variable)

    VB:
     '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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    6th May 2005
    Posts
    1,036

    Re: VBA Call function

    Or you can go

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Call Add-in .xla Function From Workbook
    By Scott Walker in forum EXCEL HELP
    Replies: 2
    Last Post: April 3rd, 2008, 05:34
  2. Help with lookup function call
    By JohnCorbin in forum EXCEL HELP
    Replies: 4
    Last Post: May 21st, 2006, 16:03
  3. Replies: 2
    Last Post: March 9th, 2006, 09:37
  4. #REF with VBA Function Call
    By TonyO in forum EXCEL HELP
    Replies: 1
    Last Post: January 12th, 2006, 05:54
  5. Use Worksheet Function to Call a Sub?
    By BobbyMurcerFan in forum EXCEL HELP
    Replies: 3
    Last Post: October 11th, 2004, 09:36

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno