locked
Excel 2010 - Userform not Showing or Initializing RRS feed

  • Question

  • I am showing/loading the UserForm from a module. Below is the initialize and module code. Run-time error '91' results.

    When I change UserForm_Initialize() to PolicyDOLForm_Initialize() the form shows but does not initialize. I am a novice and I am sure the answer is obvious however, I have searched and cannot find a solution.

    Public Sub UserForm_Initialize()
    Dim DOLText As String
    Dim PolicyDOLForm As UserForm

    Call SystemButtonSettings(Me, False) 'This Hides the UserForm Cancel button code is in "Hide_UserFrmCancel" module

    Me.StartUpPosition = 0
    Me.Top = Application.Top + (0.5 * Application.Height) - (0.8 * Me.Height)
    Me.Left = Application.Left + (0.5 * Application.Width) - (0.5 * Me.Width)

    DOLText = ActiveSheet.Range("B10").Text
    CheckBox1.value = False
    CheckBox1.Caption = DOLText

    End Sub

    Below is the module code.

               

    Option Explicit
    Public Sub PolicyDOLChk(ByVal Target As Range)
    Dim wks As Worksheet
    Dim PolicyDOLForm As UserForm
    Dim CellValue As String
    Dim CellValue2 As String
    Dim rng As Range

    Set wks = ActiveSheet
    Set rng = Range("B10,B18,B19")

        If Not Intersect(rng, Range(Target.Address)) Is Nothing _
            And IsEmpty(Range("B6")) = True _
            And IsEmpty(Range("B10")) = False _
            And IsEmpty(Range("B18")) = False _
            And IsEmpty(Range("B19")) = False _
        Then
            If Range("B10").value < Range("B18").value _
            And IsEmpty(Range("B10")) = False _
            And IsEmpty(Range("B18")) = False _
            Then
                CellValue = Range("B10").Text
                CellValue2 = Range("B18").Text
                Range("B10").Select
                Application.ScreenUpdating = True
                ColorHighlight.ColorHighlight
              PolicyDOLForm.show
                'MsgBox "DOL - " & CellValue & " cannot be EARLIER than Policy Inception - " & CellValue2 & ". Enter a new DATE.", , "DATE OF LOSS - Earlier than Policy Inception"
                Range("B10").ClearContents
                ColorReset.ResetColour
            ElseIf Range("B10").value > Range("B19").value _
            And IsEmpty(Range("B10")) = False _
            And IsEmpty(Range("B19")) = False _
            Then
                Range("B10").Select
                CellValue = Range("B10").Text
                CellValue2 = Range("B19").Text
                Application.ScreenUpdating = True
                ColorHighlight.ColorHighlight
                MsgBox "DOL - " & CellValue & " cannot be LATER than Policy Expiry - " & CellValue2 & ". Enter a new DATE.", , "DATE OF LOSS - Later than Policy Expiry"
                Range("B10").ClearContents
                ColorReset.ResetColour
            End If
        End If

    End Sub


    

    Wednesday, April 29, 2020 3:01 PM

All replies

  • It's a week since you posted and unfortunately for you no one has replied. However, I'll try to assist.

    You need to break it down to try to identify where the error is occurring.

    Comment out the code line "Call SystemButtonSettings(Me, False)" and test and see if the code will run without returning an error. If the code runs, then we need a copy of the code in that sub.

    The error indicates that you are possibly trying to assign the wrong type of data to a variable.

    Also, does the code stop on a particular line of code? If so, what line of code is displaying he error?


    Regards, OssieMac

    Tuesday, May 5, 2020 10:55 AM