Pre-select visible sheets in listbox

Hi All,

I have a user form which has a listbox which lists all the sheets in
the workbook.

It allows the user to pick from the list which sheets they would like
to see, and hide the unselected ones.

What I would like the listbox to do when the form opens, is to pre-
select the sheets that are already visible, so that the user can just
modify the selction instead of starting from scratch.

Any help that people can give would be appreciated

cheers
Paul
0
PFS
11/29/2009 11:58:18 PM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
925 Views

Similar Articles

[PageSpeed] 33

As a user, I would think that it would make more sense to ask me to select the
sheets to hide--but that doesn't change how the listbox would be initialized.

Option Explicit
Private Sub CommandButton1_Click()
    Unload Me
End Sub
Private Sub CommandButton2_Click()

    Dim iCtr As Long
    
    With Me.ListBox1
        .MultiSelect = fmMultiSelectMulti
        For iCtr = 0 To .ListCount - 1
            If .Selected(iCtr) Then
                On Error Resume Next
                'xlsheetveryhidden ????
                ActiveWorkbook.Sheets(.List(iCtr)).Visible = xlSheetHidden 
                If Err.Number <> 0 Then
                    Err.Clear
                    MsgBox "Error while trying to hide: " & .List(iCtr)
                End If
                On Error GoTo 0
            End If
        Next iCtr
    End With

End Sub
Private Sub UserForm_Initialize()

    Dim sh As Object 'could be any type of sheet
    
    For Each sh In ActiveWorkbook.Sheets
        If sh.Visible = xlSheetVisible Then
            Me.ListBox1.AddItem sh.Name
        End If
    Next sh

End Sub


PFS wrote:
> 
> Hi All,
> 
> I have a user form which has a listbox which lists all the sheets in
> the workbook.
> 
> It allows the user to pick from the list which sheets they would like
> to see, and hide the unselected ones.
> 
> What I would like the listbox to do when the form opens, is to pre-
> select the sheets that are already visible, so that the user can just
> modify the selction instead of starting from scratch.
> 
> Any help that people can give would be appreciated
> 
> cheers
> Paul

-- 

Dave Peterson
0
Dave
11/30/2009 12:36:42 AM
Thanks Dave,

I should have said, my listbox lists "all" sheets in the workbook
(visible and hidden) and the chosen selection is likely to be much
smaller than the "hidden" list.

The list is to allow a farmer to select what crops he has on his
farm.  We will have around 30-40 different crop templates available to
choose from, but a farmer is only likely to choose up to 10

When he goes to add a new crop to his farm or stops growing a crop, I
would like his existing crops (sheets) to be already selected in the
listbox, so that he can modify his selection easier, rather than
having to select everything that he already grows from scratch each
time (because in my code for the action button, what is selected in
the listbox is made visible, and the rest are hidden)

Hope this makes sense. (and my apologies if the above does this
already, I am relatively new to VBA and forms)

cheers
Paul
0
PFS
11/30/2009 2:52:38 AM
On Nov 30, 12:52=A0pm, PFS <pfstew...@gmail.com> wrote:
> Thanks Dave,
>
> I should have said, my listbox lists "all" sheets in the workbook
> (visible and hidden) and the chosen selection is likely to be much
> smaller than the "hidden" list.
>
> The list is to allow a farmer to select what crops he has on his
> farm. =A0We will have around 30-40 different crop templates available to
> choose from, but a farmer is only likely to choose up to 10
>
> When he goes to add a new crop to his farm or stops growing a crop, I
> would like his existing crops (sheets) to be already selected in the
> listbox, so that he can modify his selection easier, rather than
> having to select everything that he already grows from scratch each
> time (because in my code for the action button, what is selected in
> the listbox is made visible, and the rest are hidden)
>
> Hope this makes sense. (and my apologies if the above does this
> already, I am relatively new to VBA and forms)
>
> cheers
> Paul

In short I need some code to do....

If a sheet is visible, have it pre-selected in the listbox as the form
opens

cheers
Paul
0
PFS
11/30/2009 2:55:36 AM
You could use something like:

Private Sub UserForm_Initialize()

    Dim sh As Object 'could be any type of sheet
    
    With Me.ListBox1
        .MultiSelect = fmMultiSelectMulti
        For Each sh In ActiveWorkbook.Sheets
            .AddItem sh.Name
            If sh.Visible = xlSheetVisible Then
                .Selected(.ListCount - 1) = True
            End If
        Next sh
    End With
End Sub

I'm not quite sure what you're doing, but it may be easier to create two
listboxes and have some buttons that "move" the entries from one listbox to the
second and removes it from the first.

It may be easier for the user to see what's been selected instead of scrolling a
long listbox????

