Pre-select visible sheets in listbox

  • Thread starter Thread starter PFS
  • Start date Start date
P

PFS

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
 
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
 
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
 
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
Click to expand...

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
 
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????
 
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????
Click to expand...


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

cheers

Paul
 
Back
Top