Solved

ExtendedSheetTab Descriptions

Posted on 2016-09-02
26
30 Views
Last Modified: 2016-09-09
Hello
I started on this a while back but never finished it.  It opens a userform (from the ReadMe tab) that lists sheet tabs and allows for create/delete.  However it lists way more than what are actually in the workbook.

I need some assistance with, getting it to list the actual tabs that are there, and based on a second sheet pull in the extended name that corresponds with that tab using some sort of cross reference sheet when created/deleted.  Not sure what the Clear button was supposed to do, and the is not a Cancel button, only the red X.  If I recall it would only work if the sheet tab was created in the userform, if it was created outside the userform it would not have a extended description.

I am terrible with userforms, but the need for this has resurfaced.  Any help would be appreciated.  Thanks.
ExtendedSheetTabDescription.xlsm
0
Comment
Question by:RWayneH
  • 14
  • 12
26 Comments
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41782000
Have you seen how many name errors your workbook contains?Name errors
Your ListBox is loaded from a Named Range, but you need code to add the name of undividula Tabs I think.

I have removed the errors and added code to load the sheet names. Clicking a sheet will open that sheet.

I have added to remove RowSource, ColumnCount & ColumnWidths fortm the UserForm

There's a lot of code needs checking. Let me know a little more about what you want, e.g. what is the exyended description?
0
 

Author Comment

by:RWayneH
ID: 41785993
I did not see the attached s-sheet with the edits?
0
 

Author Comment

by:RWayneH
ID: 41786004
Please disregard the other code issues...  I would like the sheet feature to work.  Show the proper Named Range and populate the list of hidden and non-hidden sheet with there extended name with the goto option for the sheet.  The ListBox needs to display correctly and be managed as adds and deletes happen.

I assign this to a hotkey, so each time it runs, it can update appropriately based on current state of the sheets.  Hope this makes sense.
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41786516
Not sure what's happened there. I'll attach again.

Have a look at Pick a Sheet Tab userform, see if that's doing what you want. Simply click a sheet in the ListBox and it will be selected.
ExtendedSheetTabDescription.xlsm
0
 

Author Comment

by:RWayneH
ID: 41787672
Couple things.  I believe your reference to Pick a Sheet Tab was UserForm2. (UserForm1 is a previous attempt at just sheet names, need longer descriptive names too).  When I see the list (or add one to the list) the extended sheet tab name does not appear.  When I select one from the list, I should be able to delete a sheet, Currently when I select a sheet in the list, the Delete button does not activate or work.  Currently when there is value in the Sheet To Create and Sheet Description the Create Sheet button activates.

Need to add a Cancel button to userform.  The original sample workbook showed the extended description the new sample is not.
0
 

Author Comment

by:RWayneH
ID: 41787686
Oh too,  After a sheet tab add, it returns with a larger list?  It looks like it is adding an item to the MatkTracker sheet at the bottom.  So deleting a selected row should delete from there too?  So the current rows in MatlTracker should match that of the actual sheet tabs..  Plan is to hide that sheet when workbook is done.
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41788277
Your buttons to delete a sheet and create a sheet are disabled.

I don't really know what you mean by longer descriptive names.

Why are you using this list - Range("ListOfMatl") in the ListBox after creating the sheet? What is the purpose of that Sheet?

Take a look at UserForm2.

The ListBox displays sheets in the workbook.

When TextBox1 & " are completed the Create button is enabled. I have added a UDF to test if the sheet name is unique

When a sheet is selected in the ListBox it is selected and the delete button is enabled.
ExtendedSheetTabDescription--1-.xlsm
0
 

Author Comment

by:RWayneH
ID: 41788657
The ListOfMatl is required for the Extended Sheet names.  Try adding a sheet and you will see the create button activate.  If we are writing out the new sheet name and adding an Extended name, we have to write them somewhere right?
0
 

Author Comment

by:RWayneH
ID: 41788662
The Extended name is in that sheet too.  After adding a sheet look at the bottom of the list and you will find your add in the list.  Refer to the original sample file.
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41789042
Have you checked the file that I attached?
0
 

Author Comment

by:RWayneH
ID: 41789384
Ok I did check the attached, but when I execute it I, the extended sheet tab name (description) does not show with the actual sheet tab name.  They should both be listed in the list box.  That is the point of the userform.  Also would like a Cancel button instead of having to use the red X in the upper right.

When I hide a sheet it still shows in the listbox..  for this I do not want those to show, only non hidden sheets.  Not sure if a toggle for the listbox is possible to show with/without, in the userform?

Not sure if this is possible, but if there is a value in the two required to create...  could I just press Enter?

Other than that it look pretty good.
0
 

Author Comment

