Solved

Make Active Sheet tab, from a ListBox.

Posted on 2014-10-22
45
139 Views
Last Modified: 2014-10-30
I attached Userform2

How would I one, getting the userform to load with the top item selected, so I can use arrow keys to move up/down.  And two,  Use first column to search visible sheet tabs and if it exists?  Press enter to go there and make it the active sheet tab?  Of course Esc would make it display, but pressing enter should make it go to that sheet tab.  I don't see the need for a button on the userform..  unless it is needed to get it there.

I had to create a separate userform that could contain more info than just the sheet tab name.   The  other column is a better description of it that can use more characters.
UserForm2.frm
0
Comment
Question by:RWayneH
  • 23
  • 21
45 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
Could you attach the Excel file instead of the userform object?
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
We cannot view your userform you should instead as already pointed out upload the excel file.
gowflow
0
 

Author Comment

by:RWayneH
Comment Utility
Sorry, I thought you could import the userform into a workbook.  I attached the workbook.

Please look at the attached file.
There is a userform that I am currently using which you can see by running:  Button1_Click.  The new one that I would like to run with better descriptions of the sheets tabs using a listbox can be seen by running: ShowUserForm2

Some issues with doing it this way is that the listbox is populated from a list on a hidden sheet.  When a new sheet tab is added, it is not added to that list, unless a separate script is used to do both, add a sheet tab, then add that to the listbox too with extended description.

Perhaps I am thinking through this wrg...  any additional thoughts?
HHEPricingDetail-2.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
I don't understand what you want to achieve.

Activating Button1_click
brings up userform1 and has all the sheets and if you select one and click Goto it gets you to that sheet and the userform1 disappear. Is that ok or you need something else here ???

Activating ShowUserForm2
brings up Userform2 with a list of codes and descriptions and no buttons on that sheet.

So what do you want to achieve please clarify it 'CLEARLY' !


gowflow
0
 

Author Comment

by:RWayneH
Comment Utility
Button1_click  is limited to the sheet tab chars


ShowUserForm2 will not be.  Column one, is the actual sheet tab name, column two is a longer description of that sheet tab that is in a hidden sheet.  Goal to create a better more descriptive Sheet goto, and a new CreateSheetTab macro that will maintain the hidden sheet and populate listbox accordingly.  So I need another two field inputbox that field one is the actual sheet name, and the second that is the long description.  Does this make better sense?  

Perhaps we could use some sheet tab naming validations that are in this?

Sub AddSheet()   
   Dim wsNew As Worksheet
   Dim strShName As String
   Dim strSheetName As String, wks As Worksheet, bln As Boolean

    For Each C In Selection
    If IsEmpty(C.Value) Then GoTo SkipToHere:
    If Len(C.Value) > 31 Then GoTo SkipToHere:
    
    Dim IllegalCharacter(1 To 7) As String, i As Integer
    IllegalCharacter(1) = "/"
    IllegalCharacter(2) = "\"
    IllegalCharacter(3) = "["
    IllegalCharacter(4) = "]"
    IllegalCharacter(5) = "*"
    IllegalCharacter(6) = "?"
    IllegalCharacter(7) = ":"
    For i = 1 To 7
        If InStr(C.Value, (IllegalCharacter(i))) > 0 Then GoTo SkipToHere:
     Next i

strSheetName = Trim(C.Value)
'Check for duplicate sheet tab name.  If true skip to next.

    On Error Resume Next
    Set wks = ActiveWorkbook.Worksheets(strSheetName)
    If Err.Number = 0 Then GoTo SkipToHere:
   
    If C.Value <> "" And Not InStr(C.Value, (IllegalCharacter(i))) > 0 Then
         strShName = C.Value
         Set wsNew = Worksheets.Add
         wsNew.Name = strShName
    End If
       'Sheets("ReadMe").Select  'Need? other one did?
SkipToHere:
Next
End Sub

Open in new window

0
 

Author Comment

by:RWayneH
Comment Utility
The hidden sheet where the items for the Listbox are is called.  MatlTracker and there is a named range in it (ListOfMatl).  So each time I add a sheet I will use the macro if I want it to be in the list.

I have not even thought about the how to delete a sheet?  I will need to use a macro for that too.

