Help with a FORMATTED scrollable list on a form 1

[tmktech]

Hi folks.

I've never been able to make much use of the combo boxes or lists on a form, but I think my latest needs necessitates it (OR DOES IT?? Alternatives??)

I maintain a dynamic (hidden) cell range range of sheets within a workbook. I've got that working fine.

I perform some functions on multiple sheets and I want to give the user the ability to select WHICH sheets are impacted. I want to have a boolean in column 2 of the hidden range that is maintained by the user through a form or other means, but I want it to be a format with, say, a checkbox next to each sheet occurrence that can be selected or de-selected.

If there's an approach that's superior to mine, I'm wide-open to ideas.

THANKS IN ADVANCE!

TMKTECH

 

[xlbo]

Not sure I fully understand what you are after....

You have a list of sheets (that much I get ;-) )

You want to have an indicator that says Y/N as to whether to apply a function / format / bit 'o code etc to the sheets that have the Y ???

where do the tick boxes come into it ??? - would these be to select sheets on the form or in the column next to the sheet names ??

It should be easy enough to have a multi select combobox that is populated with the sheet names and a button to assign a value to the cells next to all the selected sheets from the combobox


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[tmktech]

Geoff,

It looks like you understand my objective. You're final sentence looks like what I'm trying to acheive.

In regards to the "where do the tick boxes come into play...", the answer is BOTH. I'd like to use the form to select which sheets will have the action performed on, but I want to store their selections (hence the column 2)until changed again. I don't want them to have to populate the form each time the routine runs.

So back to your last sentence: It's possible to have a combobox with a corresponding button for each entry?? That's great. Can you show me how? Comboboxes have just not been intuitive to me.

The list (combobox, etc.) would be driven off the dynamic sheet (list) range, with the button values set from column 2 upon display.

 

[xlbo]

aaaah - I get it now. Scratch my last response - I didn't quite grasp it then

See if this works for you

Lets say your range for the sheets is called "myRange"
Add another range name that has the same definition but is 2 columns wide rather than 1 - call this range "lkup"
In the column next to the sheet names, put a mixture of TRUE and FALSE

Insert a userform in the VBE
Add a combobox - call it cbSheetList
set the rowsource as myRange

Add a checkbox - call it chkselection


Doubleclick the combobox on the form - a change event sub will be automatically generated
within there, paste this code:

If WorksheetFunction.VLookup(cbSheetList.Text, [lkup], 2, False) = True Then
chkSelection.Value = True
Else
chkSelection.Value = False
End If

Double click chkselection

paste the following code into the sub that is generated there

Set fCell = [myrange].Find(cbSheetList.Text, LookIn:=xlValues, lookat:=xlWhole)
fCell.Offset(0, 1).Value = chkSelection.Value


With the sheet that holds the sheet names visible (in excel), in the VBE, make sure the form is selected and press the play button to show the form

You will be able to select a sheet from the combobox - the tickbox will either be selected or not - depending on the info in the column next to the sheet names - when you tick / untick it - the ref column will update

Hope this helps

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[tmktech]

Geoff,

Let me give it a try and I'll get back to you.

 

[tmktech]

Hi Geoff,

First of, thanks for the time already spent on this.

Well, I followed your instructions and sure enough it worked. But it's not the effect I was going for. Hopefully we can build on this to get to where I want.

I was looking to have a list (combox?) populated with all of the sheets (much like when you click on the combobox arrow) with the corresponding boolean next to each cell.

[Or alternatively, colored coded rows, based on the boolean]

The user can then scroll up and down, turning each sheet (or the corresonding boolean) on or off. A double-click on the sheet within the list (combo box) is fine, but I've never been able to do row-specific formatting).

Thanks again in advance!!!

TMKTECH

 

[Zathras]

Pardon me for jumping in here, but you may be making this more complicated than it needs to be.

Here is an approach that doesn't depend on a list in a hidden sheet:

1. Drop a list box and command button on a form.

2. Set two properties for the list box:
ListStyle =[blue]

 1 - fmListStyleOption

[/color]
MultiSelect =[blue]

 1 - fmMultiSelectMulti

[/color]

3. Put this code in the form's code page:[blue]

  Private Sub UserForm_Activate()
  Dim wks As Worksheet
    For Each wks In Sheets
      ListBox1.AddItem (wks.Name)
    Next wks
  End Sub

  Private Sub CommandButton1_Click()
    Me.Hide
  End Sub

[/color]
4. Put this code in a code module:[blue]

Sub Test()
Dim i As Integer
  UserForm1.Show
  With UserForm1.ListBox1
    For i = 0 To .ListCount - 1
      If .Selected(i) Then
        Call MyMacro(.List(i))
      End If
    Next i
  End With
End Sub

Sub MyMacro(ASheetName As String)
  MsgBox ASheetName & " was selected... Processing..."
End Sub

[/color]
Run the Test macro to see what happens.

