VBA Userform displays serial number instead of date

    I have successfully laid out the format of the userform and the rest of the necessary elements for this project. Initial testing works ok, with some refinement remaining. But when I call up a record to update, date fields display the serial number pulling from the worksheet, not in date format.


    Several searches, and attempts, to adjust the textbox display have been unsuccessful. I tried inserting a calendar, as some Google searches and other forum comments mentioned, but that doesn't do it either.


    I'm not quite certain what the problem is, or what the appropriate next steps are to correct this. What am I missing or doing wrong?


    Thanks in advance for any and all assistance.



    Code
    Private Sub txtDate_AfterUpdate()


If IsDate(Me.txtDate.Text) Then
Me.txtDate.Text = Format(Me.txtDate.Text, "mm/dd/yyyy")
End If



    End Sub

    In the cmdEdit_Click() routine use

    Code
        Me.txtDate.Value = Format(Me.lstDatabase.List(Me.lstDatabase.ListIndex, 1), "mm/dd/yyyy")

    Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags


    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Thanks.

    Apologies for the delayed response.


    Thank you, Fluff13. That did it!


    As I put the finishing touches on my project, one issue remains and is stumping me. Of the 6 total tabs in this worksheet, two (2) are very hidden, three (3) are hidden - opened by a command button on an 'as needed' basis, and the main page is visible but protected (I have summarized data pulled from the database sheet, which is one of the very hidden tabs, that I use to generate graphs that the user can see when the workbook is opened).


    I'm trying to protect the tabs that are hidden, but that can be opened using command buttons as needed. I can get the main page protection to work. I can also set protection protection on two of the 3 hidden tabs. But for some reason the final tab, which I want to set with the same password as the main page, just won't take the password as set in code. I've tried rearranging the order of the passwords set in Private Sub Workbook_Open() so that the main page and Tab #6 are listed first, then the next tabs with different tabs are listed next.


    I've researched setting different passwords for different tabs, and am wondering if there's a limit on how many passwords/tabs can be set? I just can't figure out what I'm missing with getting this last tab to accept the password.


    Any advice, assistance or direction is deeply appreciated.


    May I add, I'm so encouraged by the wealth of information I've found on this forum, and the positive assistance/direction given to newbies, like me, by the members of this forum. I'm so glad I found OzGrid!


    Thanks, everyone.

    What is the code you are using to protect and unprotect the tabs?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    The above is what I am using. Sorry, I forget to include the code with my earlier post.

    That code is fine. Couple of questions:


    1. I assume you know the sheet indexes go from left to right, so sheets(6) is always whatever tab is last in your 6 sheet workbook? (it would be safer to use the codenames of the sheets)

    2. You intended to put spaces at the start of all the passwords apart from the first one?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    When I first entered the code, I did have each sheet with the respective password in order - 1 through 6. During testing, sheet #6 wasn't protecting when the workbook opened. Sheet #6 has specific cells that are locked, so that data can populate from Sheet 2 (which is one of the very hidden sheets) when the associated command button is pressed.


    When I copied/pasted the code from my project, I replaced the actual p/w with generics before uploading. I do see an additional space in what was pasted - my apologies. The actual code in the project is without spaces.


    I'm glad the code is correct, but I just can't figure out why Sheet 6 is giving me problems.

    So I don't quite understand why, but I just deleted Sheet #6, then added it back into my project. And the protection now works when opening the workbook.


    Thank you so very much for all your help. I can now mark my project 'completed'.

    Thank you. I also found and corrected a couple of mistakes (typos) that were contributing to this. But now, when I attempt to launch the userform from the command button on the Home page, I'm getting the following error:


    "The cell or chart you're trying to change is on a protected sheet."


    Nuts! I don't want to unprotect cells that other users can change, or remove formulas (this has happened too many times before, resulting in considerable time spent putting entries and formulas back in!)


    This error never occurred before.

    You have two options


    1. Within the code unprotect the sheet, at the start of the code, then add the protection back after it has run


    Code
    Sheet1.Unprotect "password here"
'your code
Sheet1.Protect "password here"

    2. Use protect with userinterfaceonly - see example and explanation here:


    Run code on protected sheet

    I started with code that included "userinterfaceonly=True", as you mentioned. That's why this latest error was a real shocker!


    I'll try the other recommendation, which is to run sheet protect/unprotect.


    FYI, I tried opening the reference article with the link you supplied (I'm assuming that links to the article 'Excel VBA: Macro code to run macros on protected worksheets & sheets") and received a security warning and couldn't get any further.


    BTW, I had already read the referenced article before setting protection, which is why I was so surprised that one of the 6 sheets would continue to cause problems!

    My web site is transferring to a new host. I think that 's the problem. I accessed it before I posted this morning.


    When the transfer is complete it will be more secure and faster to load.

    Userinterfaceonly is not 100% reliable but works in most situations. It sounds like your Userform_Initialize code is doing something that causes an indirect change to a worksheet, but without the code it's hard to comment.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    My web site is transferring to a new host. I think that 's the problem. I accessed it before I posted this morning.


    When the transfer is complete it will be more secure and faster to load.

    So I have finally gotten all codes to work, and all errors resolved. I moved this project into the network folder at work, where I and another user have access to it. Everything works quite well. But I ran into an unexpected issue yesterday that I could use some help with.


    When one instance of this workbook is open - no problem. But if I open the workbook to search for a record, and my counterpart already has the file open, I cannot close the workbook without saving it and then it holds my search which creates a vba error when the workbook is opened again.


    I have a "time out" code such that after a period of inactivity, the workbook will automatically close and save. This is very much needed so that if the workbook is open, and the user leaves her desk, the fill will automatically close/save so others (me, mainly) can then open the workbook and make the necessary entries:

    What I've been searching for is a way to bypass the message box, when the workbook sits idle for a period of time, and close/save automatically. Below is the current code I have in a TimeOut module:


    Code
    Sub Check_Inactivity()
    Const Inactivity_Delay As Date = #12:07:00 AM#
    If LastActivityTime + Inactivity_Delay < Now() Then
        ThisWorkbook.Close True
    Else
        Application.OnTime LastActivityTime + Inactivity_Delay, "Check_Inactivity"
        End If
    End Sub

    Currently, the message box appears after the period of inactivity. But what I would like to do is bypass that message box and automatically close/save the workbook after the period of inactivity. I Googled a couple of sites and tried some of the recommended code, but I still get the message box after the period of inactivity. I would appreciate any assistance and/or insight as to what additional code can be used.


    Thanks.

    Here's some code that saves the workbook if no changes have been made for say ten minutes.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login