Hope this makes better sense...
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
So let me recap then:

You want to modify UserForm2 so that it will meet following objectives:
1) Be able to select a sheet in the list and Goto that sheet.
2) Be able to Create a Sheet with the rules specified in AddSheet
3) When a sheet is Created have the Option to either Included in the Range ListOfMatl or Not
4) Have the possibility to also Delete a sheet

Am I correct in the listing of these 4 items ? If still more issues please advise or corrections/clarifications pls do.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Did you had a chance to read my last post ?
gowflow
0
 

Author Comment

by:RWayneH
Comment Utility
Sorry for the delay.  Yes you have it correct and all cases.  However I would like #1 by itself and 2,3, and 4 together.  Thanks.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
By that you mean we treat in this question #1 and if all ok then you close it and open a new one that will treat #2, 3 and 4 ?
gowflow
0
 

Author Comment

by:RWayneH
Comment Utility
Sort of.  I would like #1 to be its own hotkey (usually use Ctrl+h) a quick Sheet Goto, nothing else.  All I have to do is use my arrow up/down keys and press enter.  The other 2,3 and 4 in its own.  If it is easier to do separates for the adds (2, 3) and a third for deletes?  That is fine...  I would at least like #1 separated out.  I normally add them to my Quick Access Toolbar...   Thanks.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok fine for #1 here is it.

Simply press CTRL h anywhere and when the list comes out select any item by the arrows and when you want to choose just press ENTER on the selected item and see the results. The system will also check if the item exist in the workbook and will let you know.

here is the code for that.

Private Sub ListBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim WS As Worksheet
Dim sSheetName As String

If KeyAscii = 13 Then
    If ListBox1.ListIndex <> -1 Then
        sSheetName = ListBox1.List(ListBox1.ListIndex, 0)
        On Error Resume Next
        Sheets(sSheetName).Activate
        If Err = 0 Then
            On Error GoTo 0
            Unload Me
            Exit Sub
        End If
        MsgBox "Sheet " & sSheetName & " does not exist !", vbExclamation, "Display Sheet"
        On Error GoTo 0
    End If
End If
End Sub

Open in new window


gowflow
HHEPricingDetail-2.xlsm
0
 

Author Comment

by:RWayneH
Comment Utility
Is it referencing the sheets in ListBox1 even though it is ListBox2 that I am looking at?  That confused me when listbox1 was in the code.   It works great!!  just trying to understand it.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
hein ??? don't understand your post ! is there a problem ?
gowflow
0
 

Author Comment

by:RWayneH
Comment Utility
It actual works nice, I can use the original that get hidden sheets and the new one to get visible.  I like this.
0
 

Author Comment

by:RWayneH
Comment Utility
Your solution references ListBox1,  the one that we are recreating with the extended descriptions in ListBox2.  Just saying that it confused me a little.
0
 

Author Comment

by:RWayneH
Comment Utility
In the hidden sheet tab, where the list is sorted by the Description and should start at the top or row one.  Is there a way to start typing a description and it will goto that group?  Also when pressing "Esc" the ListBox does not disappear.  The solution for #1 at a first glance.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Well I took your userform2 and there it had Listbox1 and userform1 also had Listbox1 !!!!
gowflow
0
 

Author Comment

by:RWayneH
Comment Utility
This is good...   I forgot that each userform can have a ListBox1...   my bad.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
no problem ! pls let me know what you want to do from here ?
gowflow
0
 

Author Comment

by:RWayneH
Comment Utility
Finish #1  by making the "Esc" key exit the userform and the typing of the Description search and for some reason the Listbox starts in the middle and not at the top row, this will finish off #1.

Then #2,3 and 4.  In #2 the adds to ListBox, After the add, we will have to sort Descriptions so the new add falls into place and not at the bottom of the list.  #3.  Lets just add it to the list each time.  We do not need to option to add to the ListBox or not...  just add it every time.  #4 will handle removing it.
0
 

Author Comment

by:RWayneH
Comment Utility
Let me clarify #2, the Description is not what is on the sheet tab.  It is column C of the hidden sheet.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok here it is for #1.

Already started working on the next issue. Pls let me know when you post the question by putting a link in here.
gowflow
HHEPricingDetail-2.xlsm
0
 

