Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Constants Declared, howto call sub from userform

    Hello,

    I have a userform named UserForm1 with a command button named SubmitButton.

    In Module this Constants are declared on top. How do i call this sub Test from UserForm1 command button named SubmitButton.

    Code:
    Option Explicit
        Const Prin1 As String = "HP LaserJet M3027 mfp PCL6 on LPT1:"
        Const Prin2 As String = "HP LaserJet 4250 PCL6 on LPT2:"
        Const AcroPath As String = "C:\Program Files\Adobe\Reader 11.0\Reader\AcroRd32.exe"
    
        Private Declare Function SetDefaultPrinter Lib "winspool.drv" _
        Alias "SetDefaultPrinterA" (ByVal pszPrinter As String) As Long
    
    
    Sub Test()
     Dim ws As Worksheet
    ....

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In the UserForm1 module add the code:

    Code:
    Private Sub SubmitButton_Click()
    Test
    End Sub
    Steve

  3. #3
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    In the UserForm1 module add the code:

    Code:
    Private Sub SubmitButton_Click()
    Test
    End Sub
    Steve
    Thanks Steve, But gives
    Compile error:
    Expected variable or procedure, not module

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Foncesa,

    Try declaring the Test sub routine as Public, e.g: Public Sub Test()
    You may also want to add Public to the Const declarations.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Unless I explicitly added "PRIVATE", I did not get the error message, it did not require the PUBLIC before the SUB Test()

    Are you sure the name of the macro is TEST?

    Is it in the same workbook as the userform? If it is in a different workbook, you need to tell the macro where it is, for example:

    Code:
    Private Sub SubmitButton_Click()
    Application.Run "'Another Book.xlsm'!Test"
    End Sub
    Steve

  6. The Following User Says Thank You to sdckapr For This Useful Post:

    foncesa (2013-11-07)

  7. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It sounds as though you named your module Test as well, in which case use Test.Test while calling the routine or, better still, change the name of one of them!
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    foncesa,

    You should always move your subroutines to a standard module and leave only the code in a form related to its controls. Subroutines on a form, like their controls, called from outside the form must be accessed by the name of the form followed by the routines name such as Userform1.Test.

    Once in a standard module, you can access the routine with the statement mentioned above:

    Code:
    Private Sub SubmitButton_Click()
       Test
    End Sub

Posting Permissions

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