• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

Make Active Sheet tab, from a ListBox.

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
RWayneH
Asked:
RWayneH
  • 23
  • 21
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
Could you attach the Excel file instead of the userform object?
0
 
gowflowCommented:
We cannot view your userform you should instead as already pointed out upload the excel file.
gowflow
0
 
RWayneHAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
gowflowCommented:
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
 
RWayneHAuthor Commented:
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
 
RWayneHAuthor Commented:
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
 
gowflowCommented:
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
 
gowflowCommented:
Did you had a chance to read my last post ?
gowflow
0
 
RWayneHAuthor Commented:
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
 
gowflowCommented:
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
 
RWayneHAuthor Commented:
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
 
gowflowCommented:
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
 
RWayneHAuthor Commented:
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
 
gowflowCommented:
hein ??? don't understand your post ! is there a problem ?
gowflow
0
 
RWayneHAuthor Commented:
It actual works nice, I can use the original that get hidden sheets and the new one to get visible.  I like this.
0
 
RWayneHAuthor Commented:
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
 
RWayneHAuthor Commented:
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
 
gowflowCommented:
Well I took your userform2 and there it had Listbox1 and userform1 also had Listbox1 !!!!
gowflow
0
 
RWayneHAuthor Commented:
This is good...   I forgot that each userform can have a ListBox1...   my bad.
0
 
gowflowCommented:
no problem ! pls let me know what you want to do from here ?
gowflow
0
 
RWayneHAuthor Commented:
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
 
RWayneHAuthor Commented:
Let me clarify #2, the Description is not what is on the sheet tab.  It is column C of the hidden sheet.
0
 
gowflowCommented:
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
 
RWayneHAuthor Commented:
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
 
RWayneHAuthor Commented:
Are you requesting I create another question?  Not sure I have done that..  I will give it  a try.
0
 
RWayneHAuthor Commented:
Can we leave this in one question?  #1,2,3,4 all relate to each other.
0
 
gowflowCommented:
ok no problem
but you did not mention about the W and quick search let me see this
gowflow
0
 
gowflowCommented:
2 and 3 are create a new sheet and update the range ListOfMatl  and 4 delete a worksheet ?
pls confirm
gowflow
0
 
RWayneHAuthor Commented:
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
 
gowflowCommented:
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
 
RWayneHAuthor Commented:
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
 
gowflowCommented:
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
 
RWayneHAuthor Commented:
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
 
RWayneHAuthor Commented:
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
 
gowflowCommented:
ok fine let me know.
gowflow
0
 
RWayneHAuthor Commented:
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
 
gowflowCommented:
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
 
RWayneHAuthor Commented:
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
 
gowflowCommented:
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
 
gowflowCommented:
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
 
RWayneHAuthor Commented:
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
 
gowflowCommented:
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
 
gowflowCommented:
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
 
RWayneHAuthor Commented:
EXCELlent!!! I wish I could give you 5000 pts, instead of 500.  Awesome solution.  Save this one for your next book.
0
 
gowflowCommented:
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 23
  • 21
Tackle projects and never again get stuck behind a technical roadblock.
Join Now