Author Comment

by:RWayneH
Comment Utility
I did not see the quick search by typing the first letter of the extended description to a grouping.  If I type a "W" the list should skip down toward the bottom...  We have close to 200 sheet tabs in our file.  They are not big sheets, but I want to prevent the holding.... and holding.....  and holding of the down arrow to get to the bottom of the list.. or middle.. etc.
0
 

Author Comment

by:RWayneH
Comment Utility
Are you requesting I create another question?  Not sure I have done that..  I will give it  a try.
0
 

Author Comment

by:RWayneH
Comment Utility
Can we leave this in one question?  #1,2,3,4 all relate to each other.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok no problem
but you did not mention about the W and quick search let me see this
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
2 and 3 are create a new sheet and update the range ListOfMatl  and 4 delete a worksheet ?
pls confirm
gowflow
0
 

Author Comment

by:RWayneH
Comment Utility
correct.  4 deletes a sheet tab and from ListOfMatl    It will always have at least one.  I have been thinking about how to have this available all the time....  perhaps storing something in my .xlsb personal workbook that would create the hidden sheet and begin the ListOfMatl range in a give workbook?  This is going to be a VERY useful tool..  Appreciate your help with this.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Well here it is !! Search no more !!! (just joking) hope I answered your points by the following

This new userform2 will do the following:

1) Be able to select a sheet and just go there
2) If the sheet does not exist it will let you know.
3) Be able to Create a sheet by filling the Reference sheet and a description. If when filling the reference the sheet is already there it will let you know and prevent from creating a duplicate. If the sheet does not exist but is on the list it will give you automatically its description and then by pressing on Create you can then create the sheet and it will added to the range ListOfMalt
4) Be able to Delete a sheet and update again the ListOfMalt by deleting the corresponding row in that sheet and removing the sheet and updating the new list.
5) Any time you can clear the data from the Textboxes

Let me know if this addresses your issues.
gowflow
HHEPricingDetail-2-V01.xlsm
0
 

Author Comment

by:RWayneH
Comment Utility
This is AWESOME!!  Couples things.  I added a couple sheets and noticed that the sort did not happen in the Listbox based on the description.  It just added them to the bottom of the list.  I also was not able to delete a sheet.  Is there a trick to deleting a sheet?  I was think light it and click the button, but it is grayed out.  Is it difficult to be able to mouse wheel down the list too?  Almost there.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok here is how it works.

Every new sheet created is added to the bottom. If you want them sorted then will need to sort the data that is in the sheet MatlTracker is there a problem ?

When you highlight an item here is the logic:
It search to see if the highlighted item 'HAS' a worksheet already. If YES then it enable the button so you can delete the worksheet. If it did not find the you cannot delete a worksheet that does not exist so it DIMMS or grey out the button showing you that you cannot delete it.

Hope it clarifies.
gowflow
0
 

Author Comment

by:RWayneH
Comment Utility
I can add the sort at the end of the create process.  On the delete, that is how I thought it would work, however I am not getting anything activate the Delete button for some reason it is staying grayed out?  Any ideas why this is not working for me?
0
 

Author Comment

by:RWayneH
Comment Utility
It was in the attached that it was not working, but when I pulled it into a larger workbook it is working.  Let me play with this for a couple days and I will let you know how it is going.   THANKS!!
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok fine let me know.
gowflow
0
 

Author Comment

by:RWayneH
Comment Utility
Ok, after some testing here is what I am seeing.  Lots to Msgbox's  If I click Create or Delete, can we remove the msgbox and just do it?  Only alert me if there is an issue, like in a name being created?  When deleting a sheet, I tried (with no luck) to add the Application.DisplayAlerts = False  to getting rid of the ... permanently deleting... msgbox.
I also tried to insert a sort after the add and I am not putting it in the right spot.

Here is the Resort after add that I am try to add.

