none
Why the code stops at the form? RRS feed

  • Question

  • Hello,

    I have created in VBA Excel a form with 2 option Buttons and a command button with the code below:

    Option Button FINAL

    Private Sub Final_Click()
    Dim ws As Object
    Dim Initial As Range
    Set ws = Sheets("Date Initiale")
    Set Initial = Sheets("Date Initiale").Range("A:A")
    ws.Activate
    Initial.Select
        Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
            FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(10, 1), Array(16, 1), Array(24, 1), _
            Array(30, 1), Array(39, 1), Array(90, 1), Array(92, 1), Array(121, 1), Array(137, 1), Array _
            (152, 1), Array(167, 1), Array(182, 1), Array(186, 1)), TrailingMinusNumbers:=True
        ws.Columns("A").Delete
        ws.Columns("N").Delete
       
        ws.Range("C:D,F:F").Select
        Selection.NumberFormat = "0"

    End Sub

    Option Button PREVIEW

    Private Sub Preview_Click()
    Dim ws As Worksheet
    Dim Initial As Range
    Set ws = Sheets("Date Initiale")
    Set Initial = Sheets("Date Initiale").Range("A:A")
    ws.Activate
    Initial.Select

        Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
            FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(15, 1), Array(24, 1), Array(29, 1), _
            Array(36, 1), Array(90, 1), Array(92, 1), Array(121, 1), Array(137, 1), Array(152, 1), _
            Array(164, 1)), TrailingMinusNumbers:=True
           
        ws.Range("C:D,F:F").Select
        Selection.NumberFormat = "0"

    End Sub

    Command Button OK

    Private Sub OK_Click()
    FChooseSource.Hide
    End Sub

    I use this form in a Module coding where, after running these form, it should continue with the coding from this form:

    Module Codding

    .........................

    On Error GoTo Mesaj_Eroare
      
       FChooseSource.Show
       
        ws.Activate
        Initial.Select
           
        Range("A1").Value = "AssetsNo."
        Range("B1").Value = " "
        Range("C1").Value = "Acct.det"
        Range("D1").Value = "BusA"
        Range("E1").Value = "Cost Ctr"
        Range("F1").Value = "Name"
        Range("G1").Value = "Reference doc."
        Range("H1").Value = "Description"
        Range("I1").Value = "Plannned Amount"
        Range("J1").Value = "Amount Posted"
        Range("K1").Value = "Amount TBP"
        Range("L1").Value = "Cumul.Amt"
        Range("M1").Value = "Crcy"
           
        Info.Select
        Info.EntireColumn.AutoFit

    .........................

    When I run the Macro, for the Option button "Preview" is continuing with the module coding, but if I choose the Option button "Final", it's running the code attached to this button, than it stops as it is complete, but it doesn't go forward on the module coding. Do you know why, or how can I correct these issue?

    Thanks in advance!

    Tuesday, July 23, 2019 7:05 AM

All replies

  • Move the .Show command to the very end of your procedure - when the userform is activated (or shown, rather than just loaded into memory), the code pauses until the userform relinquishes focus. That's why you can make your own custom inputboxes using userforms, or use userforms for other user communication.

    You could also move the rest of the code to the userform's initialize routine, but that is unnecessary.


    Tuesday, July 23, 2019 9:21 PM