Userform UPDATE record and date issue

Spaced69

New Member
Joined
Sep 15, 2019
Messages
5
Hi guys need help with vba code for userform
Cells formated to date dd/mm/yyyy and use the isdate etc on userform....and if not a valid date wont accept....but noticed entering 07/12/18 will go into cell as 12/07/18...frustrating
Also managed to get update button to update data with addition of using vbyesno...but when running the vbyesno box remains and cant do anything unless i totally close

Not sure if can attach file

But any help is welcomed
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
This forum doesn't provide a means of uploading files. You can post your code but it is always more efficient to
provide your sample workbook.

You can load the workbook to a cloud site like DROPBOX.com or similar then provide the download link here.

Re: the date display issue ... have you formatted the column where the date is written to for the date display you desire ?
 
Upvote 0
.
You could add code to your existing macros to force Cols D & F to display the date as Day / Month / Year.

However, I simply highlighted both columns and used the FORMAT command to accomplish it. Test it
by entering the date on the userform as Month / Day / Year and the data was displayed in the cols as you
desired.

???
 
Upvote 0
Im sure i set colums to date(dd/mm/yyyy...the issue seemed to be as i entered 07!12/18 into the userform txtpdate text box in the userform and when i used submit...it would enter into the sheet column as 12/07/2018
There that issue and the update button on the userform doesnt work at it should...when vbyes is clicked i expect the box to close...but it remains...i think it possible to a loop ive created
 
Upvote 0
Do you use day-month-year (like 'dd-mm-yyyy') format in the date regional setting?
I do and usually this approach works for me:

Code:
Sheets("Sheet1").Range("A3") = CDate(TextBox1)
 
Upvote 0
.
Here is the update to your UPDATE button :

Code:
Private Sub cmdupdate_Click()
Dim x As Long
Dim y As Long
Dim answer As Variant


x = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
For y = 3 To x


  answer = MsgBox("Yes / No", vbYesNo + vbQuestion, "Update Record ?")
    If answer = vbYes Then
        Sheets("sheet1").Cells(y, 1).Value = ComboBox1.Text
        Sheets("sheet1").Cells(y, 2).Value = combo.Text
        Sheets("sheet1").Cells(y, 3).Value = txtmodel.Text
        Sheets("sheet1").Cells(y, 4).Value = txtpdate.Text
        Sheets("sheet1").Cells(y, 5).Value = txtpprice.Text
        Sheets("sheet1").Cells(y, 6).Value = txtsdate.Text
        Sheets("sheet1").Cells(y, 7).Value = txtsprice.Text
        WTCForm.Hide
        Exit Sub
    End If
    
    If answer = vbNo Then
        WTCForm.Hide
        Exit Sub
    End If
Next
WTCForm.Hide
Exit Sub


End Sub
 
Upvote 0
Hi,
data coming from a textbox is text & you should use a type conversion function as suggested by @Akuini to coerce to required data type.

Glancing through your code not sure you really need to repeat code for submit / update process.

Not fully worked through & tested but make a backup of your workbook & then replace ALL your forms code with following

Code:
Private Sub cmdexit_Click()
Unload Me
End Sub


Function ControlsArr()
    ControlsArr = Array(txtreg, combo, txtmodel, txtpdate, txtpprice, txtsdate, txtsprice)
End Function

Private Sub cmdnew_Click()
    Dim Control As Variant
'clear controls
    For Each Control In ControlsArr
        Control.Text = ""
    Next
    Me.ComboBox1.Value = ""
     With Me.txtreg
        .SetFocus
        .Locked = False
     End With
'change status
     With Me.cmdsubmit
        .Caption = "SUBMIT"
        .BackColor = vbBlack
    End With
End Sub


Private Sub cmdsubmit_Click()
    Dim erow As Long, c As Long
    Dim m As Variant, Control As Variant
    Dim RegNo As String
    Dim NewRecord As Boolean
    
    RegNo = Me.txtreg.Text
    
    If Trim(Me.txtreg.Value) = "" Then
        Me.txtreg.SetFocus
        MsgBox "Please enter valid reg", 48, "Enter Reg No"
        Exit Sub
    End If
    
    m = Application.Match(RegNo, Sheet1.Columns(1), 0)
    NewRecord = CBool(IsError(m))
    
    If Not NewRecord Then If MsgBox(RegNo & Chr(10) & "Update Record?", 36, "Update Record") = vbNo Then Exit Sub
    
    With Sheet1
'get record row
        erow = IIf(NewRecord, .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row, CLng(m))
    End With
'add record to sheet
'note IsDate & IsNumeric functions do not always work as intended
    For Each Control In ControlsArr
        c = c + 1
        With Control
            If IsDate(.Text) Then
                Sheet1.Cells(erow, c).Value = DateValue(.Text)
            ElseIf IsNumeric(.Text) Then
                Sheet1.Cells(erow, c).Value = Val(.Text)
            Else
                Sheet1.Cells(erow, c).Value = .Text
            End If
        End With
    Next
    
'inform user
    msg = IIf(NewRecord, "New Record Entered", "Record Updated")
    
    MsgBox RegNo & Chr(10) & msg, 64, msg
'refresh combobox
    If NewRecord Then ComboBox1_Refresh
End Sub


Private Sub ComboBox1_Refresh()
    Dim LastRow As Long
    With Me.ComboBox1
       .Clear
    LastRow = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row
    .List = Sheet1.Cells(3, 1).Resize(LastRow - 2).Value2
    End With
End Sub


Private Sub ComboBox1_Change()
    Dim RegNo As String
    Dim m As Variant
    Dim c As Long
    
    RegNo = ComboBox1.Text
    If Len(RegNo) = 0 Then Exit Sub
    m = Application.Match(RegNo, Sheet1.Columns(1), 0)
    If Not IsError(m) Then
        For Each Control In ControlsArr
            c = c + 1
            Control.Text = Sheet1.Cells(CLng(m), c).Value
        Next
'change status
        With Me.cmdsubmit
            .Caption = "UPDATE"
            .BackColor = vbRed
        End With
'prevent editing
        Me.txtreg.Locked = True
    End If
    
End Sub


Private Sub txtpdate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(txtpdate.Text) > 0 And Not IsDate(txtpdate.Text) Then
        MsgBox "Date required", 48, "Date Required"
        Cancel = True
    End If
End Sub


Private Sub txtreg_AfterUpdate()
    Dim a As Long
    Dim RegNo As String
    
    RegNo = Me.txtreg.Text
    
    If Len(RegNo) = 0 Then Exit Sub
    a = Application.WorksheetFunction.CountIf(Sheet1.Range("A:A"), RegNo)
    If a >= 1 Then
        MsgBox RegNo & Chr(10) & "Registration already exists", 16, "Record Exists"
        Me.txtreg = ""
    End If
End Sub


Private Sub txtsdate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(txtsdate.Text) > 0 And Not IsDate(txtsdate.Text) Then
        MsgBox "Date required", 48, "Date Required"
        Cancel = True
    End If
End Sub


Private Sub UserForm_Initialize()
 ComboBox1_Refresh
End Sub

if all works ok - the Submit button performs both functions where its caption changes to Update when editing a record.

You can delete your existing update button

Hope Helpful

Dave
 
Last edited:
Upvote 0
Thnx to all those who replied...will try code and see how i get on
Is it possible to have the pdate and sdate to have enter numbers on on the userform so that it inserts the / or -
Id imagine an input mask of some sorts or predefined txtbox
 
Upvote 0
@dave
Love the coding...hope i can get my head around it...lol
Is there a way to make all txt entries into ucase
Especially the registration
Others would be nice aswell but not essential
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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