Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Is there a way to trigger a sub from inside another sub?

  1. #1
    Board Regular
    Join Date
    May 2019
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Is there a way to trigger a sub from inside another sub?

    I have a commandbutton_click sub that I would like to code so that a folder is created. Is there a way to call on a function/sub and to have it run its code and then go back to the commandbutton_click sub to finish?

    The function is below...

    Code:
    Function MkDir(strDir As String, strPath As String)
    
    
    Dim fso As New FileSystemObject
    Dim path1 As String
    
    
    'examples for what are the input arguments
    strDir = thisMonday & " Thru " & thisSunday 
    strPath = Environ("Userprofile") & "\Desktop\bn9\"
    
    
    path1 = strPath & strDir
    
    
    If Not fso.FolderExists(path1) Then
    
    
    ' doesn't exist, so create the folder
              fso.CreateFolder path1
    
    
    End If
    
    
    End Function

  2. #2
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,121
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way to trigger a sub from inside another sub?

    To use a function, you just need to put the name and the elements (in this case
    strDir as text and
    strPath as text), so
    Code:
    Sub btnclick
    
    
    MkDir("Directory you want","Path you want") End sub
    If it was a sub and not a function, you would use

    Code:
    Sub btnclick
    
    Call MkDir End sub



    Last edited by Kamolga; Jul 29th, 2019 at 03:57 PM.

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,194
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Is there a way to trigger a sub from inside another sub?

    First, MkDir() is a function that already exists in VBA, you should use another name, for example Mk_Dir()


    The arguments for the function, at least for me, should go in this order, directory and new folder:

    Try this:

    Code:
    Private Sub CommandButton1_Click()
      Dim wPath As String, wNewFolder As String
      wPath = "C:\trabajo\books\"          'Directory Name
      wNewFolder = "NameFolder"           'Name of the new folder
      If Mk_Dir(wPath, wNewFolder) = True Then
        '
        'Here you could continue another code.
        '
      Else
        MsgBox "The folder exists"
      End If
    End Sub
    
    
    Function Mk_Dir(strPath As String, wNewFolder As String)
      Dim fso As New FileSystemObject
      Dim path1 As String
      
      path1 = strPath & wNewFolder
      
      If Not fso.FolderExists(path1) Then
      ' doesn't exist, so create the folder
        fso.CreateFolder path1
        Mk_Dir = True
      Else
        Mk_Dir = False
      End If
    End Function
    Regards Dante Amor

  4. #4
    Board Regular
    Join Date
    May 2019
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way to trigger a sub from inside another sub?

    Thanks both you guys! I really appreciate the detail DanteAmor. I am still having issues running my code though. I run into an error that says "ByRef type mismatch" when I try to call on my function with the if statement. Any ideas? My full code is below.

    Code:
    Private Sub CommandButton1_Click()
        
        Pth3 = Environ("Userprofile") & "\Desktop\Folder\"
    
    
        OpeningVar3 = "Template.xlsx"
        
        Set Wbk3 = Workbooks.Open(Pth3 & OpeningVar3)
    
    
        If Application.CommandBars("Ribbon").Height <= 150 Then
            CommandBars.ExecuteMso "HideRibbon"
        End If
    
    
        With Wbk3
        
            Wbk3.ActiveSheet.Label1.Caption = UserForm4.TextBox1.Value
            Wbk3.ActiveSheet.Label2.Caption = Date
            
        End With
    
    
        If Mk_Dir(StrPath1, NewFolder) = True Then
        
            ActiveWorkbook.SaveCopyAs Environ("Userprofile") & "\Desktop\Folder\" & NewFolder & "\" & UserForm4.TextBox1.Value & " " & Format(Date, "mm_dd_yy") & ".xlsx"
        
        Else
        
           ActiveWorkbook.SaveCopyAs Environ("Userprofile") & "\Desktop\Folder\" & NewFolder & "\" & UserForm4.TextBox1.Value & " " & Format(Date, "mm_dd_yy") & ".xlsx"
        
        End If
        
    End Sub
    
    Function Mk_Dir(StrPath1 As String, NewFolder As String)
    
    
    Dim thisMonday As Date
    Dim thisSunday As Date
    Dim StrPath1 As String
    Dim NewFolder As String
    Dim fso As New FileSystemObject
    Dim path1 As String
    
    
    thisMonday = Date - Weekday(Date, vbMonday) + 1
    thisSunday = Date + 7 - Weekday(Date, vbMonday)
    
    
    'examples for what are the input arguments
    NewFolder = thisMonday & " Thru " & thisSunday
    StrPath1 = Environ("Userprofile") & "\Desktop\Folder\"
    
    
    path1 = StrPath1 & NewFolder
    
    
    If Not fso.FolderExists(path1) Then
    
    
    ' doesn't exist, so create the folder
        fso.CreateFolder path1
        Mk_Dir = True
    
    
    Else
    
    
        Mk_Dir = False
    
    
    End If
    
    
    End Function
    Last edited by MistakesWereMade; Jul 30th, 2019 at 08:23 AM.

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,194
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Is there a way to trigger a sub from inside another sub?

    Quote Originally Posted by MistakesWereMade View Post
    Thanks both you guys! I really appreciate the detail DanteAmor. I am still having issues running my code though. I run into an error that says "ByRef type mismatch" when I try to call on my function with the if statement. Any ideas? My full code is below.
    Your code had several problems, I had to redo several parts.


    From what I understood, you need something like this.:
    Code:
    Private Sub CommandButton1_Click()
      Dim Pth3 As String, OpeningVar3 As String, Wbk3 As Workbook
      Dim thisMonday As String, thisSunday As String, NewFolder As String
        
        Pth3 = Environ("Userprofile") & "\Desktop\Folder\"
        OpeningVar3 = "Template.xlsx"
        Set Wbk3 = Workbooks.Open(Pth3 & OpeningVar3)
        If Application.CommandBars("Ribbon").Height <= 150 Then
            CommandBars.ExecuteMso "HideRibbon"
        End If
        With Wbk3
            Wbk3.ActiveSheet.Label1.Caption = UserForm4.TextBox1.Value
            Wbk3.ActiveSheet.Label2.Caption = Date
        End With
        thisMonday = Format(Date - Weekday(Date, vbMonday) + 1, "dd_mm_yyyy")
        thisSunday = Format(Date + 7 - Weekday(Date, vbMonday), "dd_mm_yyyy")
        NewFolder = thisMonday & " Thru " & thisSunday
        res = Mk_Dir(Pth3, NewFolder)
        If res = "" Then
            ActiveWorkbook.SaveCopyAs Pth3 & NewFolder & "\" & UserForm4.TextBox1.Value & " " & Format(Date, "mm_dd_yy") & ".xlsx"
        Else
            MsgBox res
        End If
    End Sub
    
    
    Function Mk_Dir(StrPath1 As String, NewFolder As String)
      Dim path1 As String, fso As New FileSystemObject
      path1 = StrPath1 & NewFolder
      If Not fso.FolderExists(path1) Then
        On Error Resume Next
            fso.CreateFolder path1
        If Err.Number = 0 Then
          Mk_Dir = ""
        Else
          Mk_Dir = "Error: " & Err.Number & " Description: " & Err.Description
        End If
      End If
    End Function

    I changed the date format to dd_mm_yyyy to create the folder, you cannot create a file with the slash (/)
    Regards Dante Amor

  6. #6
    Board Regular
    Join Date
    May 2019
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way to trigger a sub from inside another sub?

    Thanks for the edits. I still get an error though right at "res = Mk_Dir". It says Argument not optional. Below is my code with your edits and a few more. Thanks for all your help!

    Code:
    Private Sub CommandButton1_Click()
    
    
    Dim Wbk3 As Workbook
    Dim Pth3 As String
    Dim OpeningVar3 As String
    
    
    Dim StrPath1 As String
    Dim NewFolder As String
    Dim thisMonday As String
    Dim thisSunday As String
    
    
        Pth3 = Environ("Userprofile") & "\Desktop\Folder\"
    
    
        OpeningVar3 = "Template.xlsx"
        
        Set Wbk3 = Workbooks.Open(Pth3 & OpeningVar3)
    
    
        If Application.CommandBars("Ribbon").Height <= 150 Then
            CommandBars.ExecuteMso "HideRibbon"
        End If
    
    
        With Wbk3
        
            Wbk3.ActiveSheet.Label1.Caption = UserForm4.TextBox1.Value
            Wbk3.ActiveSheet.Label2.Caption = Date
            
        End With
    
    
    thisMonday = Format(Date - Weekday(Date, vbMonday) + 1, "mm_dd_yy")
    thisSunday = Format(Date + 7 - Weekday(Date, vbMonday), "mm_dd_yy")
    
    
    NewFolder = thisMonday & " Thru " & thisSunday
    StrPath1 = Environ("Userprofile") & "\Desktop\Folder\kk\mm\"
    
    
    res = Mk_Dir(StrPath1, NewFolder)
    
    
    If res = "" Then
        
        ActiveWorkbook.SaveCopyAs Environ("Userprofile") & "\Desktop\Folder\kk\mm\" & NewFolder & "\" & UserForm4.TextBox1.Value & " " & Format(Date, "mm_dd_yy") & ".xlsx"
        
    Else
        
        ActiveWorkbook.SaveCopyAs Environ("Userprofile") & "\Desktop\Folder\kk\mm\" & NewFolder & "\" & UserForm4.TextBox1.Value & " " & Format(Date, "mm_dd_yy") & ".xlsx"
        
    End If
        
    End Sub
    
    
    
    
    Function Mk_Dir(StrPath1 As String, NewFolder As String, thisMonday As Date, thisSunday As Date)
    
    
    Dim fso As New FileSystemObject
    Dim path1 As String
    
    
    path1 = StrPath1 & NewFolder
    
    
    If Not fso.FolderExists(path1) Then
    
    
        On Error Resume Next
        ' doesn't exist, so create the folder
        fso.CreateFolder path1
        
        If Err.Number = 0 Then
            Mk_Dir = ""
        Else
            Mk_Dir = "Error: " & Err.Number & " Description: " & Err.Description
        End If
    End If
    
    
    End Function

  7. #7
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,033
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Is there a way to trigger a sub from inside another sub?

    Hi,
    untested but try these updates to both your codes & see if help you

    Code:
    Private Sub CommandButton1_Click()
      Dim Pth3 As String, OpeningVar3 As String
      Dim thisMonday As String, thisSunday As String, NewFolder As String
      Dim Wbk3 As Workbook
        
        On Error GoTo myerror
        Pth3 = Environ("Userprofile") & "\Desktop\Folder\"
        
        OpeningVar3 = "Template.xlsx"
        
        thisMonday = Format(Date - Weekday(Date, vbMonday) + 1, "dd_mm_yyyy")
        thisSunday = Format(Date + 7 - Weekday(Date, vbMonday), "dd_mm_yyyy")
        NewFolder = thisMonday & " Thru " & thisSunday
        
        Application.ScreenUpdating = False
        Set Wbk3 = Workbooks.Open(Pth3 & OpeningVar3, False, True)
        If Application.CommandBars("Ribbon").Height <= 150 Then CommandBars.ExecuteMso "HideRibbon"
        
        With Wbk3.Sheets(1)
            .Label1.Caption = Me.TextBox1.Value
            .Label2.Caption = Date
        End With
                
        If Mk_Dir(Pth3, NewFolder) Then Wbk3.SaveCopyAs Pth3 & NewFolder & "\" & _
                                                        Me.TextBox1.Value & _
                                                        " " & Format(Date, "mm_dd_yy")
    myerror:
        If Not Wbk3 Is Nothing Then Wbk3.Close False
        Application.ScreenUpdating = True
        If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
    End Sub
    
    
    
    
    Function Mk_Dir(ByVal StrPath1 As String, ByVal NewFolder As String) As Boolean
      Dim path1 As String, fso As New FileSystemObject
      path1 = StrPath1 & NewFolder
      If Not fso.FolderExists(path1) Then fso.CreateFolder path1
      Mk_Dir = True
    End Function
    I have assumed Userform4 is the active form & replaced name with Me keyword

    Dave
    Last edited by dmt32; Jul 30th, 2019 at 11:38 AM.

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,194
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Is there a way to trigger a sub from inside another sub?

    Try this

    Code:
    Function Mk_Dir(StrPath1 As String, NewFolder As String)
    Regards Dante Amor

  9. #9
    Board Regular
    Join Date
    May 2019
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way to trigger a sub from inside another sub?

    Thanks Dave! It seems to be working a little more fluidly, but it still gets caught up on the function portion. It highlights when I declare my fso variable as "New FileSystemObject" and errors with "Compile Error: User-defined type not defined". Any ideas on this?

  10. #10
    Board Regular
    Join Date
    May 2019
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Is there a way to trigger a sub from inside another sub?

    Thanks for catching that. Yeah, it progressed through the code a little more but it receives the same error as with Dave's code unfortunately.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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