Userform with Static Search Criteria

I am creating my first Userform and there are tons of resources and articles to help do that. I've also seen many examples of how to use textboxes or dropdowns to search and return results. I am looking for some direction or examples on how to create static search criteria. Example - when a user clicks a command button the userform will automatically initialize and load all results where column 1 = "new" or column1 = "in review" and column 3 = blank or column 3 = "pending". the user can then click next/prev and update any of the returned records. If anyone knows of some examples to reference? would be greatly appreciated.

Thanks as always

Without an example workbook, it's difficult to visualize what you are trying to achieve. If you could attach one, then myself and others should be able to guide in the right direction.

I didn't post a sample workbook because I'm just getting started. So far just have the form created but I have attached that, No code yet. I like the example you attached. I plan to have some fields not editable and some that are updateable depending on which command button is used to launch the form.

I would like to eliminate the database search section and just have set criteria for the search depending on which command button was clicked to launch the form and then use Next and Prev buttons to navigate records. This form will be utilized by different departments and when they click their button I just want them to be able to update the information they need to. The teams are not very tech savy and the search section in torachan's example would be confusing for them. They currently just filter the sheet to try and see the ones they need to update but often many are missed. Also will utilize the form during a virtual meeting. currently we just use the spreadsheet and you can't see all the data at once and there is lots of scrolling back and forth.

I have attached an updated file. I added a new tab which identifies each cmd button that will launch the form. Also the search criteria for each button and the corresponding form elements that would have editable property set to FALSE. Hopefully this gives a clearer picture of what I am trying to accomplish. The form launch buttons are on the 2023 PSI tab

Really appreciate any guidance or resources

Appreciate any help and continuing to do some independent learning. I used to do a lot of web programming with SQL and VBScript but its been ages now. In my current role I think picking up some reading on VBA will continue to be beneficial but will be a slower journey as not at all part of my core responsibilities.

I have been searching for code that would function like I wanted with Next and Prev buttons from a search but no luck. seems most want to use the listbox technique to load search results. However, looking at the code in torachan's sample file and other articles I've read I had some thoughts on the logic that might make it possible I wanted to run by others in this group first before I even attempt to try and code anything out.

1. Use a global module and have each worksheet cmd button call a different routine that sets a variable "SearchOpt" =1 or 2, etc along with calling Userform.Show
2. In the Initialize sub run a .find/find next on the columns needed depending on the value of "SearchOpt". Since I can only search for one criteria with .Find have to nest a Do Loop to search for the additional criteria
3. Anytime all criteria match load that row number into an array "ResultsArray"
4. Use Next and Prev buttons in the Userform to move through each row using the row numbers in the ResultsArray

If that sounds like a logical and efficient way to go about the problem I can try and start putting some code together? This is my first VBA project so I could be way off base.

Also I thought it was pretty slick in torachan's file to loop through the controls with numeric identifiers instead of logical names. In the past with VBScript I always named form controls but has me thinking it might be easier to go back and rename mine. I'm using a combination of textboxes and comboboxes. Should I name them all with "textbox" to only have one loop?

torachan

I've created what I feel is a functional userform that functions exactly how I intended with one issue.

When I select a button and the criteria it triggers for the search returns no results (QAS Review in the attached file) I throw a msg box that says no cases to review and then try to close the userform

Please Login or Register  to view this content.

but I get an error "Object variable or With block variable not set". I've tried several things and read posts but can't figure that part out.

I'm open to any suggestions on upgrades to any of the code or potential issues I may not be aware I'm causing. One thing I'm definitely aware of is that it is not efficient to loop through every row in a table like I'm doing. However, I will never have more than 300 rows and ,most importantly, I could not get the find/find next to work. When it was working decently I could never match up the row in the worksheet to the row in the find range for getting and updating values.

Fresh brain this morning. Found the error was from trying to unload the form during initialization. I moved the logic above to the Activate routine and it works great. I didn't like that the form still appeared so I went back and changed the unload to Hide in the above code. then on the activate method on perform the unload. This seems to work for me.

Please Login or Register  to view this content.

This is the final version of the form.

cell range referencing to obtain stored details, far better to use 'table' referencing

- I'm not fully understanding what you are saying here when you say to use table referencing.

The main purpose this form was built was to help facilitate a meeting. This spreadsheet was used during a virtual meeting where each new or in review case is reviewed collectively. In current state the spreadsheet is screenshared and there are lots of "can you scroll left?", "can you scroll up?" "Can you stretch the cell, I can't read it all". The form was envisioned to allow a cleaner and one click view of all details of each row to speed up the process. So when you say that a large number may match you are correct. But if they are showing up because they meet criteria each one needs to be reviewed by the review board during the meeting. Any that do not get discussed just roll over into the next meeting which is on a monthly basis. In reality there are only about 15-20 new cases a month and usually all can be reviewed during the 1.5hr meeting.

The additional buttons for each department (Coding, CDI, Compliance, QAS) are meant to simplify the process for them. Before the rows would be color coded if they needed to review and often were missed. The button should simply their view only showing them cases they need to review. The goal is for each department to go in and vote (Status Control combo - Agree, Disagree, Pending) and add in their comments prior to the meeting. That way all data is available during group discussion.

The data for the first 6 columns comes from another software suite and must be copy and pasted into the spreadsheet. The add button was added on the back end because the goal of the form is not data entry but data view and update. I only thought it might be more convenient for the spreadsheet owner to have an easy way to add new rows this week.

Hope this helps without getting too nitty gritty in the workflow.