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
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
2,936
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 ?
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
2,936
.
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.

???
 

Spaced69

New Member
Joined
Sep 15, 2019
Messages
5
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
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,443
Office Version
365
Platform
Windows
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)
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
2,936
.
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
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,322
Office Version
2013
Platform
Windows
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:

Spaced69

New Member
Joined
Sep 15, 2019
Messages
5
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
 

Spaced69

New Member
Joined
Sep 15, 2019
Messages
5
@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
 

Forum statistics

Threads
1,084,749
Messages
5,379,620
Members
401,616
Latest member
YoSquidly

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top