PFS wrote:
> 
> On Nov 30, 12:52 pm, PFS <pfstew...@gmail.com> wrote:
> > Thanks Dave,
> >
> > I should have said, my listbox lists "all" sheets in the workbook
> > (visible and hidden) and the chosen selection is likely to be much
> > smaller than the "hidden" list.
> >
> > The list is to allow a farmer to select what crops he has on his
> > farm.  We will have around 30-40 different crop templates available to
> > choose from, but a farmer is only likely to choose up to 10
> >
> > When he goes to add a new crop to his farm or stops growing a crop, I
> > would like his existing crops (sheets) to be already selected in the
> > listbox, so that he can modify his selection easier, rather than
> > having to select everything that he already grows from scratch each
> > time (because in my code for the action button, what is selected in
> > the listbox is made visible, and the rest are hidden)
> >
> > Hope this makes sense. (and my apologies if the above does this
> > already, I am relatively new to VBA and forms)
> >
> > cheers
> > Paul
> 
> In short I need some code to do....
> 
> If a sheet is visible, have it pre-selected in the listbox as the form
> opens
> 
> cheers
> Paul

-- 

Dave Peterson
0
Dave
11/30/2009 1:29:27 PM
On Nov 30, 11:29=A0pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:

> I'm not quite sure what you're doing, but it may be easier to create two
> listboxes and have some buttons that "move" the entries from one listbox =
to the
> second and removes it from the first.
>
> It may be easier for the user to see what's been selected instead of scro=
lling a
> long listbox????


Thanks this is a much better idea, have implemented this in my
workbook

cheers

Paul
0
PFS
11/30/2009 11:37:00 PM
Reply:

Similar Artilces:

Compare Sheets #2
I'm going insane I'm trying to compare two sheets using Walkenbach's approach but for some reason when I use it...certain values don't show differences. Example: Sheet 1 (Range Name is Grid) has 18000 on Cell A1 Sheet 2 (Range Name is OldGrid) has 18000 on Cell A2 So if I change Cell A1 on Sheet 1 to 1 it should show me change but no...it just does nothing. However if I type 133...it shows change. Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=COUNTIF(OldGrid,A1)=0" Selection.FormatConditions(1).Interior.ColorIndex = 40 Help really appreciate...

