Debug this code.

Hi Fellow users,
I have a code with self-learning and bit of chat GPT.
The code and userform runs alright. Just a tiny bit of thing worries me. That No matter what I change or do, when I enter date in Start time and Stop time field, it always displays in mm-dd-yyyy format.
Now, before you shoot me with obvious reason, I will tell you all the things I have tried:-
- Checked my computers system Date setting.
- Checked excel's date default setting.
- Changed Formating of column to dd-mm-yyyy (custom formatting)
- Tried changing input to text instead of format now.

Can someone please help out?
Here's the code:

Code:

---

Private Sub cmddelete_Click()
Dim x As Long
Dim y As Long
x = Sheets("Worksheet").Range("A" & Rows.Count).End(xlUp).Row
For y = 8 To x
    If Sheets("Worksheet").Cells(y, 1).Value = txtsearch.Text Then
        Rows(y).Delete
    End If
Next y
'Clear Boxes
Me.txtsearch.Value = ""
Me.cmbwf.Value = ""
Me.txtwtg.Value = ""
Me.txtwindspeed.Value = ""
Me.txtalarmcode.Value = ""
Me.txtalarmdes.Value = ""
Me.txtstoptime.Value = ""
Me.txtstarttime.Value = ""
Me.cmballocation.Value = ""
Me.cmbattend.Value = ""
Me.ComboBox1.Value = ""
MsgBox "Data has been deleted", vbInformation
End Sub

Private Sub cmdexit_Click()
If MsgBox("Do you want to exit this form?", vbQuestion + vbYesNo, "Confirmation") = vbYes Then
    Unload Me
End If
End Sub

Private Sub cmdreset_Click()
Unload Me
UserForm1.Show
End Sub

Private Sub cmdsave_Click()
Dim sh As Worksheet
Dim lr As Long
Dim dataRange As Range
' Set worksheet and find the last row
Set sh = ThisWorkbook.Sheets("Worksheet")
lr = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
' Define the range containing the existing data
Set dataRange = sh.Range("A8:J" & lr)
' Insert a new row at the top of the table
 sh.Rows("8:8").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'VALIDATION
If Me.txtwtg.Value = "" Then
    MsgBox "Please enter the WTG No.", vbCritical
    Exit Sub
End If
If IsNumeric(Me.txtwindspeed) = False Then
    MsgBox "Please enter the wind speed", vbCritical
    Exit Sub
End If
If IsNumeric(Me.txtalarmcode) = False Then
    MsgBox "Please enter the alarm code", vbCritical
    Exit Sub
End If
'Add Data in Excel Sheet
With sh
    ' Copy format from the row below the inserted row
    dataRange.Rows(2).Copy
    .Rows(8).PasteSpecial Paste:=xlPasteFormats
    ' Copy values from the userform to the inserted row
    .Cells(8, "A").Value = Me.cmbwf.Value
    .Cells(8, "B").Value = Me.txtwtg.Value
    .Cells(8, "C").Value = Me.txtwindspeed.Value
    .Cells(8, "D").Value = Me.txtalarmcode.Value
    .Cells(8, "E").Value = Me.txtalarmdes.Value
    .Cells(8, "F").Value = Me.txtstoptime.Value
    .Cells(8, "G").Value = Me.txtstarttime.Value
    .Cells(8, "H").Value = Me.ComboBox1.Value
    .Cells(8, "I").Value = Me.cmballocation.Value
    .Cells(8, "J").Value = Me.cmbattend.Value
End With
'Clear Boxes
Me.cmbwf.Value = ""
Me.txtwtg.Value = ""
Me.txtwindspeed.Value = ""
Me.txtalarmcode.Value = ""
Me.txtalarmdes.Value = ""
Me.txtstoptime.Value = ""
Me.txtstarttime.Value = ""
Me.txtdowntime.Value = ""
Me.cmballocation.Value = ""
Me.cmbattend.Value = ""
Me.txtnotes.Value = ""
Me.ComboBox1.Value = ""
Call Refresh_data
MsgBox "Data has been added to the worksheet", vbInformation
Call Refresh_data
End Sub

