Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: ??? Userform and Macro code should work but it doesnt

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello,

    Ik have a relative long Macro that needs to be executed when a userform is displayed.

    First i put the macro code outside the userform, the macro therefore was only executed when the userform was closed.

    After this i put the exact same code in the userform. But now all i get to see is a white userform and excel crashing on it

    any one knows how you should do these two tasks simultaniously?

    my code:

    Sub mysub1()

    Consolform.Show
    ButtonInfra_Click
    Consolform.ConsolLabel2.Caption = "l"
    ButtonProducts_Click
    Consolform.ConsolLabel2.Caption = "ll"
    ButtonProcesses_Click
    Consolform.ConsolLabel2.Caption = "lll"
    ButtonEA_Click
    Consolform.ConsolLabel2.Caption = "llll"
    ButtonCIB_Click
    Consolform.ConsolLabel2.Caption = "llll"
    ButtonRM_Click
    Consolform.ConsolLabel2.Caption = "lllll"

    End Sub

    This didn't work at all, next i put the code inside the userform this caused excel to crash.

    Thanks for helping out.


  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    how do i set a msgbox to read as a list..eg
    a
    b
    c
    d
    e

    instead of a b c d e

    is it vbleft or something?

    i keep going blank

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    oops. soory about that FRISCO

  4. #4
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    Hi friso. You need to divide the code as following.
    Please try.


    '--- into a standard module--from here-
    Sub mysub1()
    Consolform.Show
    End Sub
    '--- to here ---------------------------

    '--- into a Consolform module--from here-
    Private Sub ButtonInfra_Click()
    Call PvtMysub("l")
    End Sub

    Private Sub ButtonProducts_Click()
    Call PvtMysub("ll")
    End Sub

    Private Sub ButtonProcesses_Click()
    Call PvtMysub("lll")
    End Sub

    Private Sub ButtonEA_Click()
    Call PvtMysub("llll")
    End Sub

    Private Sub ButtonCIB_Click()
    Call PvtMysub("llll")
    End Sub

    Private Sub ButtonRM_Click()
    Call PvtMysub("lllll")
    End Sub

    Private Sub PvtMysub(strCaption As String)
    ConsolLabel2.Caption = strCaption
    End Sub
    '--- to here ---------------------------

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks but it doesn't work at all

    anyone else??


  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    So what you're saying is, that as soon as the userform is diplayed, you want the macro to run, right? If this is so, what's the name of your macro?

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hi this is the entire code for the macro:


    Sub ShowIt_Now()
    Consolform2.Show
    End Sub

    Sub ShowIt_Now2()
    Consolform2.Hide
    End Sub




    Sub ButtonKolomConsol_Click()
    ShowIt_Now
    ButtonInfra_Click
    Consolform2.Label2.Caption = "l"
    ButtonProducts_Click
    Consolform2.Label2.Caption = "ll"
    ButtonProcesses_Click
    Consolform2.Label2.Caption = "lll"
    ButtonEA_Click
    Consolform2.Label2.Caption = "llll"
    ButtonCIB_Click
    Consolform2.Label2.Caption = "llll"
    ButtonRM_Click
    Consolform2.Label2.Caption = "lllll"
    Worksheets("Consol-Kolom").Select
    Range("A3:BB602").Select
    Selection.Clear

    Worksheets("DB-Kolom").Select
    Range("A1:BD595").Select
    Application.CutCopyMode = False
    Selection.Copy
    Worksheets("Consol-Kolom").Select
    Range("A3:BD597").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False


    Dim rRange As Range
    With Sheets("Consol-Kolom")

    Set rRange = .Range("A3", .Range("A65536").End(xlUp)).Offset(0, 70)

    rRange.FormulaR1C1 = "=IF(RC[-68]=RC[-69],1,"""")"

    Set rRange = rRange.SpecialCells(xlCellTypeFormulas, _
    xlNumbers)
    rRange.EntireRow.Delete
    End With

    ShowIt_Now2
    Worksheets("Consol-Kolom").Select
    Range("A1").Select
    End Sub



Some videos you may like

User Tag List

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
  •