Issues with Select statement doing 'simple' math
Hello, I have tried numerous solutions to what should be a simple issue, but I have had no success. I am trying to gather some simple math values from a single table, and I cannot create the proper select statement. The below query works and provides the correct values: SELECT TOP (100) PERCENT MAX(DistCode) AS DistCode, MAX(DistName) AS DistName, MAX(CustServRep) AS CustServRep, COUNT(DistCode) AS AllOrders FROM dbo.[TABLE OrderHistory] AS OH WHERE (M2K_Timestamp >= CONVERT(DATETIME, '2010-03-01 00:00:00', 102)) AND (M2K_Timestamp < CONVERT(DATETIME, '...

how do I set up spread sheet for demographical data to get graph.
I need to set up a spread sheet to be able to get age, sex,service usage numbers, site utilisation, presenting issue e.g., drug and alcohol, region, percentage of use base on population e.g, 80 staff DoHi, Do you want to show these results graphically, or in the form of some sort of output table? Dave url:http://www.ureader.com/msg/10355820.aspx How are you intending to get the data or enter the data into this sheet? How many worksheets or tables will you need? What is the final display options: Examples, Reports, Charts, Pivot = table? And lastly who are the end users, and how do they...

Why do sheet tabs keep disappearing? Box in Tools is checked.
When I open a new or existing Excel file, the sheet tabs at the bottom may or may not show up. I have repeatedly gone to Tools, Options, View and made sure the sheet tabs box is checked, but still no tabs. How do I get them back? I can't get from one page of a workbook to another. This is in Offfice 2003. Hi Arlie, Try Tools/Options/General In the Sheets in new workbook box, check and see how many sheets are set to appear when opening a new workbook. Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://w...

How do you select a cell with an "absolute" address with a cursor?
When you try to make an absolute reference to a cell, you place "$" in front of the column and/or row. But can you do it more easily by, for example, selecting a cell with your mouse while pressing CTRL (obviously this does not work) when you select a cell? Thank you. New Daddy, select the cell press F2 then F4 to toggle absolute -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "New Daddy" <newlywed2@daum.net>...

Timestamp a sheet #3
That works good. Thanks alot -- eluehman ----------------------------------------------------------------------- eluehmann's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1309 View this thread: http://www.excelforum.com/showthread.php?threadid=26418 Your welcome, thanks for the feedback -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 2003 ** remove news from my email address to reply by email ** "elueh...

How do I delete selected cell when using check box
I have data in rows A17:J500 with check boxes in rows J17:J500 By activating a macro in A10 I want to clear contents of selecte rows. Thank you -- Message posted from http://www.ExcelForum.com Use Range("A17:J500").ClearContents How do you activate the macro in A10, by selecting A10? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$10" Then Application.DisplayAlerts = False Range("A17:J500").ClearContents Application.DisplayAlerts = True End If End Sub -- No private emails please, for everyone'...

Workbook there but not visible! Help!
I was working on some VBA code for an excel application I am writing. I switched between the VBE and the spreadsheet and all of a sudden my workbook is no longer visible! In the VBE project explorer window it shows my project and my code but in the excel window...no workbook! Nothing! Ran a test procedure calling IsAddin to see if somehow I accidentally clicked a button/box telling Excel to make this file an addin but it returned false. I did close the file and re-open it. Any ideas? Maybe it's just off the visible screen: Window|Arrange|tiled (and resize manually) or maybe y...

select case
Hi I have various cells on a worksheet in different rows that is linked to a vba case selection, whereby rows are hidden or not based on the drop down selection in those cells. The problem that I have is that the user has the option to insert rows in between these cells which will have the effect that the wrong row ranges will now be hidden or not as the rows now move downwards depending on how many rows are inserted. Is there a way of ensuring that the range selection as per the vba moves downwards as and when rows are being inserted? Hein As ALWAYS, post YOUR code for com...

DLookUp or SELECT
I have a table of EMPLOYESS (tblEmployees). Fields are EmployeeID; FirstName; LastName. I have another table of ACTIVITIES (tblActivities). One of the fileds in the tblActivities table is PersonID which contains the number of the EmployeeID in the tblEmployees table. I want the resulting "match" between the [tblActivities.PersonID] and [tblEmployees.EmployeeID to populate a Text Box with the results of this "equal compare" AND INCLUDE the FirstName and LastName fields. I am lost as to how to do this. Create a query based on TblEmployees and TblActivit...

Add text to a field based on a combo box selection
I'm making a form that contains a dropdown field (dropdown1) containing 5 possible selections. Based upon what the user selects, I want to fill a second field (text3) with a corresponding value. For instance: If dropdown1="Maintenance" then enter "Jack Doe" text3 If dropdown1="Safety" then enter "Tom Collins" text 3 etc. Is there a way to write a 'if then else' formula in a 'calculate' form field? And if so what would the syntax be? Or is there a better (easier, non-programatic) way to accomplish this? I'm not...

Collating entries from multiple sheets
Dear Experts, I have 28 worksheets (drug classes) each with a column O in which drug names will appear if they meet certain criteria. I would like to list these drugs, from the 28 worksheets on one page, as a summary. I want excel to look down column O, for about 100 rows, and collect any occurrences of drug names. In 100 rows drug names may appear 20 times (they will all be unique), the other cells being blank. Can I collect these occurrences on one sheet for summary purposes? regards Martina This should give you some ideas: http://www.rondebruin.nl/copy2.htm Also: ...

Add comments to your excel sheet using SpotNote
SpotNote 3.1 is a commenting tool that allows the user to post electronic notes directly to the documents they are editing. Cut out secondary documentation and emails to describe what you changed in a document for a team member or co-worker. SpotNote also Merges IM with E-Sticky Notes BCI announces the addition of SpotLAN to its highly rated electronic note software, SpotNote´┐Ż 3.1. SpotLAN allows the business user to send custom sticky notes instantly across their corporate LAN environment to another computer's desktop. SpotLAN solves the problem of moving from your desk or making a p...

Using Function to call Woorbook Sheet
I would like to use WEEKDAY() to call a particular Sheet in a Wookbook. The Sheets are called Sunday - Saturday and I have an IF statement that needs to check for particular information on that sheet depending on what day it is. Have it currently functioning but it is taking up way to much space. I am unable to update my code with other functions that I need it to calculate until I can figure this part out. Code =IF(OR(AND((TEXT(TODAY(),"dddd")="Monday"),(HLOOKUP(MOD(NOW(),1),Monday!$B$1:$BA$40,2))>0),AND((TEXT(TODAY(),"dddd")="Tuesday"),(HLOOKU...

Combining multiple spread sheets into one
I have individual week spread sheets needing to be combined into one form for year report. This is weekly contributor reporting that needs to be an individual reporting for year end report. Is there a simple way to accomplish this? Hi flaschman Try my add-in http://www.rondebruin.nl/merge.htm Or see the links to the VBA code examples -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "flaschman" <flaschman@discussions.microsoft.com> wrote in message news:A9F6BC95-9CC9-405A-BEF6-38D056BDC1DF@microsoft.com... >I have individual week spread...

editing multiple sheets at once
I was cruising along editing multiple sheets at the same time (by selecting them all - aka grouping) and everything was working fine and then suddenly it stopped. All that happens now is that despite several sheets being selected the only sheet that gets any editing done is the one in view. Any suggestions? What type of editing are you attempting? Not all features are available with groupd sheets. You cannot group edit if one if the sheets is protected. Do you get any error message? Some print setup features are not possible. You cannot sort or filter on grouped sheets. Gord Dibb...

Transfer data from sheet to sheet
I'm currently working on a quotation and I have 3 seperate worksheets I'm working with. Sheet 1 has all the model name, elevation, part description, quantity, cost per piece and total. This worksheet is for internal use only. Sheet 2 has all the products and prices listed. (Probably has no purpose for my question) Now Sheet 3 is where I am trying to pull only certain data from Sheet 1. What I am trying to do is transfer only the columns labeled "model name", "elevation" and "total" Obviously the easy way to do it is to just cut and paste but since...

document doesn't auto display when selected
When I click on an existing excel document it doesn't automatically open. I have to click on something within Excel for it to display. Does anyone know what setting needs to be changed so it will open at startup? -- thx ...

Multiple Sheet Print
Please advise how i can print data onto one sheet that is currently on separate worksheets., without pasting one set of data into the others worksheet? Thank You Hardip You could insert a new sheet. Highlight the data you want. On the new sheet, click Edit> Paste Special > Paste link. "Red Devil" wrote: > Please advise how i can print data onto one sheet that is currently on > separate worksheets., without pasting one set of data into the others > worksheet? > > Thank You > > Hardip ...

Macro to match 2 column values in different sheets
Hi, I need a macro which compares the values in colA and colC of sheet 1 to column D and column E of sheet 2, if both the value match, then it copies the contents from sheet2 col F,G and H into sheet1 colC,D and F. I tried the code below but I got an error message saying:Subscript out of range Please advise if any modifications required in the following code: Thanks Sub CompareThis() For iRow = 1 to 1000 If Sheets("Sheet1").cells(iRow, 1).Value = Sheets("Sheet2").cells (iRow, 4).Value And _ Sheets("Sheet1").cells(iRow, 3).Value = She...

? Make entire sheet of same-name labels using mailmerge
My art organization would like to make each member their own 'custom' sheet of artwork identification labels as a thank you gift. I am setting the project up in Publisher 2003, using Avery 5163 labels (10 labels per sheet). I have everything working great (mail-merging from an Excel file), but no matter what I have tried, I can't seem to make 'one' name print "10" times on one page of labels. I am sure this can be done somehow, because several times a year I receive personalized address labels from various organizations asking for donations. Any help is ...

hyperlinks
I asked a question earlier, and the answer was to make a macro This explains what you need to do: http://www.techonthenet.com/excel/macros/delete_hl.php Basically you have to create a macro to remove all hyperlinks at once. The link provides detailed instructions on how to do it. If you get stuck just post another question and someone can walk you through it. Answer: You will need to create a macro to delete the hyperlink addresses in your Excel sheet. Open your Excel spreadsheet that you wish to remove the hyperlinks from. Press <ALT>-F11 to go to the Visual Basic editor. Cre...

Item not selected by default when changing folders
It used to be that I would change folders and the first item (contact, e-mail, whatever) would be selected/highlighted. I must have changed something though because now when I change folders, no item is selected. This is a real pain when I try to do a quick lookup of a contact (where you just type the first few characters), because I have to use my mouse to first highlight one of the contacts, and then start typing. Any ideas? Thanks. ...

macro to include "user select"
Col A is sequential mumber list 1 - 90. (61 currently used - allowed 90 for "expansion"). Col B is a list of names, with associated data in 7 cols C - I (truncated layout below). Col B1:B90 is rangenamed "names" (for function F5 "goto" purpose) I want to macro "delete" function (to user-select any one of the 90 names), CLEAR the cells B - I in that row, then "data/sort" the entire range to remove the blank. Got most of it by "recording" but came unstuck with the "user select". A B C D E F G H I 1 ale...

Listbox selected line mostly just below bottom of window
I think I'm just missing a call of some sort. I have a listbox. Then I sort the lines, select the line I want and redisplay the listbox using: sendmessage( g_TagBrowse_HDlg, LB_SETCURSEL, C_LINE-1, 0); RedrawWindow( window, 0, 0, rdw_Invalidate ); But sometimes my selected line is mostly just below the bottom of the window. (see image http://members.cox.net/deleyd2/misc/listbox.gif ) I'm relatively new to this so I assume the problem is I just need to make another call of some sort to get the line fully in view? There's something very, very, very wrong with this code. ...