Sub Refresh_data()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Worksheet")
Dim lr As Long
lr = Sheets("Worksheet").Range("A" & Rows.Count).End(xlUp).Row
If lr = 7 Then lr = 8
    With Me.ListBox
        .ColumnCount = 10
        .ColumnHeads = True
        .ColumnWidths = "90, 30,30,30,100,80,80,80,90,100"
        .RowSource = "Worksheet! A8:J" & lr
    End With
End Sub

Private Sub cmdsearch_Click()
Dim x As Long
Dim y As Long
x = Sheets("Worksheet").Range("A" & Rows.Count).End(xlUp).Row
For y = 8 To x
    If Sheets("Worksheet").Cells(y, 1).Value = txtsearch.Text Then
        cmbwf = Sheets("Worksheet").Cells(y, 1).Value
        txtwtg = Sheets("Worksheet").Cells(y, 2).Value
        txtwindspeed = Sheets("Worksheet").Cells(y, 3).Value
        txtalarmcode = Sheets("Worksheet").Cells(y, 4).Value
        txtstoptime = Sheets("Worksheet").Cells(y, 5).Value
        txtstarttime = Sheets("Worksheet").Cells(y, 6).Value
        ComboBox1 = Sheets("Worksheet").Cells(y, 7).Value
        cmballocation = Sheets("Worksheet").Cells(y, 8).Value
        cmbattend = Sheets("Worksheet").Cells(y, 9).Value
    End If
Next y
End Sub

Private Sub cmdupdate_Click()
Dim x As Long
Dim y As Long
x = Sheets("Worksheet").Range("A" & Rows.Count).End(xlUp).Row
For y = 8 To x
    If Sheets("Worksheet").Cells(y, 1).Value = txtsearch.Text Then
        Sheets("Worksheet").Cells(y, 1).Value = cmbwf
        Sheets("Worksheet").Cells(y, 2).Value = txtwtg
        Sheets("Worksheet").Cells(y, 3).Value = txtwindspeed
        Sheets("Worksheet").Cells(y, 4).Value = txtalarmcode
        Sheets("Worksheet").Cells(y, 5).Value = txtstoptime
        Sheets("Worksheet").Cells(y, 6).Value = txtstarttime
        Sheets("Worksheet").Cells(y, 7).Value = ComboBox1
        Sheets("Worksheet").Cells(y, 8).Value = cmballocation
        Sheets("Worksheet").Cells(y, 9).Value = cmbattend
    End If
Next y
Me.txtsearch.Value = ""
Me.cmbwf.Value = ""
Me.txtwtg.Value = ""
Me.txtwindspeed.Value = ""
Me.txtalarmcode.Value = ""
Me.txtalarmdes.Value = ""
Me.txtstoptime.Value = ""
Me.txtstarttime.Value = ""
Me.txtdowntime.Value = ""
Me.cmballocation.Value = ""
Me.cmbattend.Value = ""
Me.ComboBox1.Value = ""
MsgBox "Data has been updated in the worksheet", vbInformation
End Sub

Private Sub txtstarttime_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
' Check if the user pressed Ctrl + ;
If KeyCode = 186 And (Shift And 2) Then
    ' Input today's date and current time into the text box
    Me.txtstarttime.Value = Format(Now, "dd/mm/yyyy hh:mm")
End If
End Sub

Private Sub txtstoptime_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
' Check if the user pressed Ctrl + ;
If KeyCode = 186 And (Shift And 2) Then
    ' Input today's date and current time into the text box
    Me.txtstoptime.Value = Format(Now, "dd/mm/yyyy hh:mm")
End If
End Sub

Private Sub UserForm_Activate()
cmbwf.List = Array("North Brown Wind Farm", "The Bluff Wind Farm", "Hallet Wind Farm", "Hallet Hill Wind Farm", "Oaklands Hill Wind Farm", "Snowtown Wind Farm", "Clements Gap Wind Farm")
cmballocation.List = Array("Manufacturer", "Owner")
cmbattend.List = Array("Nandit", "Dhruv", "Pragnesh", "Janak", "Other")
ComboBox1.List = Array("Reset by Suzlon Monitoring Centre", "Reset by Site tech", "Reset by India Team", "Breakdown", "Repetitive Alarm-Technician has to attend", "")
Call Refresh_data
End Sub

