Excel 2010 - Compile Error in Userform – Resizing form

[TheShyButterfly]

Hi there,

Thank you for taking the time to check this out.
I found the code for resizing the Userform to the user’s customization by dragging the corner with the mouse, and it worked perfectly when I ran my test form that I created. However….

When I pasted the codes into their appropriate code windows in my real userform that I am developing, it returns a ‘Compile Error’ “Ambiguous name detected: Userform_Initialize".

When I checked my code, I already had Option Explicit in the beginning of the Code Window, and already had the Userform_Initialize in another Sub routine above this code.

I logically thought that if I removed the Option Explicit and the sub routine of Userform_Initialize in this resize code, that it would resolve the problem (no use to have another initialization code when one already exists). I was wrong!

I removed Private Sub Userform_Initialise() and the End Sub, moving the code that was between it in to the last row of my previous sub routine that had already had the Userform_Initialize.

But when I ran the form again, it resulted in another Compile Error ‘"Only comments may appear after End Sub,End Function, or end Property". So I checked the code at the ‘break point’ and it didn’t like the ‘Set’ in the Set m_clsResizer = New CResizer.

I just don’t seem to be able to get my head around this … I don’t use VBA often and am only in the early stages of getting the hang of it (sort of).

I’ll provide the code that I current have, perhaps the more experienced person maybe able to work it out … so that it will run smoothly with my real form.

I’d be most grateful if it was possible to include the resizing the form by using either the bottom corner of the form or the top part of the form, depending on the users preference.

This is the code from my Form Code window:

Option Explicit

Dim currentrow As Long


'Private Sub cmdbut_CancelForm_Click()
'THIS IS REALLY WORKING - 13th MARCH 2015
'Cancels out of the form without saving data -THIS IS READY TO BE ACTIVATED _
BY REMOVING THE SINGLE QUOTATION MARKS
'Unload Me
'End Sub


'Private Sub cmdbut_CancelForm_Click()
'This closes the form without saving
'Unload Me

'End Sub



Private Sub cmdbut_SaveCloseBook_Click()
'This button will transfer the data to the spreadsheet in _
   the next available row
   
   
  'Range("G2").Value = txt_Inc_DATE
  
   erow = ws_Incident_Details.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    'This copies over the information from the form to the worksheet "Incident Details"
    'directing it to the next empty row, then COLUMN number where you want the data stored
    
        Cells(erow, 1) = txtSEC_INC_No.Value 'Column A
        Cells(erow, 2) = txt_Date_Recorded.Value 'Column B
        Cells(erow, 3) = txt_Time_Recorded.Value 'Column C
        Cells(erow, 4) = txt_INC_Recorded_By.Text 'Column D
        Cells(erow, 5) = Value = Environ("UserName") 'Placeholder for auto UserID to populate  'Column E
        Cells(erow, 6) = txt_Inc_DATE.Value 'Column F
        Cells(erow, 7) = txt_INC_Time.Value 'Column G
        Cells(erow, 8) = txt_Inc_Day.Value 'Column H in day format
        
        Cells(erow, 9) = txt_SAC_No.Value 'Column I
        Cells(erow, 10) = txt_Site_Address.Text 'Column J
        Cells(erow, 11) = txt_Event_Location.Text 'Column K
        Cells(erow, 12) = txt_Brief_Description.Text 'Column L
        Cells(erow, 13) = txt_Detailed_DESCRIP.Text 'Column M
        Cells(erow, 14) = txt_Action_Taken.Text 'Column N
        Cells(erow, 15) = Cmbox_IncCategory.Text 'Column O
        Cells(erow, 16) = Cmbox_ServiceProvider.Text 'Column P
        Cells(erow, 17) = Cmbox_JobPriority.Value 'Column Q
        
        Cells(erow, 18) = txt_Logged_With_Operator.Text 'Column R
        Cells(erow, 19) = txt_Date_ServiceJobLogged.Value 'Column S
        Cells(erow, 20) = txt_Time_ServiceJobLogged.Value 'Column T
        Cells(erow, 21) = txt_Job_No.Value 'Column U
        Cells(erow, 22) = Cmbox_Notification_Method.Text 'Column V
         
   Unload Me
   
End Sub

'Private Sub GetAddress() - THIS WASN'T WORKING SO I HAD TO SKIP IT
'   Dim SacNum As Variant
'   Dim Address As String
'
'   SacNum = txt_SAC_No
'   Sheets("ws_AlarmResponseList").Activate
'   Address = WorksheetFunction.VLookup(SacNum, Range("AlarmResponseList"), 2, 0)
'
'End Sub

Private Sub UserForm_Initialize()

'This works - adds date and time when form is opened.
        Me.txt_Date_Recorded = Date
        Me.txt_Date_Recorded.Value = Format(Date, "dd/mm/yyyy")
       ' Me.txt_Inc_Day.Value = txt_Inc_Day = Format(Date, "ddd") ' I want this field to show the DAY _
        that was entered into the Incident Date field/textbox - I THINK THIS SHOULD GO INTO A SUB CLICK
        Me.txt_Time_Recorded.Value = Format(Time, "HH:mm")
        
