Debug this code.

[njadvani]

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:

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

[Logit]

This is different from your initially stated desired format :

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

[Aussiebear]

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?

[njadvani]

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

[njadvani]

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.

[Logit]

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

[georgiboy]

Have you tried using the CDate() function?

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

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

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

DateVariable = CDate(Texbox1.Value)

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

[Aussiebear]

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

.... Quick everyone ring Georgiboy.

[njadvani]

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.

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

SMC Case Remote SMC Original.zipSMC Case Remote SMC Original.zip


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

[Logit]

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 ?

[Paul_Hossler]

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

I cleaned all that up and changed this

        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

[njadvani]

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

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!

[Aussiebear]

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.