Private Sub txtalarmcode_AfterUpdate()
Dim ws As Worksheet
Dim lookupValue As String
Dim resultValue As Variant
Dim lastRow As Long
Dim i As Long
' Set the reference to the "alarmcode" sheet
Set ws = ThisWorkbook.Sheets("alarmcode")
' Find the last row of data in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Get the value from txtalarmcode
lookupValue = Me.txtalarmcode.Value
' Loop through the rows of the "alarmcode" sheet to find the corresponding value
For i = 2 To lastRow
    If ws.Cells(i, 1).Value = lookupValue Then
        ' If a match is found, assign the corresponding values to txtalarmdes and txtnotes
        Me.txtalarmdes.Value = ws.Cells(i, 2).Value
        Me.txtnotes.Value = ws.Cells(i, 3).Value
        Exit Sub
    End If
Next i
' If no corresponding value is found, clear txtalarmdes and txtnotes
Me.txtalarmdes.Value = ""
Me.txtnotes.Value = ""
Call Refresh_data
End Sub

---

This is different from your initially stated desired format :

Quote:

---

- Changed Formating of column to dd-mm-yyyy (custom formatting)

---

Welcome to VBAX njadvani. I have taken the opportunity to wrap your submitted code with tags, and removed Code sections where there was no code provided between Private Sub & End Sub as they provided no function to your submitted code. I have also edited your title so that it reflects what I believe your intent was name in requesting assistance to debug your code. I trust you don't mind.

When you ask for assistance in debugging your code are you asking for assistance to understand your code or as in "debugging" a feature that already exists within the Developer platform?

By Debugging, I meant to seek help in desired output of code. Despite no compiling error, the code does not give the output that I want.
Thanks

Can you tell me where have I defined the column format?
For your reference, I have created a another simple button to take date time input from user. So, even if put's 03-04-2024 12:23, the output is given as 04-03-2023 12:23.
Tried changing regional formats, etc.
Tried doing Text to columns, and selecting mdy in third option as well.

Are you willing to post a copy of your workbook for review ? Give it just enough data so you code can be tested ?

Have you tried using the CDate() function?

When you output the date to the worksheet try wrapping the date value in the CDate function.

Code:

---

Range("A1").Value = CDate(your date value)

---

You may instead need to use CDate when you collect the date value from the user form.

Code:

---

DateVariable = CDate(Texbox1.Value)

---

I am on my phone at the moment so not able to test.

Quote:

---

Originally Posted by georgiboy

I am on my phone at the moment so not able to test.

---

.... Quick everyone ring Georgiboy. :devil2::devil2:

Quote:

---

Originally Posted by Logit

Are you willing to post a copy of your workbook for review ? Give it just enough data so you code can be tested ?

---

Yes Sure.
Here's code for Another simple button that takes user input and displays on selected cell.

Code:

---

Sub InputDateAndTime()
    Dim userInput As Variant
    Dim formattedDateTime As String
' Prompt the user to input a date and time
    userInput = InputBox("Enter the date and time (format: dd-mm-yyyy hh:mm):")
' Check if the user canceled the input box
    If userInput = "" Then Exit Sub
' Format the user input into "dd-mm-yyyy hh:mm" format
On Error Resume Next
    formattedDateTime = Format(CDate(userInput), "dd-mm-yyyy hh:mm")
    On Error GoTo 0
' Check if the formatted date and time is valid
    If formattedDateTime <> "" Then
    ' Convert the formatted date and time to a string
    Dim formattedString As String
    formattedString = CStr(formattedDateTime)
    ' Insert the formatted string into the active cell
    ActiveCell.Value = formattedString
    Else
    MsgBox "Invalid date and time format. Please enter the date and time in the format 'dd-mm-yyyy hh:mm'.", vbExclamation
    End If
End Sub

---

Attachment 31482Attachment 31482


Here s file with original code and just enough data.
When you press add case>input any random values from drop down lists and txt fields>input date and time in format dd/mm/yyyy hh:mm

and press save. See the corresponding values on cell. Everything else works fine, except for start time and stop time.




Any insights would be useful.
Thanks

