Posts by Grandma7

    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
    1. Sub Check_Inactivity()
    2. Const Inactivity_Delay As Date = #12:07:00 AM#
    3. If LastActivityTime + Inactivity_Delay < Now() Then
    4. ThisWorkbook.Close True
    5. Else
    6. Application.OnTime LastActivityTime + Inactivity_Delay, "Check_Inactivity"
    7. End If
    8. 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.

    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!

    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.

    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.

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

    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.

    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
    1. Private Sub txtDate_AfterUpdate()
    2. If IsDate(Me.txtDate.Text) Then
    3. Me.txtDate.Text = Format(Me.txtDate.Text, "mm/dd/yyyy")
    4. End If



    End Sub

    Greetings. During a brief hospital stay, I had lots of time to dig into this project and the issue previously experienced. Thanks to much of the information in this Forum, and a couple of of others, I've solved the initial issue by utilizing a helper sheet. Thanks for that information.


    I am encountering a new, different issue with this almost completed project, but will post as a new issue and attach the file as it exists now (still much more work to be done before it's complete).


    Thanks everyone!

    Thanks for the suggestion. And I apologize that I didn't post properly.


    The suggested modification still generates an error; however, after much digging and searching, I think I figured out my mistake. I need an additional step, an advanced filter, in order to finish my project.


    I'm still learning, and testing my latest assumption. If/when I get it, I will post an update.


    Hope I'm on the right track.

    Good afternoon. I am new to this forum and extremely new to VBA. I've searched this forum, and also did a Google search for answers, and am coming up short. I hope I'm posting question this correctly!


    Using Excel 2013, I currently have a spreadsheet, with a total of 40 columns, where injury data is recorded. I stumbled through creating a Userform to make it easier to enter all that is required.

    Entries in the spreadsheet are summarized in the list box. The list box does not display all 40 columns; rather, only the most crucial information from 13 select columns (not all are adjacent to one another) from the spreadsheet.


    If an entry needs to be modified, the user double clicks on the record displayed in the list box, it is loaded back into the User Form, and the appropriate edit can be made and saved back to the original spreadsheet.


    I was recently asked to add a search box, allowing users to quickly search through/find entries on the spreadsheet. The search criteria is limited to these three columns: Last Name (column C), First Name (Column D), and Staff ID (Column E). I've added a Command Button next to the search box (txtSearch) that, once clicked, would search for and filter the items displayed in the list box based on the entry in the search box.


    I used the following code, associated with the Command Button, but receive a "subscript out of range" error (run time '9'):



    The error seems to focus on this line of code: iSearch = Worksheets("Sheet2").Range("A1").CurrentRegion.Rows.count


    I am really lost, and would appreciate some guidance on what I'm doing wrong.


    Thank you.