Form trouble

[Oreg]

Hi,

With help on this board, I've been able to create a form with
comboboxes and listboxes that enters data in the next empty row of a
sheet named "METRO".
partial code below:

Private Sub ADD_Click()
Dim r As Long
Dim ws As Worksheet
Set ws = Worksheets("METRO")
r = ws.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Row
ws.Range("C" & r).Value = ComboBox1.Value
ws.Range("E" & r).Value = rngsw.Value
ws.Range("F" & r).Value = rngfail.Value
ws.Range("H" & r).Value = ComboBox2.Value
ws.Range("I" & r).Value = ComboBox3.Value
ws.Range("J" & r).Value = ComboBox4.Value
ws.Range("K" & r).Value = ComboBox5.Value
ws.Range("L" & r).Value = ComboBox6.Value
ws.Range("T" & r).Value = ComboBox7.Value
ws.Range("AA" & r).Value = ComboBox8.Value
ws.Range("AD" & r).Value = Timestart.Value
ws.Range("AE" & r).Value = Timeend.Value
ws.Range("M" & r).Value = ComboBox9.Value
ComboBox1.Value = ""
rngsw.Value = ""
rngfail.Value = ""
ComboBox7.Value = ""
ComboBox8.Value = ""
Timestart.Value = ""
Timeend.Value = ""
ComboBox9.Value = ""
ComboBox2.SetFocus
Unload CIDATA
CIDATA.Show

End Sub
If possible, I would like to create a similar form which lists rows
that contain a "Y" in column E.

Any Ideas?
Thanks

 

[Nigel]

It appears that the CIDATA userform is cleared after each addition to the
form. So you need to specify how the new userform is controlled. To
control what rows are listed in the combo boxes using the value in a cell
(column E = Y in your OP) you will need to fill the boxes under programme
control. How are the current comboBox list values currently set up? Suggest
you post the rest of the userform code to help understand what is happening.

Cheers
Nigel

 

[Oreg]

Hey Nigel,

All of my Comboboxes are populated with selections I've created. Cod
below: Hope this is what your looking for.


Private Sub UserForm_Initialize()
ComboBox1.AddItem "Out-of-Process"
ComboBox1.AddItem "In-Process"
ComboBox1.AddItem "Environmental"
ComboBox1.AddItem "TBD"
ComboBox1.AddItem "IRU"
ComboBox2.AddItem "A"
ComboBox2.AddItem "B"
ComboBox2.AddItem "N/A"
ComboBox2.AddItem "?"
ComboBox3.AddItem "PCI"
ComboBox3.AddItem "CI"
ComboBox3.AddItem "N/A"
ComboBox3.AddItem "?"
ComboBox4.AddItem "Y"
ComboBox4.AddItem "N"
ComboBox4.AddItem "N/A"
ComboBox4.AddItem "?"
ComboBox5.AddItem "Y"
ComboBox5.AddItem "N"
ComboBox5.AddItem "N/A"
ComboBox5.AddItem "?"
ComboBox6.AddItem "Y"
ComboBox6.AddItem "N"
ComboBox6.AddItem "N/A"
ComboBox6.AddItem "?"
ComboBox7.AddItem "JK"
ComboBox7.AddItem "Perm"
ComboBox7.AddItem "Other"
ComboBox7.AddItem "Reel"
ComboBox7.AddItem "Slack"
ComboBox8.AddItem "Y"
ComboBox8.AddItem "N"
ComboBox9.AddItem "T"
ComboBox9.AddItem "P"
ComboBox9.AddItem "N/A"
ComboBox9.AddItem "?"

Dim r As Long
Dim ws As Worksheet
Set ws = Worksheets("METRO")
With ws
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
End If
End With
r = ws.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).Row
TextBox1.Text = ws.Range("D" & r).Value
TextBox2.Text = ws.Range("B" & r).Value
TextBox3.Text = ws.Range("G" & r).Value
TextBox4.Text = ws.Range("AH" & r).Value

End Su