Need assistance to populate sheet from Userform data

C

Corey

I am going crazy trying to work this out.

I have a shet that i am trying to populate with data from another sheet.

I am using a userform to do this for some of the data.
The userform is triggered from the event below :-
~~~~~~~~~ Userform.Show ~~~~~~~~~~~~~~~~~~~~
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
With Sheet5
If Not Intersect(Target, Me.Range("A4:A13")) Is Nothing Then
ActiveCell.Select
Call UF11
End If
End With
Application.ScreenUpdating = True
End Sub
~~~~~~~~~ End of Code ~~~~~~~~~~~~~~~~~~~~~~

When the user activates a cell in the above code range the Userform is
displayed.
The Userform has so far 2 comboboxes(Combobox1 & Combobox 2)
:
~~~~~~~~~~~~~~~ Combobox1 Code ~~~~~~~~~~~~~
Private Sub ComboBox1_DropButtonClick()
'Application.ScreenUpdating = False
If ComboBox1.ListCount > 0 Then Exit Sub
'Place the References in here for the Roll Numbers and Lengths
Dim lastcell As Long
Dim myrow As Long
On Error Resume Next
lastcell = workSheets("InspectionData").Cells(Rows.Count, "A").End(xlUp).Row

With ActiveWorkbook.workSheets("InspectionData")
..Select 'first thing to do with a With statement that occurs on a second
sheet
For myrow = 2 To lastcell
If .Cells(myrow, 1) <> "" Then
If .Cells(myrow, 1).Offset(-1, 2).Text = Sheet5.Range("B2").Value
And IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then
ComboBox1.AddItem Cells(myrow, 1)
End If
End If

Next
End With
End Sub
~~~~~~~~~~~~~~ End of Code ~~~~~~~~~~~~~~~~~~~

The ABOVE code lists ALL values in the other sheet that are Offset(-1,2)
from the value that was placed in Range(B2) in the CURRENT sheet.
(See Bottom of Post for where this value is placed)

Then the user selects a value from the list in Combobox2, Code below :
~~~~~~~~~~~~~~ Combobox2 Code ~~~~~~~~~~~~~~~
Private Sub ComboBox2_DropButtonClick()
Application.ScreenUpdating = False
If ComboBox2.ListCount > 0 Then Exit Sub
'Place the References in here for the Roll Numbers and Lengths
Dim lastcell As Long
Dim myrow As Long
On Error Resume Next
lastcell = workSheets("InspectionData").Cells(Rows.Count,
"A").End(xlUp).Row

With ActiveWorkbook.workSheets("InspectionData")
For myrow = 2 To lastcell
If .Cells(myrow, 1) <> "" Then
If .Cells(myrow, 1).Offset(-1, 2).Text =
Sheet5.Range("B2").Value And .Cells(myrow, 1).Offset(0, 0).Value =
ComboBox1.Text And IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then
For i = 2 To 22
If Cells(myrow, 3).Offset(i, 0).Value <> "" Then
ComboBox2.AddItem Cells(myrow, 3).Offset(i, 0)
ComboBox2.List(ComboBox2.ListCount - 1, 1) = Cells(myrow, 3).Offset(i,
0).Address
End If
Next i
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub
~~~~~~~~~~~~~~ End of Code ~~~~~~~~~~~~~~~~~~

The Above code lists the values that are Offset between Offset(2,2) -
Offset(22,2) from the Value in the other sheet that was Selected in
Combobox1.
(See Bottom of Post for where this value is placed)

The values chosen from the Comboboxes 1 & 2 from the userform are placed int
he Current sheet byt he Commanbutton Click event below :
~~~~~~~~~~~ CommandButton Click Code ~~~~~~~~~
Private Sub CommandButton1_Click()
With Sheet5
..Select
ActiveCell.Value = UserForm11.ComboBox1.Value
ActiveCell.Offset(0, 5).Value = UserForm11.ComboBox2.Value
ActiveCell.Offset(0, 1).Value = UserForm11.TextBox1.Value ' <=== This value
does not populate
Unload Me
End With
End Sub
~~~~~~~~~~~~ End of Code ~~~~~~~~~~~~~~~~~~~


So far ALL is WELL and Working Great.
NOW,
what i am trying to do is populate some other DATA from the other sheet into
the current sheet that is OFFSET from the Value chosen in Combobox2.
I want to ADD the value that is Offset(0,7).value from Combobox2.value
Chosen and Place it in ActiveCell.Offset(0, 1).Value.

Can anyoine assist me in this ?

Corey....
 
C

Corey

