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

mwisnefsky

New Member
Joined
Sep 3, 2013
Messages
14
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?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Use the Userform's name. Usually UserForm1 or UserForm2 etc. unless you changed it.

UserForm1.Show
 
Upvote 0
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".
 
Upvote 0
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) & "*"
    
[FONT=arial black][B]    With barcode[/B]
[B]        .StartUpPosition = 0[/B]
[B]        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)[/B]
[B]        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)[/B]
[B]        .Show[/B]
[B]        End With[/B][/FONT]


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.
 
Upvote 0
Have you tried stepping through the Initialize sub of the barcode form?
 
Upvote 0
I can replicate your problem.

Remove the .Show command from the UserForm_Initialize procedure.

Code:
    [COLOR=darkblue]With[/COLOR] barcode
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
        [B].Show[/B] [COLOR=green]'Delete this[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top