tiredofit
Well-known Member
- Joined
- Apr 11, 2013
- Messages
- 1,834
- Office Version
- 365
- 2019
- Platform
- Windows
My workbook contains a userform and data on Sheet1 as shown:
The code is as follows:
This is in Userform1:
The above works so but is in a procedural fashion.
If it were to be done in an oo way, then we'll have:
This is in Userform1:
This is in cCustSurvey:
This is in cExcelUtils:
It seems to be a lot of extra work to be using classes.
Have I missed a point?
Thanks
The code is as follows:
This is in Userform1:
Code:
Option Explicit
Dim LastRow As Integer
Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub cmdNew_Click()
Dim iAnswer As Integer
If Me.txtState <> vbNullString Or Me.txtPhone <> vbNullString Then
iAnswer = MsgBox("There is unsaved data. Do you want to continue?", vbYesNo, "Unsaved Data")
If iAnswer = vbYes Then
Call ClearForm
End If
Else
Call ClearForm
End If
End Sub
Private Sub cmdSave_Click()
If Me.txtState = vbNullString Or Me.txtPhone = vbNullString Then
MsgBox "Can't save"
Else
Call SaveData
Call ClearForm
End If
End Sub
Private Sub SaveData()
Dim EmptyRow As Integer
EmptyRow = LastRow + 1
With Sheet1
.Cells(EmptyRow, 1).Value = Me.lblID.Caption
.Cells(EmptyRow, 2).Value = Me.txtState.Value
.Cells(EmptyRow, 3).Value = Me.txtPhone.Value
.Cells(EmptyRow, 4).Value = Me.chkHeard.Value
.Cells(EmptyRow, 5).Value = Me.chkInterested.Value
.Cells(EmptyRow, 6).Value = Me.chkFollowup.Value
End With
Call UserForm_Initialize
End Sub
Private Sub ClearForm()
With Me
.txtPhone.Value = vbNullString
.txtState.Value = vbNullString
.chkHeard.Value = False
.chkInterested.Value = False
.chkFollowup.Value = False
End With
End Sub
Private Sub UserForm_Initialize()
Me.lblID.Caption = Sheet1.Cells(LRowInCol(wks:=Sheet1, Col:=1), 1) + 1
LastRow = LRowInCol(wks:=Sheet1, Col:=1)
End Sub
Public Function LRowInCol(ByRef wks As Variant, _
ByRef Col As Variant) As Long
On Error GoTo Correction
If TypeName(wks) = "String" Then Set wks = Worksheets(wks)
LRowInCol = wks.Columns(Col).Find(What:="*", _
LookIn:=xlFormulas, _
SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, _
SearchFormat:=False).Row
Exitpoint:
On Error GoTo 0
Exit Function
Correction:
LRowInCol = 1
Resume Exitpoint
End Function
The above works so but is in a procedural fashion.
If it were to be done in an oo way, then we'll have:
This is in Userform1:
Code:
Option Explicit
Private m_oCustSurvey As cCustSurvey
Private m_blnSaved As Boolean
'
Private Sub cmdCancel_Click()
ClearForm
Unload UserForm1
End Sub
Private Sub cmdNew_Click()
'sets form up for a new record
Dim iAnswer As Integer
'check that current record is saved (if any)
If Not m_blnSaved Then 'see if any text data is entered that is not saved
If (Len(Me.txtPhone.Value & "") + Len(Me.txtState.Value & "")) <> 0 Then
iAnswer = MsgBox("There is unsaved data. Do you want to continue?", vbYesNo, "Unsaved Data")
If iAnswer = vbYes Then
ClearForm
End If
Else
ClearForm
End If
End If
End Sub
Private Sub cmdSave_Click()
With m_oCustSurvey
.State = txtState.Text
.PhoneNumber = txtPhone.Text
.HeardOfProduct = chkHeard.Value
.WantsProduct = chkInterested.Value
.Followup = chkFollowup.Value
End With
If Not m_oCustSurvey.ValidateData Then
MsgBox "State and Phone Number required", vbOKOnly, "Cannot Save"
Exit Sub
Else
m_blnSaved = m_oCustSurvey.Save
End If
DoAfterSave m_blnSaved
End Sub
Private Sub DoAfterSave(success As Boolean)
If success Then
ClearForm
lblID.Caption = m_oCustSurvey.GetNextID
MsgBox "Record Saved"
Else
MsgBox "Could not save record"
End If
m_blnSaved = False 'resetting flag
End Sub
Private Sub ClearForm()
Me.txtPhone.Value = ""
Me.txtState.Value = ""
Me.chkHeard.Value = False
Me.chkInterested.Value = False
Me.chkFollowup.Value = False
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'prevent closing by X button or keystrokes
If CloseMode = vbFormControlMenu Then Cancel = True
End Sub
Private Sub UserForm_Initialize()
Set m_oCustSurvey = New cCustSurvey
Set m_oCustSurvey.DBWorkSheet = Sheets("Sheet1")
m_oCustSurvey.GetNextID
lblID.Caption = m_oCustSurvey.ID
m_blnSaved = False
ClearForm
End Sub
Private Sub UserForm_Terminate()
Set m_oCustSurvey = Nothing
End Sub
This is in cCustSurvey:
Code:
Option Explicit
Private m_lngID As Long
Private m_strState As String
Private m_strPhone As String
Private m_blnHeardOfProduct As Boolean
Private m_blnWantsProduct As Boolean
Private m_blnFollowup As Boolean
Private m_xlWksht As Worksheet
Private m_oXL As cExcelUtils
'
Property Get ID() As Long
ID = m_lngID
End Property
Property Get State() As String
State = m_strState
End Property
Property Let State(newState As String)
m_strState = newState
End Property
Property Get PhoneNumber() As String
PhoneNumber = m_strPhone
End Property
Property Let PhoneNumber(newPhoneNumber As String)
m_strPhone = newPhoneNumber
End Property
Property Get HeardOfProduct() As Boolean
HeardOfProduct = m_blnHeardOfProduct
End Property
Property Let HeardOfProduct(newHeardOf As Boolean)
m_blnHeardOfProduct = newHeardOf
End Property
Property Get WantsProduct() As Boolean
WantsProduct = m_blnWantsProduct
End Property
Property Let WantsProduct(newWants As Boolean)
m_blnWantsProduct = newWants
End Property
Property Get Followup() As Boolean
Followup = m_blnFollowup
End Property
Property Let Followup(newFollowup As Boolean)
m_blnFollowup = newFollowup
End Property
Property Get DBWorkSheet() As Worksheet
Set DBWorkSheet = m_xlWksht
End Property
Property Set DBWorkSheet(newSheet As Worksheet)
Set m_xlWksht = newSheet
End Property
Public Function Save() As Boolean
Dim lngNewRowNum As Long
Dim blnReturn As Boolean
blnReturn = False
If m_xlWksht Is Nothing Then 'double check that we still have a valid object
blnReturn = False
GoTo Exit_Function
End If
lngNewRowNum = m_oXL.FindEmptyRow(m_xlWksht)
With m_xlWksht
.Cells(lngNewRowNum, 1).Value = Me.ID
.Cells(lngNewRowNum, 2).Value = Me.State
.Cells(lngNewRowNum, 3).Value = Me.PhoneNumber
.Cells(lngNewRowNum, 4).Value = Me.HeardOfProduct
.Cells(lngNewRowNum, 5).Value = Me.WantsProduct
.Cells(lngNewRowNum, 6).Value = Me.Followup
End With
If Err.Number = 0 Then 'no error
blnReturn = True
End If
Exit_Function:
Save = blnReturn
Exit Function
End Function
Public Function ValidateData() As Boolean
Dim blnReturn As Boolean
If (Len(Me.PhoneNumber & "") * Len(Me.State & "")) = 0 Then
blnReturn = False
Else
blnReturn = True
End If
ValidateData = blnReturn
End Function
Public Function GetNextID() As Long
Dim lngReturn As Long
lngReturn = m_xlWksht.Cells(Rows.Count, 1).End(xlUp).Value + 1
m_lngID = lngReturn ' set the ID property
GetNextID = lngReturn
End Function
Private Sub Class_Initialize()
Set m_oXL = New cExcelUtils
End Sub
Private Sub Class_Terminate()
Set m_oXL = Nothing
End Sub
This is in cExcelUtils:
Code:
Function FindEmptyRow(ws As Worksheet) As Long
Dim lngReturn As Long
lngReturn = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
FindEmptyRow = lngReturn
End Function
It seems to be a lot of extra work to be using classes.
Have I missed a point?
Thanks