Announcement

Collapse
No announcement yet.

VBA Call function

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

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

  • #2
    Re: VBA Call function

    Hi Brenny

    Welcome to ozgrid

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

    Comment


    • #3
      Re: VBA Call function

      sorry, hope this is better
      brenny

      Comment


      • #4
        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

        Comment


        • #5
          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

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

          Comment


          • #6
            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

            Comment


            • #7
              Re: VBA Call function

              Hi,

              Re: passing a filename (as a variable)

              Code:
              '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
              Cheers,

              dr

              "Questions, help and advice for free, small projects by donation. large projects by quote"

              http://www.ExcelVBA.joellerabu.com

              Comment


              • #8
                Re: VBA Call function

                Or you can go

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

                Comment

                Working...
                X