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

dan_elmore87

New Member
Joined
Nov 20, 2011
Messages
8
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 20, 2011
Messages
8
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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.:eek:

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

dan_elmore87

New Member
Joined
Nov 20, 2011
Messages
8

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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:

Code:
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.

Code:
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

New Member
Joined
Nov 20, 2011
Messages
8

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 20, 2011
Messages
8
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,
e809k9.jpg
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,462
Messages
5,596,280
Members
414,051
Latest member
tabecker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top