by:RWayneH
ID: 41789400
Just found another issue.  When I delete a selected sheet, it still shows in the listbox?  The delete should remove it from the listbox too.  Plan is to hide the MatlTracker sheet so the sheet that manages the listbox is transparent to user.
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41789875
I did ask what the extended sheet names are used for . Have you studied the code that I wrote because some of these issues can be amended. I'm not sure whether I am helping you to learn or doing the whole project!
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41789879
Just checked, deleting a sheet removes the name from the list.

What is the extended sheet name, i.e what would you enter into TextBox 2?

Your sheet names currently bear no resemblance to the list MatlTracker.
0
 

Author Comment

by:RWayneH
ID: 41790106
Extended Sheet Name:  It is when I would like to use a smaller names (in my case a 8 digit matl number) that relate to a much larger descriptive name that is the matl number description, which can be as high as 150+ chars.  By having both displayed, Users can identify the sheet by either the matk or the description.  Some user remember that the matl is something like 122-50xx, so adding a field in the userform that would allow the user to start typing the matl number (sheet) and watch the listbox chg as it filters the listbox, they can then read the description as they get close.

Hope this explains the Extended Sheet Name or maybe a betting name would be Sheet Tab Description?

I like that the initial population of the listbox is the current actual sheets, that is good.  If they are created in Excel not using this feature, the description will be blank.  However if this feature is used to create a sheet, it then will have a description in the listbox.  That is why it is written to the MatlTracker tab, so the descriptions can be added to the listbox.  I think the issue is keeping them in sync, that is why the MatlTracker is planned to be a hidden sheet that does not show in the listbox.

Any further questions?  Happy to reply or expand on.  Thanks.
0
 

Author Comment

by:RWayneH
ID: 41790119
(additional explanation)
Another thing that may help is reviewing the MatlTracker sheet and row 180.... 181 182 etc.  When you use Create it adds to the list, then that is where the listbox gets the extended description.  May have to take the existing sheets list, and match it to the one on MatlTracker before displaying it in the listbox?  It is keeping the actual sheet tab name, in sync with the place that the extended names are stored...  this is the issue I am having with the workbook. (among a few other minor issues).
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41790779
You cannot filter a ListBox as you type.

I think this does what you want
ExtendedSheetTabDescription--1-.xlsm
0
 

Author Comment

by:RWayneH
ID: 41791223
Only thing that I am a little confused on is the Create option.  Does selecting something from the listbox have to populate the Create fields?  Also in the Create.  After fields are filled in Create button is not active??  The only way I see to activate it is to tab off it then go back and click the Create button.

Can the Create be active on initialize? so the tab does not goto the Clear?  There is something odd in the create process that forces an odd sequence of steps to complete?

I marked up the workbook a bit.  Hide a sheet and remove unused sheet tabs. (In listbox that did not exist).  Yes it works nice, just want to see if there is a way to clean up the Create process?
ExtendedSheetTabDescription.xlsm
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41791413
You don't need to have the names in the create fields. It just seemed a useful feature, maybe you might want to correct a name.

The Create Button is enabled when the user has completed the TextBoxes and tabs out. If it is enabled when the userform is activated then there is a chance that the user will click it before the textboxes are complete. I've changed the code so that the button will be enabled as soon as both textboxes are completed
ExtendedSheetTabDescription--2-.xlsm
0
 

Author Comment

by:RWayneH
ID: 41791517
Excellent!!  Nailed it.  Very nice solution.  Would the process blow up if the listbox got sorted alphabetically before display in the listbox?  That would be to last item and we are complete...    Thanks for all your help with this.  It disappointed me when the listbox could not be searched based on typing in the beginning few chars and it would goto that sheet, but feel that if they are in some sort of order, the right sheet can still be found rather quickly when there are large amounts of sheets in a book.
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41791524
Just sort the list. I'll amend the code.

I can filter a ListBox but it involves working with the actual data and filtering that to a temporary table. It might not be worth the effort though. What would you sort by? The actual Sheet name?
0
 
LVL 17

Accepted Solution

by:
Roy_Cox earned 500 total points
ID: 41791564
You have changed the data source without amending the code. It now refers to the wrong range and the CurrentRegion doesn't work correctly. If you don't understand the code then ask before making any changes.

I've corrected this and the sheet names are automatically sorted
ExtendedSheetTabDescription--2-.xlsm
0
 

Author Comment

by:RWayneH
ID: 41791565
Yes the actual sheet name.  Which I was thinking may have to be its own field.  if we have sheet name that start with 110- and some with 210-  510-   etc,  the sort would take them to that area in the list.  Just trying to make the search for a specific sheet easier.
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41791579
See my previous post with example
0
 

Author Closing Comment

by:RWayneH
ID: 41791763
powered thru it... AWESOME!!
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41792268
Glad to help.

You need to remove the unnecessary UserForm and I would suggest that you check your other macros, many of them seem to be unnecessary
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

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

12 Experts available now in Live!

Get 1:1 Help Now