Command Button to take me to the Drop-Down or List-Box's Selection on another sheet....

[SpoodyJ]

Thanks for taking the time to try and figure this one out!

At my work we keep a log of companies that do work for us (particularly create drawings/schematics).
We take their name and assign them a number. We call this an F#, which stands for Foreign Number (eg. F-125100-001 thru -999).

What I want to achieve is have a Main Page, that includes a Drop-Down List, or List-Box, or maybe even something better you know of? I keep thinking about it being a Drop-Down/List-Box one or the other.
So...
I'd like a Drop-Down/List-Box that contains all the Vendors names so that I could choose the Vendor and then have a Command Button that would take what is selected in the Drop-Down/List-Box and magically take me to the Vendors name and Info when pushed. I'm thinking have all the Vendors info on a separate sheet of course, there are as of now well over 500 Vendors, and we get new ones added every so often.

I'm am new to VBA/Macros, I have no doubt that there may be a better way to do this, but for days now, in my mind, I keep thinking this is achievable. My hopes are to find a way that I could get some code that essentially says "When Command Button is pushed with this particular List-Box/Drop-Down selection then go to cell #xxx" ("xxx" will be the cell that the selected vendor is located on a separate sheet). I could do that for every Vendor we have, or maybe there is a way I could take the drop down selection and have whatever is selected thrown into a "Find" or "LookUp" search of some kind that would take me to the desired vendor info.

I apologize that this was so long of a post! I've searched forums watched countless YouTube videos, started reading and searching through books, starting at the basics and working my way up in hopes I would run across an answer and I cannot seem to get close (I know, I just haven't learned enough yet) but at the same time I haven't been convinced that this isn't possible. If its not possible please let me know so I can stop trying to figure it out. I am open to other ways of doing this of course.

With Anticipation,
SpoodyJ

 

[Sunny Kow]

Hi spoodyJ

You can use a Worksheet Change event to do that. You worksheets name MUST match the data validation list.
In this example the data validation is assumed to be in cell A1.

Private Sub Worksheet_Change(ByVal Target As Range)
    'Check if that particular cell have changed
    If Target.Address = "$A$1" Then
        'Go to the selected worksheet
        Worksheets(Target.Value).Select
    End If
End Sub

 

[SpoodyJ]

[FONT=&quot]Awesome! Thank You so much for the prompt reply!

This does work and gets me a lot closer to my vision. Doing it this way will mean I have to create a worksheet for every "Vendor" which will be close to 600 worksheets with my Workbook. I dont have a problem spending the time doing that because it essentially does what I want and I believe that Excel can handle that many sheets, although I wonder if you or anyone else has anymore suggestions on doing it any other way?

For now I will assume this is the only way, and if it is my question is now, is it possible to tweak that code a bit. But instead of a quick change can the Vendor name be selected in the Drop-Down and then use a Command Button to initiate the change of Worksheets?
Or -
Maybe if we could use an ActiveX Control List-Box along with an ActiveX Command Button instead of a Validation Drop-Down List?

With Anticipation,[/FONT]
[FONT=&quot]SpoodyJ[/FONT]

 

[Sunny Kow]

Hi

You can create a button using Insert-Shapes and assign the following macro to it if you don't want to do it automatically.

Sub GotoSheet()
    Worksheets(Range("A1").Value).Select
End Sub

For the part about creating 600 worksheets for the vendors, it is possible to do that with VBA.
It will depend if the sheets you want are empty or is it from a template? You need to be aware that the max length of the sheet name is 31 characters.

 

[tonyyy]

SpoodyJ,

The code below uses the following assumptions:

