Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Userform.Show gives runtime error on form close with new code?

  1. #1
    New Member
    Join Date
    Sep 2013
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Userform.Show gives runtime error on form close with new code?

    Hello,

    I just started using Userforms to help force data entry to be correct. I was having difficulty getting the form to appear in the middle of the excel window though, so I looked online and found the following code snipped to add to the initialize sub:

    Code:
        With barcode        .StartUpPosition = 0
            .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
            .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
            .Show
            End With
    So I added that code, clicked the button that runs the
    Code:
    Userform.Show
    command, the form popped up in the middle of the excel window and everything was great. However, I go and close that window, and I get "Run-time error '91': Object variable or With block variable not set" and its highlighting the
    Code:
    Userform.Show
    command as the problem line.

    Before I added the code to control the starting position it worked fine. Any ideas why it would error out there? Any particular code in this you need to see?

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,875
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Userform.Show gives runtime error on form close with new code?

    Use the Userform's name. Usually UserForm1 or UserForm2 etc. unless you changed it.

    UserForm1.Show
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    New Member
    Join Date
    Sep 2013
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform.Show gives runtime error on form close with new code?

    For clarity I changed the names to the names of the object types. The form is actually called "barcode" and the code actually reads "barcode.show".

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,251
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Userform.Show gives runtime error on form close with new code?

    Can you post all the code?
    If posting code please use code tags.

  5. #5
    New Member
    Join Date
    Sep 2013
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform.Show gives runtime error on form close with new code?

    The following is the code in the only "Module" in the book. There is a button on the actual excel pages that runs the "btn_newpart_click()" macro.

    Code:
    Public wb As Workbook, log As Worksheet, writerow As Range, prevser As Integer, newser As Integer, entry As Worksheet, djob As Range, dclock As Range, drev As Range
    Sub btn_newpart_click()
    
    
        barcode.Show
    
    
    End Sub
    Then I have a userform called "barcode" with the following code:

    Code:
    Private Sub btn_no_Click()
    
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        Application.DisplayAlerts = True
        Unload Me
        
    End Sub
    
    
    Private Sub btn_yes_Click()
        
        writerow(1, 1).Value = newser
        Unload Me
        leaktest.Show
        
    End Sub
    
    
    Private Sub UserForm_Initialize()
        
        Application.Calculate
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        
        Set wb = ThisWorkbook
        Set log = wb.Sheets("Data Log")
        Set writerow = log.Range("log_write_row")
        If Not (IsNumeric(writerow(1, 1).Offset(-1, 0).Value)) Then
            prevser = 0
        Else
            prevser = writerow(1, 1).Offset(-1, 0).Value
            End If
        newser = prevser + 1
        BarcodeLabel.Caption = "*SN " & Right("00000" & newser, 5) & "*"
        
        With barcode
            .StartUpPosition = 0
            .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
            .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
            .Show
            End With
    
    
    End Sub
    Finally, there is a UserForm called "leaktest" which has the following code:

    Code:
    Private Sub ContinueButton_Click()
    
    
        If check_forms Then
            Call updatedeg
            Call enter_data
            Unload Me
        Else
            MsgBox Prompt:="Data is not formatted correctly.  Data not entered yet."
            End If
    
    
    End Sub
    
    
    Private Sub psiend_Change()
        Call updatedeg
    End Sub
    
    
    Private Sub psistart_Change()
        Call updatedeg
    End Sub
    
    
    Private Sub UserForm_Initialize()
        
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        
        Set wb = ThisWorkbook
        Set entry = wb.Sheets("Data Entry")
        Set log = wb.Sheets("Data Log")
        'Set writerow = log.Range("log_write_row")
        Set djob = entry.Range("B7")
        Set dclock = entry.Range("B8")
        Set drev = entry.Range("B9")
        
        jobno.Value = djob.Value
        clockno.Value = dclock.Value
        partrev.Value = drev.Value
    
    
    End Sub
    
    
    
    
    
    
    Private Sub updatedeg()
    
    
        If IsNumeric(psistart.Value) And IsNumeric(psiend.Value) Then
            degradation.Caption = Round(psistart.Value - psiend.Value, 3)
        Else
        
            End If
    
    
    End Sub
    
    
    Private Function check_forms() As Boolean
    
    
        Dim result As Boolean
        result = True
        If Not IsNumeric(jobno.Value) Then result = False
        If Not IsNumeric(clockno.Value) Then result = False
        If Not IsNumeric(psistart.Value) Then result = False
        If Not IsNumeric(psiend.Value) Then result = False
        If Not IsNumeric(degradation.Caption) Then result = False
        If Not IsNumeric(notests.Value) Then result = False
        If Not IsNumeric(noleaks.Value) Then result = False
        
        check_forms = result
    
    
    End Function
    
    
    Private Sub enter_data()
    
    
        writerow(1, 2).Value = Now()
        writerow(1, 3).Value = jobno.Value
        writerow(1, 4).Value = clockno.Value
        writerow(1, 5).Value = partrev.Value
        writerow(1, 6).Value = psistart.Value
        writerow(1, 7).Value = psiend.Value
        writerow(1, 8).Value = degradation.Caption
        writerow(1, 9).Value = notests.Value
        writerow(1, 10).Value = noleaks.Value
    
    
    End Sub
    
    
    Private Sub UserForm_Terminate()
    
    
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        Application.DisplayAlerts = True
    
    
    End Sub
    The bold code above in the barcode module was the most recent thing I added. Prior to adding that, everything worked great, aside from the windows starting in a non-desirable location on the screen. After adding that, it gives me an error. I don't know enough to know why its freaking out.

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,251
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Userform.Show gives runtime error on form close with new code?

    Have you tried stepping through the Initialize sub of the barcode form?
    If posting code please use code tags.

  7. #7
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,875
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Userform.Show gives runtime error on form close with new code?

    I can replicate your problem.

    Remove the .Show command from the UserForm_Initialize procedure.

    Code:
        With barcode
            .StartUpPosition = 0
            .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
            .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
            .Show 'Delete this
        End With
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  8. #8
    New Member
    Join Date
    Sep 2013
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform.Show gives runtime error on form close with new code?

    Ahhh! Thanks AlphaFrog. That solved it!

  9. #9
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,875
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Userform.Show gives runtime error on form close with new code?

    You're welcome.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

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

    Thumbs up Re: Userform.Show gives runtime error on form close with new code?

    Exactly what was required from me to, I had copied very similar code to that above to correct an issue in Excel 2010 on Frame opening hidden on dual monitors, the Form was opening just visible on the Right hand scroll bar. Same 91 error when closing the form.
    Many thanks.

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
  •