locked
Help with vba urgent RRS feed

  • Question

  • Hi,

    I'm working on making a file where you can enter an amount of data, and when you press a certain button, an email will be generated.

    Now I added a userform that opens when you open the excel file. It asks you what language you like the email to be written in.

    My question is:

    first it needs to remember what optionbutton you've picked.

    Then when you press continue, the pop up needs to dissapear.

    Then when I click on the button to generate the email, it needs to remember what language I've selected, and then go to that specific sub and run it.

    I already created the subs with the different languages, so it just needs to refer to that sub.

    Can anyone help me with it.

    I've got this so far:

    the code in the userform:

    Public languages As Integer

    Private Sub CommandButton1_Click()

    Select Case True
    Case Me.OptionButton1
    languages = 1
    Case Me.OptionButton2
    languages = 2
    Case Me.OptionButton3
    languages = 3
    Case Me.OptionButton4
    languages = 4
    Case Me.OptionButton5
    languages = 5
    Case Me.OptionButton6
    languages = 6
    Case Else
    End Select

       UserForm1.Hide
      
    End Sub

    the code in the module:

    Sub cmdSendEmailCustomer()

    If UserForm1.languages = 1 Then Module1.English
    If UserForm1.languages = 2 Then Module1.German

    If UserForm1.languages = 3 Then Module1.French
    If UserForm1.languages = 4 Then Module1.Spanish
    If UserForm1.languages = 5 Then Module1.Italian
    If UserForm1.languages = 6 Then Module1.Russian

    End Sub

    But this doen't work, what am I doing wrong??

    Monday, December 24, 2012 2:09 PM

All replies

  • Change the form code to

    Option Explicit

    Private Sub CommandButton1_Click()
        With Me
            Select Case True
                Case .OptionButton1.Value
                    .Tag = 1
                Case .OptionButton2.Value
                    .Tag = 2
                Case Me.OptionButton3.Value
                    .Tag = 3
                Case Me.OptionButton4.Value
                    .Tag = 4
                Case Me.OptionButton5.Value
                    .Tag = 5
                Case Me.OptionButton6.Value
                    .Tag = 6
                Case Else
            End Select
            .Hide
        End With
    End Sub

    and the calling macro to

    Option Explicit
    Sub cmdSendEmailCustomer()
        With UserForm1
            .Show
            Select Case .Tag
                Case Is = 1: Module1.English
                Case Is = 2: Module1.German
                Case Is = 3: Module1.French
                Case Is = 4: Module1.Spanish
                Case Is = 5: Module1.Italian
                Case Is = 6: Module1.Russian
            End Select
        End With
        Unload UserForm1
    End Sub

    You haven't indicated what is in Module1 so we will have to assume the language macros work.


    Graham Mayor - Word MVP
    www.gmayor.com

    Monday, December 24, 2012 3:30 PM
  • Hi,

    it works, thank you soooooo much!!

    but 1 problem:

    so I select the language. pop up goes away.

    Then push the email button, but then he show's the language pop up again.

    How come?

    Monday, December 24, 2012 4:11 PM
  • To establish why the macro is called again we would need to know what the language macros do and how you are calling the original macro. Also you have not said which application, though userforms are fairly universal. It is also a good idea to use more meaningful names for the various components than the default names.

    While my earlier suggestion followed your lead, personally I would change the userform code and perform the processing in the calling macro e.g.

    Option Explicit

    Private Sub CommandButton1_Click()
        'OK Button
        With Me
            .Hide
            .Tag = 1
        End With
    End Sub

    Private Sub CommandButton2_Click()
        'Cancel button
        With Me
            .Hide
            .Tag = 0
        End With
    End Sub

    In Module1

    Option Explicit

    Sub cmdSendEmailCustomer()
        With UserForm1
            .OptionButton1.Value = True
            .Show
            If .Tag = 0 Then Exit Sub
            Select Case True
                Case Is = .OptionButton1.Value: Module1.English
                Case Is = .OptionButton2.Value: Module1.German
                Case Is = .OptionButton3.Value: Module1.French
                Case Is = .OptionButton4.Value: Module1.Spanish
                Case Is = .OptionButton5.Value: Module1.Italian
                Case Is = .OptionButton6.Value: Module1.Russian
            End Select
        End With
        Unload UserForm1
    End Sub

    Sub English()
        'Run your code for English here e.g.
        MsgBox "English"
    End Sub
    Sub German()
        MsgBox "German"
    End Sub
    Sub French()
        MsgBox "French"
    End Sub
    Sub Spanish()
        MsgBox "Spanish"
    End Sub
    Sub Italian()
        MsgBox "Italian"
    End Sub
    Sub Russian()
        MsgBox "Russian"
    End Sub


    Graham Mayor - Word MVP
    www.gmayor.com

    Tuesday, December 25, 2012 10:19 AM
  • Hi Graham,

    thanks for the input, for the moment I just resolved my problem with not showing the pop up when the file is opened, but only show it when you press the email button.

    It works perfectly like that with your original code.

    I will try your suggestion as well, only I'm working on the clock here, and I need to add a lot more stuff.

    I want to thank you for your help, and maybe when I have another problem you can save me again :)

    Thanks a lot,

    greetings

    Natje

    Tuesday, December 25, 2012 10:45 AM