TheShyButterfly
New Member
- Joined
- Nov 6, 2011
- Messages
- 43
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:
The following is the code from the Class Module window:
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
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:
Code:
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:
Code:
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