I've tried to run your code in the posted workbook. However, each time I attempt to save I receive an error message indicating there are not sufficient resources available to perform the action.

I tried to clean up the workbook but even those steps generate an error message.

Is there another copy of your workbook that exists ? Maybe that version won't generate these error messages ?

There was too much extranious formatting (borders) that took clogged the ability to insert

I cleaned all that up and changed this

Code:

---

        dataRange.Rows(8).PasteSpecial Paste:=xlPasteFormats    '  <<<<<<<<<<<<

---

so that borders didnt get copied to the last column each time

I don't know about the start / end times but at least it runs.

With so little data but such a large file size, I always check to see where the formatting is

The ver1 file is a lot smaller, but you'll probably need to check that the formatting / shading / borders are what you want after the CODE line above

Hey,
Thanks for your help.
The Cdate function works. I've integrated it with my save() button.

Code:

---

Private Sub cmdsave_Click()
    Dim sh As Worksheet
    Dim lr As Long
    Dim dataRange As Range
    ' Set worksheet and find the last row
    Set sh = ThisWorkbook.Sheets("Worksheet")
    lr = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
    ' Define the range containing the existing data
    Set dataRange = sh.Range("A8:J" & lr)
    ' Insert a new row at the top of the table
    sh.Rows("8:8").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow
    'VALIDATION
    If Me.txtwtg.Value = "" Then
        MsgBox "Please enter the WTG No.", vbCritical
        Exit Sub
    End If
    If IsNumeric(Me.txtwindspeed) = False Then
        MsgBox "Please enter the wind speed", vbCritical
        Exit Sub
    End If
    If IsNumeric(Me.txtalarmcode) = False Then
        MsgBox "Please enter the alarm code", vbCritical
        Exit Sub
    End If
    'Add Data in Excel Sheet
    Dim userInput As Variant
    userInput = txtstoptime
    Dim userInput1 As Variant
  userInput1 = txtstarttime
    ' Convert the user input to a date using CDate function
    Dim dateVariable As Date
    dateVariable = CDate(Me.txtstoptime.Value)
    Me.txtstoptime.Value = dateVariable
    ' Check if txtstarttime is not empty
    If Me.txtstarttime.Value <> "" Then
        Dim dateVariable1 As Date
        dateVariable1 = CDate(Me.txtstarttime.Value)
        ' Update the value of txtstoptime with formatted date and time
        Me.txtstoptime.Value = dateVariable
        Me.txtstarttime.Value = dateVariable1
    End If
    With sh
        ' Copy format from the row below the inserted row
        dataRange.Rows(2).Copy
        .Rows(8).PasteSpecial Paste:=xlPasteFormats
        ' Copy values from the userform to the inserted row
        .Cells(8, "A").Value = Me.cmbwf.Value
        .Cells(8, "B").Value = Me.txtwtg.Value
        .Cells(8, "C").Value = Me.txtwindspeed.Value
        .Cells(8, "D").Value = Me.txtalarmcode.Value
        .Cells(8, "E").Value = Me.txtalarmdes.Value
        .Cells(8, "F").Value = dateVariable
        .Cells(8, "G").Value = Me.txtstarttime.Value
        .Cells(8, "H").Value = Me.ComboBox1.Value
        .Cells(8, "I").Value = Me.cmballocation.Value
        .Cells(8, "J").Value = Me.cmbattend.Value
    End With
    'Clear Boxes
    Me.cmbwf.Value = ""
    Me.txtwtg.Value = ""
    Me.txtwindspeed.Value = ""
    Me.txtalarmcode.Value = ""
    Me.txtalarmdes.Value = ""
    Me.txtstoptime.Value = ""
    Me.txtstarttime.Value = ""
    Me.txtdowntime.Value = ""
    Me.cmballocation.Value = ""
    Me.cmbattend.Value = ""
    Me.txtnotes.Value = ""
    Me.ComboBox1.Value = ""
    Call Refresh_data
    MsgBox "Data has been added to the worksheet", vbInformation
    Call Refresh_data
End Sub

---

This one works for me.!
Thanks for your input champions!

Glad you arrived at a solution. Please read the first line in my signature regarding use of code tags. Note the requirement to enclose the tag with square brackets.