Sub ResortListAfterAdd()

    Sheets("MatlTracker").Visible = True
    Sheets("MatlTracker").Select
    
    ActiveWorkbook.Worksheets("MatlTracker").Sort.SortFields.Clear
    lastRow = Range("C" & Rows.Count).End(xlUp).Row
    ActiveWorkbook.Worksheets("MatlTracker").Sort.SortFields.Add Key:=Range( _
        "C3:C" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("MatlTracker").Sort
        .SetRange Range("B3:C" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B1:C1").Select
 
    Sheets("MatlTracker").Select
    ActiveWindow.SelectedSheets.Visible = False
End Sub

Open in new window

0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Is this a question we are answering or a Project ???
I don't understand !!!

Look at the intial question it says:

How would I one, getting the userform to load with the top item selected, so I can use arrow keys to move up/down.  And two,  Use first column to search visible sheet tabs and if it exists?  Press enter to go there and make it the active sheet tab?  Of course Esc would make it display, but pressing enter should make it go to that sheet tab.  I don't see the need for a button on the userform..  unless it is needed to get it there.

 I had to create a separate userform that could contain more info than just the sheet tab name.   The  other column is a better description of it that can use more characters.

Do not want to sound rude but I think we are way over the intial question.
gowflow
0
 

Author Comment

by:RWayneH
Comment Utility
I agree, but  I am having issues making edits to your solution.  This will be it...  I at least need the sort after the add,  I guess I can put up with all the MsgBox's
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Its ok I can adapt to what you need but needed to clarify this issue.

Please state clearly what you need to be done in point and I will do it.
No offence.

gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Again another issue regarding the Sort.

With all due respect I do not think that the Sort procedure you laid here is correct as it sorts only Col B and C in sheet MatlTracker ignoring totally that you have a table from Col B to Col H that ALL need to be sorted and not only Col B and C.

If you do this then your data will be messed up royally ! and you will end up with data sorted on Col C with info in row 3 and down for Columns D to K belonging to the data prior to the sort !

Again no offence. If you need the help of an Expert simply ask.
Rgds/gowflow
0
 

Author Comment

by:RWayneH
Comment Utility
The only data we use is B and C.  anything > column C can be deleted... (not needed).  I disregard column D on up.  Not sure what it is but I am having issues making edits to your code...  I is a little more advanced then I can handle within a ton of study and trail/error attempts.  Thanks.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
You don't need to make edits. Just tell me what you need to remove/add by points and I will do.

I gave you a professional solution but seems you like to cut corners. The msgboxes are only there when needed and as natural warning same that I do when I develop all solutions and not overkill. But if you feel some are not necessary pls let me know which ones and If you say that Col D till end not needed in MAtlTracker shall I take the liberty to delete them in the version I will post ?? Pls let me know as well so the solution is coherent.

gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
ok I guess we are on a different timezone so in order to be efficient I took the liberty to modify the code as per your last comments as I judge best fit to cater both for your requirement and for minimum safety of code (note that messageboxes are there to warn before critical no turning back issues such as deleting and can be avoided in other cases)

Here are my comments:

1) regarding the Sort I replaced all your proposed code of several lines into 1 single line which is:
'---> Sort the Range ListOfMatl
WS.Range("B3:C" & MaxRow).Sort Key1:=WS.Range("C3"), order1:=xlAscending, MatchCase:=False, Header:=xlYes

Open in new window


2) Regarding the filtering of restricted characters for Sheet naming I also included in the Keypress event of TextBox1 (where you input the sheet name) following code that will filter all the unwanted characters, instead of the code you posted earlier in the beginning of this question.
Dim RestrictedChar As String
RestrictedChar = "/\[]*?:"

'---> Prevent Restricted Char from being used is sheet nameing
If InStr(1, RestrictedChar, Chr(KeyAscii), vbTextCompare) <> 0 Then KeyAscii = 0

Open in new window


3) Last but not least I deleted in Sheet MatlTracker all Columns from D to the right as per your latest comments. Also you will note that each time a sheet is created the new item created has a preserve formatting same as previous rows (Borders and if you have more data like previously col D to .... they will all carry the formatting).

Please check this version and let me know your comments.
gowflow
HHEPricingDetail-2-V02.xlsm
0
 

Author Closing Comment

by:RWayneH
Comment Utility
EXCELlent!!! I wish I could give you 5000 pts, instead of 500.  Awesome solution.  Save this one for your next book.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Thank you and glad it is to your satisfaction.
Pls let me know if you need more help in posting a link in here.

Regards
gowflow
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now