Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Change Of Height
Hi All,
I have a userform that gathers some of my worksheets in the workbook. have a hide & unhide command button that will hide and unhide th worksheets (with the help of the NG). I have been working on an option command button that would make th userforms height larger, show a command button called Print Sheet(s) and change the listbox list style property to fmListStyleOption an fmMultiSelectSingle. Thinking that once the option command button ha been clicked on it would in large the form change the list styl property so the user can click on (checkbox) each sheet that they wan to print then click on the command button Print Sheet(s) to print. The problem I am having is that the list style is only showin fmListStyleOption and I am trying to get it to show the checkboxe instead. I think the form has a mind of its own as well (not really) every time I click on the options button the sheets names disappear on at a time. So, in other words the list box with sheet names slowly get smaller (why). Any help would be appreciated Here is my code that I have been working with: Option Explicit Private Sub cmdExit_Click() Unload Me End Sub ' This is where I am having trouble or better yet causing ' myself a headache Private Sub cmdOption_Click() If cmdOption.Caption = "Options " Then Me.Height = 160.5 cmdOption.Caption = "<< Options" ListBox1.ListStyle = fmMultiSelectSingle + fmListStyleOption Else Me.Height = 197.25 cmdOption.Caption = "Options " ListBox1.ListStyle = fmListStylePlain End If End Sub Private Sub cmdUnhide_Click() Dim L As Long For L = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(L) Then _ Sheets(ListBox1.List(L)).Visible = True Next End Sub Sub cmdHide_Click() Dim L As Long On Error Resume Next For L = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(L) Then _ Sheets(ListBox1.List(L)).Visible = False Next End Sub Private Sub ListBox1_Click() Worksheets(ListBox1.Text).Activate Range("a1").Select End Sub Private Sub UserForm_Initialize() Dim wks As Worksheet For Each wks In Worksheets Select Case wks.Name Case "Parts List", "Sheet List", "All Parts", _ "All Part Numbers", "Enter Data", "Summary", _ "Logo", "HelpSheet" Case Else: Me.ListBox1.AddItem wks.Name End Select Next Me.Height = 160.5 End Su -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Change Of Height
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Change Of Height
The settings for your ListBox1 should read for options and multiples.....
ListBox1.ListStyle = fmListStyleOption ListBox1.MultiSelect = fmMultiSelectMulti or for plain lists and single selection...... ListBox1.ListStyle = fmListStylePlain ListBox1.MultiSelect = fmMultiSelectSingle Cheers Nigel "Rockee052 " wrote in message ... Hi All, I have a userform that gathers some of my worksheets in the workbook. I have a hide & unhide command button that will hide and unhide the worksheets (with the help of the NG). I have been working on an option command button that would make the userforms height larger, show a command button called Print Sheet(s), and change the listbox list style property to fmListStyleOption and fmMultiSelectSingle. Thinking that once the option command button has been clicked on it would in large the form change the list style property so the user can click on (checkbox) each sheet that they want to print then click on the command button Print Sheet(s) to print. The problem I am having is that the list style is only showing fmListStyleOption and I am trying to get it to show the checkboxes instead. I think the form has a mind of its own as well (not really), every time I click on the options button the sheets names disappear one at a time. So, in other words the list box with sheet names slowly gets smaller (why). Any help would be appreciated Here is my code that I have been working with: Option Explicit Private Sub cmdExit_Click() Unload Me End Sub ' This is where I am having trouble or better yet causing ' myself a headache Private Sub cmdOption_Click() If cmdOption.Caption = "Options " Then Me.Height = 160.5 cmdOption.Caption = "<< Options" ListBox1.ListStyle = fmMultiSelectSingle + fmListStyleOption Else Me.Height = 197.25 cmdOption.Caption = "Options " ListBox1.ListStyle = fmListStylePlain End If End Sub Private Sub cmdUnhide_Click() Dim L As Long For L = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(L) Then _ Sheets(ListBox1.List(L)).Visible = True Next End Sub Sub cmdHide_Click() Dim L As Long On Error Resume Next For L = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(L) Then _ Sheets(ListBox1.List(L)).Visible = False Next End Sub Private Sub ListBox1_Click() Worksheets(ListBox1.Text).Activate Range("a1").Select End Sub Private Sub UserForm_Initialize() Dim wks As Worksheet For Each wks In Worksheets Select Case wks.Name Case "Parts List", "Sheet List", "All Parts", _ "All Part Numbers", "Enter Data", "Summary", _ "Logo", "HelpSheet" Case Else: Me.ListBox1.AddItem wks.Name End Select Next Me.Height = 160.5 End Sub --- Message posted from http://www.ExcelForum.com/ ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Change Of Height
Nigel,
Thanks for your reply, that worked great... For my listbox changing th height everytime when I selected the options, I just set the listbo height in the code and it worked... Thank you very much Rocke -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change row' height | New Users to Excel | |||
How do I change the ROW HEIGHT in one column? | Excel Worksheet Functions | |||
how to change one cell height not the row | Excel Worksheet Functions | |||
how to change one cell height not the row | Excel Worksheet Functions | |||
Change height of row | Excel Discussion (Misc queries) |