There is a drop down list of vendor names in cell A1 of worksheet "Main".
There is an Active X CommandButton1 on worksheet "Main".
There is a sheet named "VendorList" that contains vendor names in Column A. (In Column B and beyond are the F#, contact name, phone, email, etc.)

Private Sub CommandButton1_Click()
Dim sVendor As String
Dim rng As Range

sVendor = Sheets("Main").Range("A1")
With Sheets("VendorList").Range("A:A")
    Set rng = .Find(What:=sVendor, _
                    After:=.Cells(.Cells.Count), _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
    If Not rng Is Nothing Then
        Application.Goto rng, True
    Else
        MsgBox "Nothing found"
    End If
End With
End Sub

Right-click the CommandButton1 and select View Code, then copy/paste the above code into that sheet module.

Cheers,

tonyyy

 

[Sunny Kow]

Hi spoodyJ

This code will allow you to create all of those 600 worksheets at one go. You will need a list of vendors name and a template (if you have one). You will need to modify my codes slightly to cater for the template's name.
Select the names and then run the codes. Please do it with a COPY of your file.

Sub CreateWS()
    'Select your range where the names of your vendors are located
    'If you have a worksheet template, it will be copied for each vendor else blank worksheets will be created
    
    Dim Cell As Range
    TemplateName = "MyTemplate" '<===change this if you have a template else use "" to create blank sheets
    
    Application.ScreenUpdating = False
    For Each Cell In Selection
        If TemplateName = "" Then
            'Create a blank worksheet for each record
            Sheets.Add After:=Worksheets(Worksheets.Count)
        Else
            'Create worksheet from a template for each record
            Sheets(TemplateName).Copy After:=Worksheets(Worksheets.Count)
        End If
        
        'Rename worksheet
        With ActiveSheet
            .Name = Cell.Value
        End With
    Next
    Application.ScreenUpdating = True
End Sub

 

[SpoodyJ]

You Guys Rock!!!

Thanks Tonyyy That worked, it is exactly what I was wanting.

Sunny Kow, both of your worked also just fine, I actually like it better than what I originally wanted. It would be nice to use an ActiveX control to choose the Vendor instead of the Data Validation Drop-Down list. I am going to be attempting to try and figure that out.

 

[tonyyy]

SpoodyJ,

You're welcome. Glad it worked out for you...

 

[SpoodyJ]

I've stumbled upon a problem I cannot seem to figure out. (I'm getting better at this VBA stuff though lol)

My workbook is setup as Sunny Kow's code above so that each vendor has its own worksheet.
I have a Main page which has the drop down list of vendors and a button that will take me to the vendors sheet.

I got to thinking, how am I going to add a vendor when needed? So I been trying to create a userform which will load when a button is clicked from the main page.
so there will be a button that says Add Vendor, and when clicked the userform will open.
The User form will have a textbox and an add button, a cancel button, etc. what I cannot figure out is...

When I add text to the TextBox I need the input to go into the first empty row in column "A" of the WorkSheet that is named "VendorList".
On top of that it would be nice to have a similar Macro (like Sunny Kow did above in Post #6) to have that entry created into a worksheet at the same time using "My Template" (My templates sheet is named "My Template"). Also I need to textbox to be limited to no more than 31 characters since that's the max for a worksheet name.

Sounds simple and I'm sure it is but I'm lost.

With Anticipation,
SpoodyJ

 

[Sunny Kow]

Hi SpoodyJ
Try this

Sub AddNew()
    Dim NextRow As Long
    Dim NewSheetName As String
    
    'Get vendor's name
    NewSheetName = InputBox("Enter New Vendor's Name (Max 31 Characters)")
    
    'Check for length of text entered
    If Len(NewSheetName) > 31 Then
        MsgBox "Max length is 31", vbCritical
        Exit Sub
    End If
    
    'Create new sheet from template
    Sheets("My Template").Copy After:=Worksheets(Worksheets.Count)
    
    'Rename the new sheet
    ActiveSheet.Name = NewSheetName
    With Worksheets("VendorList")
        NextRow = .Range("A1").End(xlDown).Row + 1
        .Cells(NextRow, 1).Value = NewSheetName
    End With
End Sub