How to Create a search by Sheet Name function in a userform?

[dan_elmore87]

Basically my aim is to have a client database with 1 sheet per client, i wish to have a userform with various links and functions, the part im struggling with is being able to have a text box and a search button which will allow me to search for and bring up the desired clients sheet?

Any Help is much appreciated

Many Thanks Dan

 

[Norie]

Dan

Instead of a searchbox why not have a combobox which lists the clients?

When you select a client from it you are taken to their worksheet.

 

[dan_elmore87]

This could work hadn't thought of that! Dont suppose you could help me with the coding could you? Im pretty new to this im ok with html but this is a whole new world :-s

 

[Norie]

Dan

Creating the combobox is quite straightforward and the rest shouldn't be complicated but will depend on how you want to do things and how things are setup.

For example do you want to jump to the client worksheet as soon as they are selected from the combobox.

Or would you want a 'Go' button that needs to be pressed to go to the selected client's sheet.

Also, what are the sheet names?

Will it be the client name, something based on the name, the client ID...?

By the way, what other links/functions do/will you have on the form?

One thought I has was to display some sort of summary for the selected client so you get a quick overview without actually going to the sheet.

Obviously you would keep the ability to jump to the sheet.

Perhaps I'm overthinking things a bit though.

Anyway, if you can post some more information I'm sure we can help.

 

[dan_elmore87]

Creating the combobox is quite straightforward and the rest shouldn't be complicated but will depend on how you want to do things and how things are setup.

Or would you want a 'Go' button that needs to be pressed to go to the selected client's sheet.
A go button would be prefered

Also, what are the sheet names?
These will be client names

By the way, what other links/functions do/will you have on the form?
I would like to create a function that will allow me to create new clients (and name them hopefully) and on each client sheet a back button to return to the userform and save any changes made


One thought I has was to display some sort of summary for the selected client so you get a quick overview without actually going to the sheet.
Im not sure what you mean or how this would work, but like i said im really a novice when it comes to VBA so all your help and advice is greatly appreciated.

 

[Norie]

For the last bit, I was just thinking that when you select a client some data is pulled from their worksheet and displayed on the userform.

Just an idea though and could be totally inapprorpriate - I don't even know what data you have.

Anyway, forget that for now.

This is how you can set up the combobox?

1 Add a combobox call it cboClients say, to the userform.

2 Put this in the userform module:

Private Sub UserForm_Initialize()
Dim ws As Worksheet
 
      For Each ws In ThisWorkbook.Worksheets
 
           cboClients.AddItem ws.Name
 
      Next ws
 
      cboClients.Value = "Select client..." ' optional
 
End Sub

3 Add a commandbutton, call it cmdGo.

4 Put this code in the userform module.

Private Sub cmdGo_Click()
       
      If cboClients.ListIndex <> -1 Then  ' check a client has been selected
             
             Application.Goto Worksheets(cboClients.List(cboClients.ListIndex)).Range("A1"), True
 
      End If
 
End Sub

That should get you started.

 

[dan_elmore87]

The combobox and go button work brilliantly thanks!

For the last bit, I was just thinking that when you select a client some data is pulled from their worksheet and displayed on the userform.

Just an idea though and could be totally inapprorpriate - I don't even know what data you have.

This sounds interesting?

 

[Norie]

Do you have any idea what data you might want to pull from a client worksheet?

What data's on a client worksheet anyway?

 

[dan_elmore87]

Basically its for a hair salon, at the minute all of clients details are stored on cards in a box the aim of this database is to replace this and create a "company dashboard" from which all day to day task can be launched from. ie i now have a cmd button linking to salon website and a button going to an online appointment system.

In terms of what data each sheet will have a basic table recording each clients visits. I have attached a sample picture,

 

[Norie]

Have you considered doing the whole thing on a userform?

As far as I can see you could have a listbox for treatments with all the required columns.

Items (treatments) could be added, edited, deleted etc.

The combobox can be used when populating the listbox.

ie select client and their treatment data will get populated in the listbox

You could still have the individual worksheets for each client.

Again, just an idea.