You can replace the code in MyMacro with whatever you need to execute. (The variable ASheetName will contain the name of the sheet to process.) If there are sheets you don't want listed, you can put in an IF statement to test for the names of specific sheets not to add. If you want the sheet names sorted, that can be dealt with too, with a bit more code.

 

[tmktech]

Hi Zathras:

Let me try this out, but it doesn't sound like it's achieving my objective, which is to show a complete list of the available sheets AND whether they are selected or not. With the ability to turn sheets "on" or "off" while scrolling up & down.

I'm gonna go try your code, but let me know in the meantime if my further clarification ehlps.

Thanks to you too!

 

[xlbo]

Don't think you're gonna get that functionality. AFAIK, there is no way to have an "on/off" switch actually IN the combobox - you could have a multicolumn combo to show the current status but you would not have any functionality to change it within the combobox - this will need to be achieved via a secondary switch. With my method, try adding a textbox called "tbAll" and set it's multiline property to true

In the userform initialise event, paste this:

Dim CurrSel As String, Multiplier As Integer
Const stdHeight = 9
Multiplier = 1
For Each c In [myRange]
If c.Offset(0, 1) = True Then
Multiplier = Multiplier + 1
If c.Row <> 1 Then
CurrSel = CurrSel & vbCrLf & c.Text
Else
CurrSel = c.Text
End If
End If
Next c
tbAll.Height = stdHeight * Multiplier
tbAll.Text = CurrSel

Replace the current code in the chkSelection checkbox click event with this:

Dim CurrSel As String, Multiplier As Integer, fCell As Range
Const stdHeight = 9
Multiplier = 1
Set fCell = [myRange].Find(cbSheetList.Text, LookIn:=xlValues, lookat:=xlWhole)
fCell.Offset(0, 1).Value = chkSelection.Value
For Each c In [myRange]
If c.Offset(0, 1) = True Then
Multiplier = Multiplier + 1
If c.Row <> 1 Then
CurrSel = CurrSel & vbCrLf & c.Text
Else
CurrSel = c.Text
End If
End If
Next c
tbAll.Height = stdHeight * Multiplier
tbAll.Text = CurrSel


You may be pleasantly surprised



Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[tmktech]

Hi Geoff.

I sure will. I'll be on the road for 2 days, so giv me til Thursday to gt back to you.

Thanks again for your investment in this.

I APPRECIATE IT!

Tmktech

 

[tmktech]

Geoff,

That's a great create solution! Here's a STAR.

I actually modified your code slightly (see below) to eliminate the checkbox. Therefore selection of an entry in the combobox becomes is the toggle itself (i.e. if on, turns, off and visa-versa).


public update_complete as boolean

Private Sub cbSheetList_Change()
If cbSheetList.Text = &quot;&quot; _
Or update_complete Then
update_complete = False
Exit Sub
End If

Dim fCell As Range
Set fCell = [myRange].Find(cbSheetList.Text, LookIn:=xlValues, lookat:=xlWhole)
fCell.Offset(0, 1).Value = IIf(fCell.Offset(0, 1).Value = True, False, True)
Build_tbAll

update_complete = True
cbSheetList.Text = &quot;&quot;

Me.Repaint

End Sub


My only &quot;sloppy&quot; code is my solution for keeping the change event from looping within itself (and setting the toggle back to where it was) when I reset the cbSheetList.Text to &quot;&quot;.

I do this so that there is never a &quot;current&quot; selection.

If you've got a better way keep this from looping on itself, I love to hear it.


THANKS AGAIN!

 

[tmktech]

Well, Geoff.

After you got me started out in the right direction, I've actually taken it a step further and have achieved exactly what I was looking for, perhaps better!

Here's my approach / code:

On my form, all I have is a single LISTbox and a commandbutton. I still employ your code to load the listbox, but because of the selection property, I can HIGHLIGHT the values selected (TRUE) and then update the excel list based on the selected values. Also, with the (listbox) multiline property set to SelectExtended, I get the power of the CRTL and SHIFT for multiple selections.

It's quick slick and the code is very tight.

Check it out.

Private Sub UserForm_Initialize()

Const stdHeight = 12
For Each c In [myRange]
lbAll.AddItem c.Value
lbAll.Selected(lbAll.ListCount - 1) = c.Offset(0, 1)
Next c

'make sure listbox isn't longer than the form
lbAll.Height = WorksheetFunction.Min(stdHeight * lbAll.ListCount, Me.Height)
DoEvents 'doing this to get the height to take

End Sub
Private Sub commandbutton1_Click()

Dim fCell As Range

For i = 0 To lbAll.ListCount - 1

Set fCell = [myRange].Find(lbAll.List(i), LookIn:=xlValues, lookat:=xlWhole)
fCell.Offset(0, 1).Value = IIf(lbAll.Selected(i) = True, True, False)

Next i

Me.Repaint

End Sub



Thanks again to you and Zathras for your efforts.

TMKTECH

 

[xlbo]

Very nice and I applaud your efforts to look further and tighten up the code. Glad you got where you wanted to be

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244