'Now I am trying to get the Auto increment txtbox to _
 work so that the prefix "Inc" goes before the auto number_
 'at the moment it increments, but not like Inc20150001
    
    Me.txtSEC_INC_No.Enabled = True
    Dim irow As Long
    Dim ws As ws_Incident_Details
    Set ws = ws_Incident_Details
     'find last data row from database'
    irow = ws.Cells(Rows.Count, 1) _

The following is the code from the Class Module window:

Option Explicit

Private Const MFrameResizer = "FrameResizeGrab"
Private Const MResizer = "ResizeGrab"
Private WithEvents m_objResizer As MSForms.Frame
Private m_sngLeftResizePos As Single
Private m_sngTopResizePos As Single
Private m_blnResizing As Single
Private WithEvents m_frmParent As MSForms.UserForm
Private m_objParent As Object

Private Sub Class_Terminate()

    m_objParent.Controls.Remove MResizer
    
End Sub


Private Sub m_frmParent_Layout()
    
    If Not m_blnResizing Then
        With m_objResizer
            .Top = m_objParent.InsideHeight - .Height
            .Left = m_objParent.InsideWidth - .Width
        End With
   End If

End Sub


Private Sub m_objResizer_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

    If Button = 1 Then
        With m_objResizer
            .Move .Left + X - m_sngLeftResizePos, .Top + Y - m_sngTopResizePos
            m_objParent.Width = m_objParent.Width + X - m_sngLeftResizePos
            m_objParent.Height = m_objParent.Height + Y - m_sngTopResizePos
            .Left = m_objParent.InsideWidth - .Width
            .Top = m_objParent.InsideHeight - .Height
        End With
    End If
    
End Sub


Private Sub m_objResizer_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Button = 1 Then
        m_blnResizing = False
    End If
End Sub

Public Function Add(Parent As Object) As MSForms.Frame
'
' add resizing control to bottom righthand corner of userform
'
    Dim labTemp As MSForms.Label
    
    Set m_frmParent = Parent
    Set m_objParent = Parent
    
    Set m_objResizer = m_objParent.Controls.Add("Forms.Frame.1", MFrameResizer, True)
    Set labTemp = m_objResizer.Add("Forms.label.1", MResizer, True)
    With labTemp
        With .Font
            .Name = "Marlett"
            .Charset = 2
            .Size = 14
            .Bold = True
        End With
        .BackStyle = fmBackStyleTransparent
        .AutoSize = True
        .BorderStyle = fmBorderStyleNone
        .Caption = "o"
        .MousePointer = fmMousePointerSizeNWSE
        .ForeColor = RGB(100, 100, 100)
        .ZOrder
        .Top = 1
        .Left = 1
        .Enabled = False
    End With
    
    With m_objResizer
        .MousePointer = fmMousePointerSizeNWSE
        .BorderStyle = fmBorderStyleNone
        .SpecialEffect = fmSpecialEffectFlat
        .ZOrder
        .Caption = ""
        .Width = labTemp.Width + 1
        .Height = labTemp.Height + 1
        .Top = m_objParent.InsideHeight - .Height
        .Left = m_objParent.InsideWidth - .Width
    End With
End Function

'- See more at: http://www.andypope.info/vba/resizeform.htm#sthash.Ll0Q5Usl.dpuf

In case you want to check the code out in its original form, this is the webpage from where I got the code: Resize userform

Thanking you in advance for your help, if you need any further clarification, please let me know (I know I go on, and on, and on, but I want to provide all information that will hopefully assist with resolving the issue.

Cheers,
TheShyButterfly

 

[RickXL]

Hi,

I cannot do much testing because some bits are missing - like the end of the UserForm_Initialize macro.

Also, I cannot work out what:

    Dim ws As ws_Incident_Details
    Set ws = ws_Incident_Details

is supposed to do.

However, a couple of thing that may help:

Option Explicit is a way of making sure that you do not use a variable without saying what it is supposed to be in a Dim statement. It does not check if you have used something twice. It is good practice to have one.

The Class requires some code in your UserForm_Initialize macro and you want some in there as well. The solution is to have one UserForm_Initialize macro and add the lines of code from the website first and put yours afterwards.

If you want me to test the code then I will need to have it all. Also, the bit about ws_Incident_Details needs some explaining. I presume it refers to a worksheet but then I don't know if ws is different from ws_Incident_Details.

I can probably work out what buttons and text boxes will be required on the form.

 

[TheShyButterfly]

Hi RickXL,

Thank you for taking an interest in my post.
I am trying to reduce the size of my workbook so I can upload it.
My brain is just mush at the moment, because I have been at this project 18 hrs per day for the last 2 weeks ... so I'll need a day to just have a break and then I'll get it up there for you ....

The 'ws_Incident_Details is the worksheet code name of where the data is transferred to when the save button is clicked. From reading (so much) it appears that the best way to ensure written procedures are not affected if someone changes the name of the worksheet, it was strongly suggested to refer to the spreadsheet by it's code name, rather than just the sheet name.

I hope to have the workbook uploaded within the next day or so.

Thank you again for your time and I look forward to reviewing your comments/advice once I have uploaded the file.

Regards,
TheShyButterfly

 

[RickXL]

I asked about ws_Incident_Details because I can't make sense of the code, If your worksheet name is Incident_Details then I would expect to see something like this:

    Dim ws As Worksheet     
    Set ws = Worksheets("Incident_Details")