Re: Retasin Selected Irem from Listbox on Userform
Hi....I am using a Userform to enter data into a worksheet labelled "Master". The Listbox that I am having problems with is "ProgramName" . The listbox items are loaded and displayed and when an item from list is selected, a popup window indicates the item that was selected. Up to this point everything is working fine. Its from this point onward where I am having the problem where I would like the selected item to be retained in the listbox and be sent to the worksheet labelled "Master" in column B when the command button Submit is clicked to populate the worksheet with all the entries on the Userform. Any suggestion what I need to do...thanks.
Dim currentrow As Long
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Master")
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
With ws
ws.Cells(iRow, 1).Value = Me.txtCurTrack.Value
ws.Cells(iRow, 4).Value = Me.txtMonYear.Value
ws.Cells(iRow, 5).Value = Me.txtBriefSyn.Value
ws.Cells(iRow, 7).Value = Me.txtTypStatus.Value
ws.Cells(iRow, 8).Value = Me.txtOCStatus.Value
ws.Cells(iRow, 10).Value = Me.txtSentDate.Value
End With
'clear the data
Me.txtCurTrack.Value = ""
Me.txtMonYear.Value = ""
Me.txtBriefSyn.Value = ""
Me.txtTypStatus.Value = ""
Me.txtOCStatus.Value = ""
Me.txtSentDate.Value = ""
Me.txtCurTrack.SetFocus
End Sub
Private Sub cmdAddProgram_Click()
Dim Msg As String
Dim i As Integer
Dim sSel, iCt
Msg = "You selected" & ProgramNames
For i = 0 To ProgramName.ListCount - 1
If ProgramName.Selected(i) Then
Msg = Msg & ProgramName.List(i) & ProgramNames
End If
Next i
MsgBox Msg
End Sub
Private Sub cmdCheckTrackNumbers_Click()
frmTrackNumbers.Show
End Sub
Private Sub cmdClearData_Click()
Me.txtCurTrack.Value = ""
Me.txtMonYear.Value = ""
Me.txtBriefSyn.Value = ""
Me.txtTypStatus.Value = ""
Me.txtOCStatus.Value = ""
Me.txtSentDate.Value = ""
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub cmdFindNext_Click()
Dim ws As Worksheet
Set ws = Worksheets("Master")
Dim lastrow
lastrow = Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row
myDate = Me.txtCurTrack.Text
For currentrow = 2 To lastrow
If ws.Cells(currentrow, 1).Text = myDate Then
Me.txtCurTrack.Text = ws.Cells(currentrow, 1).Text
Me.txtMonYear.Text = ws.Cells(currentrow, 4).Text
Me.txtBriefSyn.Text = ws.Cells(currentrow, 5).Text
Me.txtTypStatus.Text = ws.Cells(currentrow, 7).Text
Me.txtOCStatus.Text = ws.Cells(currentrow, 8).Text
Me.txtSentDate.Text = ws.Cells(currentrow, 10).Text
End If
Next currentrow
Me.txtCurTrack.SetFocus
End Sub
Private Sub cmdFindPrevious_Click()
Dim ws As Worksheet
Set ws = Worksheets("Master")
Dim lastrow
lastrow = Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row
myDate = Me.txtCurTrack.Text
For currentrow = lastrow To 2 Step -1
If ws.Cells(currentrow, 1).Text = myDate Then
Me.txtCurTrack.Text = ws.Cells(currentrow, 1).Text
Me.txtMonYear.Text = ws.Cells(currentrow, 4).Text
Me.txtBriefSyn.Text = ws.Cells(currentrow, 5).Text
Me.txtTypStatus.Text = ws.Cells(currentrow, 7).Text
Me.txtOCStatus.Text = ws.Cells(currentrow, 8).Text
Me.txtSentDate.Text = ws.Cells(currentrow, 10).Text
End If
Next currentrow
Me.txtCurTrack.SetFocus
End Sub
Private Sub cmdNextRecord_Click()
Dim ws As Worksheet
Set ws = Worksheets("Master")
lastrow = Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row
currentrow = currentrow + 1
If currentrow = lastrow + 1 Then
MsgBox ("You have reached the last row of Data!")
currentrow = lastrow
End If
Me.txtCurTrack.Text = ws.Cells(currentrow, 1).Text
Me.txtMonYear.Text = ws.Cells(currentrow, 4).Text
Me.txtBriefSyn.Text = ws.Cells(currentrow, 5).Text
Me.txtTypStatus.Text = ws.Cells(currentrow, 7).Text
Me.txtOCStatus.Text = ws.Cells(currentrow, 8).Text
Me.txtSentDate.Text = ws.Cells(currentrow, 10).Text
End Sub
Private Sub cmdPreviousRecord_Click()
Dim ws As Worksheet
Set ws = Worksheets("Master")
currentrow = currentrow - 1
If currentrow > 1 Then
Me.txtCurTrack.Text = ws.Cells(currentrow, 1).Text
Me.txtMonYear.Text = ws.Cells(currentrow, 4).Text
Me.txtBriefSyn.Text = ws.Cells(currentrow, 5).Text
Me.txtTypStatus.Text = ws.Cells(currentrow, 7).Text
Me.txtOCStatus.Text = ws.Cells(currentrow, 8).Text
Me.txtSentDate.Text = ws.Cells(currentrow, 10).Text
ElseIf currentrow = 1 Then
MsgBox ("Now you are in the header!")
currentrow = currentrow + 1
End If
End Sub
Private Sub cmdUpdate_Click()
Dim ws As Worksheet
Set ws = Worksheets("Master")
Dim CurTrack As String, OriTrack As String, ReqDate As String, MonYear As String, BriefSyn As String, ReqName As String, TypStatus As String, OCStatus As String, CompDate As String, SentDate As String, DocumSR As String, Comments As String
CurTrack = txtCurTrack.Text
ws.Cells(currentrow, 1).Value = CurTrack
OriTrack = txtvbNewLine.Text
ws.Cells(currentrow, 4).Value = MonYear
BriefSyn = Me.txtBriefSyn.Text
ws.Cells(currentrow, 5).Value = BriefSyn
TypStatus = Me.txtTypStatus.Text
ws.Cells(currentrow, 7).Value = TypStatus
OCStatus = Me.txtOCStatus.Text
ws.Cells(currentrow, 8).Value = OCStatus
SentDate = Me.txtSentDate.Text
ws.Cells(currentrow, 10).Value = SentDate
End Sub
Private Sub ListBox1_Click()
End Sub
Private Sub ListBox2_Click()
End Sub
Private Sub ListBox3_Click()
End Sub
Private Sub ProgramName_Change()
End Sub
Private Sub txtCurTrack_Change()
End Sub
Private Sub txtOriTrack_Change()
End Sub
Private Sub txtReqDate_Change()
End Sub
Private Sub txtMonYear_Change()
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
Dim ws As Worksheet
Set ws = Worksheets("Master")
currentrow = 2
Me.txtCurTrack.Text = ws.Cells(currentrow, 1).Text
Me.txtMonYear.Text = ws.Cells(currentrow, 4).Text
Me.txtBriefSyn.Text = ws.Cells(currentrow, 5).Text
Me.txtTypStatus.Text = ws.Cells(currentrow, 7).Text
Me.txtOCStatus.Text = ws.Cells(currentrow, 8).Text
Me.txtSentDate.Text = ws.Cells(currentrow, 10).Text
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the Close Form Button!"
End If
End Sub
Private Sub CommandButton3_Click()
frmTrackNumbers.Show
End Sub
End Sub
Hi....I am using a Userform to enter data into a worksheet labelled "Master". The Listbox that I am having problems with is "ProgramName" . The listbox items are loaded and displayed and when an item from list is selected, a popup window indicates the item that was selected. Up to this point everything is working fine. Its from this point onward where I am having the problem where I would like the selected item to be retained in the listbox and be sent to the worksheet labelled "Master" in column B when the command button Submit is clicked to populate the worksheet with all the entries on the Userform. Any suggestion what I need to do...thanks.
Dim currentrow As Long
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Master")
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
With ws
ws.Cells(iRow, 1).Value = Me.txtCurTrack.Value
ws.Cells(iRow, 4).Value = Me.txtMonYear.Value
ws.Cells(iRow, 5).Value = Me.txtBriefSyn.Value
ws.Cells(iRow, 7).Value = Me.txtTypStatus.Value
ws.Cells(iRow, 8).Value = Me.txtOCStatus.Value
ws.Cells(iRow, 10).Value = Me.txtSentDate.Value
End With
'clear the data
Me.txtCurTrack.Value = ""
Me.txtMonYear.Value = ""
Me.txtBriefSyn.Value = ""
Me.txtTypStatus.Value = ""
Me.txtOCStatus.Value = ""
Me.txtSentDate.Value = ""
Me.txtCurTrack.SetFocus
End Sub
Private Sub cmdAddProgram_Click()
Dim Msg As String
Dim i As Integer
Dim sSel, iCt
Msg = "You selected" & ProgramNames
For i = 0 To ProgramName.ListCount - 1
If ProgramName.Selected(i) Then
Msg = Msg & ProgramName.List(i) & ProgramNames
End If
Next i
MsgBox Msg
End Sub
Private Sub cmdCheckTrackNumbers_Click()
frmTrackNumbers.Show
End Sub
Private Sub cmdClearData_Click()
Me.txtCurTrack.Value = ""
Me.txtMonYear.Value = ""
Me.txtBriefSyn.Value = ""
Me.txtTypStatus.Value = ""
Me.txtOCStatus.Value = ""
Me.txtSentDate.Value = ""
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub cmdFindNext_Click()
Dim ws As Worksheet
Set ws = Worksheets("Master")
Dim lastrow
lastrow = Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row
myDate = Me.txtCurTrack.Text
For currentrow = 2 To lastrow
If ws.Cells(currentrow, 1).Text = myDate Then
Me.txtCurTrack.Text = ws.Cells(currentrow, 1).Text
Me.txtMonYear.Text = ws.Cells(currentrow, 4).Text
Me.txtBriefSyn.Text = ws.Cells(currentrow, 5).Text
Me.txtTypStatus.Text = ws.Cells(currentrow, 7).Text
Me.txtOCStatus.Text = ws.Cells(currentrow, 8).Text
Me.txtSentDate.Text = ws.Cells(currentrow, 10).Text
End If
Next currentrow
Me.txtCurTrack.SetFocus
End Sub
Private Sub cmdFindPrevious_Click()
Dim ws As Worksheet
Set ws = Worksheets("Master")
Dim lastrow
lastrow = Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row
myDate = Me.txtCurTrack.Text
For currentrow = lastrow To 2 Step -1
If ws.Cells(currentrow, 1).Text = myDate Then
Me.txtCurTrack.Text = ws.Cells(currentrow, 1).Text
Me.txtMonYear.Text = ws.Cells(currentrow, 4).Text
Me.txtBriefSyn.Text = ws.Cells(currentrow, 5).Text
Me.txtTypStatus.Text = ws.Cells(currentrow, 7).Text
Me.txtOCStatus.Text = ws.Cells(currentrow, 8).Text
Me.txtSentDate.Text = ws.Cells(currentrow, 10).Text
End If
Next currentrow
Me.txtCurTrack.SetFocus
End Sub
Private Sub cmdNextRecord_Click()
Dim ws As Worksheet
Set ws = Worksheets("Master")
lastrow = Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row
currentrow = currentrow + 1
If currentrow = lastrow + 1 Then
MsgBox ("You have reached the last row of Data!")
currentrow = lastrow
End If
Me.txtCurTrack.Text = ws.Cells(currentrow, 1).Text
Me.txtMonYear.Text = ws.Cells(currentrow, 4).Text
Me.txtBriefSyn.Text = ws.Cells(currentrow, 5).Text
Me.txtTypStatus.Text = ws.Cells(currentrow, 7).Text
Me.txtOCStatus.Text = ws.Cells(currentrow, 8).Text
Me.txtSentDate.Text = ws.Cells(currentrow, 10).Text
End Sub
Private Sub cmdPreviousRecord_Click()
Dim ws As Worksheet
Set ws = Worksheets("Master")
currentrow = currentrow - 1
If currentrow > 1 Then
Me.txtCurTrack.Text = ws.Cells(currentrow, 1).Text
Me.txtMonYear.Text = ws.Cells(currentrow, 4).Text
Me.txtBriefSyn.Text = ws.Cells(currentrow, 5).Text
Me.txtTypStatus.Text = ws.Cells(currentrow, 7).Text
Me.txtOCStatus.Text = ws.Cells(currentrow, 8).Text
Me.txtSentDate.Text = ws.Cells(currentrow, 10).Text
ElseIf currentrow = 1 Then
MsgBox ("Now you are in the header!")
currentrow = currentrow + 1
End If
End Sub
Private Sub cmdUpdate_Click()
Dim ws As Worksheet
Set ws = Worksheets("Master")
Dim CurTrack As String, OriTrack As String, ReqDate As String, MonYear As String, BriefSyn As String, ReqName As String, TypStatus As String, OCStatus As String, CompDate As String, SentDate As String, DocumSR As String, Comments As String
CurTrack = txtCurTrack.Text
ws.Cells(currentrow, 1).Value = CurTrack
OriTrack = txtvbNewLine.Text
ws.Cells(currentrow, 4).Value = MonYear
BriefSyn = Me.txtBriefSyn.Text
ws.Cells(currentrow, 5).Value = BriefSyn
TypStatus = Me.txtTypStatus.Text
ws.Cells(currentrow, 7).Value = TypStatus
OCStatus = Me.txtOCStatus.Text
ws.Cells(currentrow, 8).Value = OCStatus
SentDate = Me.txtSentDate.Text
ws.Cells(currentrow, 10).Value = SentDate
End Sub
Private Sub ListBox1_Click()
End Sub
Private Sub ListBox2_Click()
End Sub
Private Sub ListBox3_Click()
End Sub
Private Sub ProgramName_Change()
End Sub
Private Sub txtCurTrack_Change()
End Sub
Private Sub txtOriTrack_Change()
End Sub
Private Sub txtReqDate_Change()
End Sub
Private Sub txtMonYear_Change()
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
Dim ws As Worksheet
Set ws = Worksheets("Master")
currentrow = 2
Me.txtCurTrack.Text = ws.Cells(currentrow, 1).Text
Me.txtMonYear.Text = ws.Cells(currentrow, 4).Text
Me.txtBriefSyn.Text = ws.Cells(currentrow, 5).Text
Me.txtTypStatus.Text = ws.Cells(currentrow, 7).Text
Me.txtOCStatus.Text = ws.Cells(currentrow, 8).Text
Me.txtSentDate.Text = ws.Cells(currentrow, 10).Text
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the Close Form Button!"
End If
End Sub
Private Sub CommandButton3_Click()
frmTrackNumbers.Show
End Sub
End Sub
