PDA

View Full Version : Solved: User Form / List Box Help Me!



ssinghal
02-03-2007, 02:26 AM
I am having all sorts of trouble using a listbox. Here is what I need to do:

1. My listbox named "selecthotels" needs to include dat from cells A1 to A57 on a sheet named "PropList". I have just entered the text so far.

2. I want to select multiple items on the list. For each selected item, I need to copy the selected text to the cell A1 on a sheet named "Month" and then print "Month". A1 populates data on the Month sheet.


This is what I have so far. The list is working with my manual entry, but the cmdOK is not.
==================================================

Sub cmdOK_Click()
Dim s As Range

Unload Me
Application.ScreenUpdating = False


'When the user clicks OK, loop through the names in the list box
For i = 0 To selecthotels.ListCount - 1

'If a sheet name is selected...
If selecthotels.Selected(i) = True Then

'See which which boxes are selected and print the range
Sheets("MONTH").Select
Range("a1").Select
ActiveCell.Value = selecthotels.Selected(i).Value
Set s = Sheets("MONTH")
s.PrintOut

End If

Next
End Sub


=======================================================
Private Sub UserForm_Initialize()


selecthotels.AddItem "Aurora"
selecthotels.AddItem "Austin"
selecthotels.AddItem "Baton Rouge"
selecthotels.AddItem "Birmingham"
selecthotels.AddItem "Charlotte"
selecthotels.AddItem "Chesapeake"
selecthotels.AddItem "Clearlake"
selecthotels.AddItem "Colorado Springs"
selecthotels.AddItem "Corpus Christi"
selecthotels.AddItem "Crestwood Suites"
selecthotels.AddItem "Cumming"
selecthotels.AddItem "Dallas"
selecthotels.AddItem "Douglasville"
selecthotels.AddItem "Flamingo"
selecthotels.AddItem "Ft. Myers"
selecthotels.AddItem "Greensboro"
selecthotels.AddItem "Greensboro2"
selecthotels.AddItem "Greenspoint"
selecthotels.AddItem "Gulfport"
selecthotels.AddItem "Gwinnett"
selecthotels.AddItem "Hattiesburg"
selecthotels.AddItem "High Point"
selecthotels.AddItem "Houston290"
selecthotels.AddItem "Jacksonville"
selecthotels.AddItem "Las Vegas Blvd"
selecthotels.AddItem "Lewisville"
selecthotels.AddItem "Madison"
selecthotels.AddItem "Marietta"
selecthotels.AddItem "Metairie"
selecthotels.AddItem "Murfreesboro"
selecthotels.AddItem "New Orleans"
selecthotels.AddItem "Newport News"
selecthotels.AddItem "Orlando1"
selecthotels.AddItem "Orlando2"
selecthotels.AddItem "Plano"
selecthotels.AddItem "Raleigh"
selecthotels.AddItem "Roswell"
selecthotels.AddItem "Smyrna"
selecthotels.AddItem "Snellville"
selecthotels.AddItem "Stafford"
selecthotels.AddItem "Stockbridge"
selecthotels.AddItem "Sun Suites"
selecthotels.AddItem "Suwanee"
selecthotels.AddItem "Town Center"
selecthotels.AddItem "West Chase"
selecthotels.AddItem "Sleep Inn"
selecthotels.AddItem "Durham"
selecthotels.AddItem "Tulsa"
selecthotels.AddItem "Totals"
selecthotels.AddItem "Arlington Heights, IL"
selecthotels.AddItem "Augusta, GA"
selecthotels.AddItem "Columbia, SC"
selecthotels.AddItem "Little Rock, AR"

End Sub

mdmackillop
02-03-2007, 03:31 AM
Try the attached
Option Explicit

Private Sub UserForm_Initialize()
With Sheets("PropList")
SelectHotels.List = Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Value
End With
End Sub

Private Sub CommandButton1_Click()
Dim i As Long
With SelectHotels
For i = 0 To .ListCount - 1
If .Selected(i) = True Then
Sheets("Month").Cells(1, 1) = .List(i)
Sheets("Month").PrintOut
End If
Next
End With
Unload UserForm1
End Sub

xld
02-03-2007, 04:32 AM
Sub cmdOK_Click()
Dim s As Range

Unload Me
Application.ScreenUpdating = False

'When the user clicks OK, loop through the names in the list box
For i = 0 To selecthotels.ListCount - 1

'If a sheet name is selected...
If selecthotels.Selected(i) = True Then

'See which which boxes are selected and print the range
Worksheets("Sheet3").Range("a1").Value = selecthotels.List(i)
'Set s = Sheets("MONTH")
's.PrintOut

End If

Next
End Sub

ssinghal
02-03-2007, 07:25 AM
Thanks mda,
Works like a charm.