Loading sheet row data to a userform issue.

[Pacman52]

Hi all I've tried in vain how to work out how I fix an issue I have when loading a userform that pulls the cell data from a specific row on a worksheet so a user can amend or add anything they need to. For ease of explanation I'll call this userform 'EditData'

The original row cell data is written to the worksheet from another userform with various textboxes all of which work perfectly and when a user saves this userform the data is written to the worksheet ok with no problems.

Public Sub LoadData()

    With Sheet1.Range("A3").Offset(m_currentRow)

        txbDepositDatePaid = .Cells(1, 10).Value
        txbDepositDatePaid = Format(CDate(txbDepositDatePaid), "DD/MM/YYYY")
        txbBalanceDatePaid = .Cells(1, 12).Value
        txbBalanceDatePaid = Format(CDate(txbBalanceDatePaid), "DD/MM/YYYY")
        
    End With

End Sub

I am only having problems with the two specific cells when loading the 'EditData' Userform (both of which are dates) and having them formatted as a date when the form opens (shown in the code above.) If the relevant cell hasn't had a date written to the sheet it originally there is nothing to format so it errors out.

I've tried all sorts of ways to resolve this with 'If' statements etc in the LoadData sub and also by having two different LoadData Sub's 'LoadData 1' & 'LoadData 2' when the userform is activated preceded with another 'if' statement seeing if there is anything in the cells or not.

I've tried so many different ways to fix this I've literally lost the plot hence this thread. So if my question makes any sense I'd love to know what I need to do to get this to work.

Many thanks Paul

 

[DanteAmor]

With Sheet1.Range("A3").Offset(m_currentRow)

That line is very confusing to me.
I don't know why you make a Offset to cell A3.
I also don't see the declaration of the m_currentRow variable in your code, I assume it is a global variable.
If the above is correct, then try the following:

Public Sub LoadData()

    With Sheet1.Range("A3").Offset(m_currentRow)

        txbDepositDatePaid = .Cells(1, 10).Value
        If IsDate(txbDepositDatePaid) Then txbDepositDatePaid = Format(CDate(txbDepositDatePaid), "DD/MM/YYYY")
        txbBalanceDatePaid = .Cells(1, 12).Value
        If IsDate(txbBalanceDatePaid) Then txbBalanceDatePaid = Format(CDate(txbBalanceDatePaid), "DD/MM/YYYY")
       
    End With

End Sub

If it doesn't work for you, then explain in more detail:
- why the offset of A3
- How you have declared the m_currentRow variable.
- What does the error message say?
- On which line of the macro it stops.

 

[Pacman52]

Many thanks for the reply and the solution which did the trick. I've been so focused on making sure a user inputs any date data correctly using 'If Not IsDate' I totally forgot you can validate something by simply removing the 'Not'.

Just one follow on though whilst testing I noticed that even though the worksheet is showing dates correctly formatted i.e. DD/MM/YYYY when the EditData form is loaded they show/Load as American format i.e. MM/DD/YYYY. I've looked through all the macros on all the Userforms and all is correct so any idea why this could be happening?

In reply to your questions - so sorry I tried to simplify my first thread by just showing the problematic code but in reality the EditData form is called from a selection in a listbox hence the offset and m_currentRow (which is declared as a global variable).

Paul

 

[DanteAmor]

Just one follow on though whilst testing I noticed that even though the worksheet is showing dates correctly formatted i.e. DD/MM/YYYY when the EditData form is loaded they show/Load as American format i.e. MM/DD/YYYY. I've looked through all the macros on all the Userforms and all is correct so any idea why this could be happening?

Check that you actually have a date in the cell and not text.
If it is a date with the following, the format will be dd/mm/yyyy

Format(CDate(txbDepositDatePaid), "DD/MM/YYYY")

 

[Pacman52]

Thanks for the reply - the cell is formatted as a date with *14/03/2024 and is showing the date correctly as it was input e.g. 14-09-2024 but when the EditData form loads the cell it shows as 09/14/2024.

As mentioned I've double checked all the code in Editdata userform and thats all correct so I have no idea why it reverses it.

Paul

 

[DanteAmor]

I'm not understanding.
Where do you have the problem?
When do you pass the data from the cell to the texbox?
When do you pass the data from the textbox to the cell?

If it is from the cell to the textbox, by setting the format to dd/mm/yyyy, then it is displayed in that format.

Put all your code here to review.

 

[Pacman52]

Hi Just to update you on this - I found the problem was when the userform data was being written to the worksheet and on those two specific textboxes I hadn't set any formatting so it was just writing the value I assume as a text value. I adapted the 'Is Date' code in your earlier post and all seems resolved now.

I have no idea why the worksheet was showing/converting the date to the American format though as the actual worksheet cell format was set to the UK format. I thought cell formatting was superseded when writing data to a worksheet cell using VBA?

Anyway thank you so much for your help and advice on this one - thankfully now I can move on with the testing phase of this project.

Paul

 

[DanteAmor]

I have no idea why the worksheet was showing/converting the date to the American format though as the actual worksheet cell format was set to the UK format. I thought cell formatting was superseded when writing data to a worksheet cell using VBA?

VBA defaults to a US locale whenever it has to interpret data. If you are operating with actual date values, then there is no issue, but as soon as some sort of conversion occurs - usually coercing text to a date, you have to explicitly tell VB what format to use.

Also check: When you enter text in a cell, Excel assumes it is a date and formats it according to the default date settings in Control Panel.