If i place a 3rd Combobox on the Userform and put the follong code attached
to it:
Private Sub ComboBox3_DropButtonClick()
Application.ScreenUpdating = False
If ComboBox3.ListCount > 0 Then Exit Sub
'Place the References in here for the Roll Numbers and Lengths
Dim lastcell As Long
Dim myrow As Long
On Error Resume Next
lastcell = workSheets("InspectionData").Cells(Rows.Count,
"A").End(xlUp).Row

With ActiveWorkbook.workSheets("InspectionData")
For myrow = 2 To lastcell
If .Cells(myrow, 1) <> "" Then
If .Cells(myrow, 1).Offset(-1, 2).Text =
Sheet5.Range("B2").Value And .Cells(myrow, 1).Offset(0, 0).Value =
ComboBox1.Text And IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then
For i = 2 To 22
If Cells(myrow, 10).Offset(i, 0).Value <> "" Then
ComboBox3.AddItem Cells(myrow, 10).Offset(i, 0)
ComboBox3.List(ComboBox2.ListCount - 1, 1) = Cells(myrow, 10).Offset(i,
0).Address

End If
Next i
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub
I get a list of the Values i need,
BUT i ONLY want the Value int hat list that is in the SAME ROW as the
selected value in COMBOBOX2.

May assist you in assisting me.


Corey....
 
C

Corey

The first section of code populates Combobox2 for me:
It populates the list with values in Column C in the InspectionData Sheet.

I can get the Code 2(Combobox3) list to Populate with ALL the Values in the
Same ROW as the ALL the values in the List in Combobox2, But
i ONLY want the 1(ONE) value that is in the same ROW as the Selected value
in the Combobox2.

Is there a way to do this ????

~~~~~~~ Code 1 ~~~~~~~~~~~~~~~~~
Private Sub ComboBox2_DropButtonClick()
Application.ScreenUpdating = False
If ComboBox2.ListCount > 0 Then Exit Sub
'Place the References in here for the Roll Numbers and Lengths
Dim lastcell As Long
Dim myrow As Long
On Error Resume Next
lastcell = Worksheets("InspectionData").Cells(Rows.Count,
"A").End(xlUp).Row

With ActiveWorkbook.Worksheets("InspectionData")
For myrow = 2 To lastcell
If .Cells(myrow, 1) <> "" Then
If .Cells(myrow, 1).Offset(-1, 2).Text =
Sheet5.Range("B2").Value And .Cells(myrow, 1).Offset(0, 0).Value =
ComboBox1.Text And IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then
For i = 2 To 22
If Cells(myrow, 3).Offset(i, 0).Value <> "" Then
ComboBox2.AddItem Cells(myrow, 3).Offset(i, 0)
ComboBox2.List(ComboBox2.ListCount - 1, 1) = Cells(myrow, 3).Offset(i,
0).Address
End If
Next i
End If
End If
Next
End With
Application.ScreenUpdating = True

End Sub
~~~~~~~~ End ~~~~~~~~~~~~~~~~~

~~~~~~~~ Code 2 ~~~~~~~~~~~~~~

Private Sub ComboBox3_DropButtonClick()
Application.ScreenUpdating = False
If ComboBox3.ListCount > 0 Then Exit Sub
'Place the References in here for the Roll Numbers and Lengths
Dim lastcell As Long
Dim myrow As Long
On Error Resume Next
lastcell = Worksheets("InspectionData").Cells(Rows.Count,
"A").End(xlUp).Row

With ActiveWorkbook.Worksheets("InspectionData")
For myrow = 2 To lastcell
If .Cells(myrow, 1) <> "" Then
If .Cells(myrow, 1).Offset(-1, 2).Text =
Sheet5.Range("B2").Value And .Cells(myrow, 1).Offset(0, 0).Value =
ComboBox1.Text And IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then
For i = 2 To 22
If Cells(myrow, 10).Offset(i, 0).Value <> "" Then
ComboBox3.AddItem Cells(myrow, 10).Offset(i, 0)
ComboBox3.List(ComboBox2.ListCount - 1, 1) = Cells(myrow, 10).Offset(i,
0).Address
If .Cells(myrow, 3).Offset(0, 0).Value = UserForm11.ComboBox2.Value Then
ComboBox3.Value = .Cells(myrow, 3).Offset(0, 7).Value

End If
End If
Next i
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub
~~~~~~~~ End ~~~~~~~~~~~~~~~
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to change For i - to refer to specific columns instead of rows 6
Error in code 8
Combobox list to leave ONLY related values in list ?? 2
How to code, AND where to place it !! 6
Why does the line of code NOT work? 2
Problem with Chnaging fonts to Strikethrough 1
Remove Numerical Values from Listbox and Certain Text Value 3
Change Combobox list to list more than 1 cell.value 2

Top