How to code, AND where to place it !!

  • Thread starter Thread starter Corey
  • Start date Start date
C

Corey

I want to enable once the Combobox is left, that the value that was chosen from the worksheet, to change to a strikethrough font.

I am not sure how to code this, AND if it should go in the combobox section below or in the Commandbutton (OK) click on the userform,
What would i code and where to put it?

Private Sub ComboBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)

End Sub

OR

Private Sub CommandButton1_Click()
Sheets("DespatchData").Select
' does the stuff here
Unload Me
Sheets("Main").Select
Range("A1").Activate
End Sub




Code i use to populate Combobox6:

Private Sub ComboBox6_DropButtonClick()
Application.ScreenUpdating = False
If ComboBox6.ListCount > 0 Then Exit Sub
'Place the References in here for the Roll Numbers and Lengths
Dim lastcell As Long
Dim myrow As Long

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 = ComboBox28.Text And .Cells(myrow, 1).Offset(-1, 6).Text = ComboBox1.Text And .Cells(myrow, 1).Offset(0, 0).Value = ComboBox5.Text And IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then
For i = 2 To 22
If Cells(myrow, 3).Offset(i, 0).Font.Strikethrough = False And Cells(myrow, 3).Offset(i, 0).Value <> "" Then
ComboBox6.AddItem Cells(myrow, 3).Offset(i, 0)
Cells(myrow, 3).Offset(i, 0).Font.Strikethrough = True
End If
Next i
End If
End If
Next
End With

Application.ScreenUpdating = True
End Sub
 
re: "I want to enable once the Combobox is left, that the value that was
chosen from the worksheet, to change to a strikethrough font."

Huh?
 
Meaning Once the selection has been done,
and the user moves on to the next item,
the value(NOT in the combobox it self)
but the value that populated the combobox
(eg. Say combobox list was sheet4 C2:C26. and C25 was selected then
....Sheet4 C25) has the value changed to Strikethrough.

I added this but it changed ALL(i, 2) values to strikethrough:


Private Sub ComboBox6_DropButtonClick()
Application.ScreenUpdating = False
If ComboBox6.ListCount > 0 Then Exit Sub
'Place the References in here for the Roll Numbers and Lengths
Dim lastcell As Long
Dim myrow As Long

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 = ComboBox28.Text And
..Cells(myrow, 1).Offset(-1, 6).Text = ComboBox1.Text And .Cells(myrow,
1).Offset(0, 0).Value = ComboBox5.Text And IsNumeric(Trim(Mid(.Cells(myrow,
1), 2))) = True Then
For i = 2 To 22
If Cells(myrow, 3).Offset(i, 0).Font.Strikethrough =
False And Cells(myrow, 3).Offset(i, 0).Value <> "" Then
ComboBox6.AddItem Cells(myrow, 3).Offset(i, 0)
Cells(myrow, 3).Offset(i,
0).Font.Strikethrough = True ' <================= Here. But ALL values
changed. But i need to give user option to replace the value if wrongly
selected. That is why i was thinking of Combobox6 Exit section????
End If
Next i
End If
End If
Next
End With

Application.ScreenUpdating = True
End Sub


Hope i cleared that up a bit.

Corey....
 
You are using the DropButtonClick event.
The user does not select an item when the dropdown is clicked.
Selection is done in the ComboBox_Click event.

You can get the index of the item selected and use that to
find the worksheet cell or run a Match function on the value...

Private Sub ComboBox1_Click()
MsgBox ComboBox1.Value & vbCr & ComboBox1.ListIndex
End Sub
--
It is time for bed.
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Corey" <[email protected]>
wrote in message
Meaning Once the selection has been done,
and the user moves on to the next item,
the value(NOT in the combobox it self)
but the value that populated the combobox
(eg. Say combobox list was sheet4 C2:C26. and C25 was selected then
....Sheet4 C25) has the value changed to Strikethrough.

I added this but it changed ALL(i, 2) values to strikethrough:


Private Sub ComboBox6_DropButtonClick()
Application.ScreenUpdating = False
If ComboBox6.ListCount > 0 Then Exit Sub
'Place the References in here for the Roll Numbers and Lengths
Dim lastcell As Long
Dim myrow As Long

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 = ComboBox28.Text And
..Cells(myrow, 1).Offset(-1, 6).Text = ComboBox1.Text And .Cells(myrow,
1).Offset(0, 0).Value = ComboBox5.Text And IsNumeric(Trim(Mid(.Cells(myrow,
1), 2))) = True Then
For i = 2 To 22
If Cells(myrow, 3).Offset(i, 0).Font.Strikethrough =
False And Cells(myrow, 3).Offset(i, 0).Value <> "" Then
ComboBox6.AddItem Cells(myrow, 3).Offset(i, 0)
Cells(myrow, 3).Offset(i,
0).Font.Strikethrough = True ' <================= Here. But ALL values
changed. But i need to give user option to replace the value if wrongly
selected. That is why i was thinking of Combobox6 Exit section????
End If
Next i
End If
End If
Next
End With

Application.ScreenUpdating = True
End Sub


Hope i cleared that up a bit.

Corey....
 
When i cut/paste the code to the click section instead of the
dropbuttonclick i get NO list then ??


Jim Cone said:
You are using the DropButtonClick event.
The user does not select an item when the dropdown is clicked.
Selection is done in the ComboBox_Click event.

You can get the index of the item selected and use that to
find the worksheet cell or run a Match function on the value...

Private Sub ComboBox1_Click()
MsgBox ComboBox1.Value & vbCr & ComboBox1.ListIndex
End Sub
--
It is time for bed.
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Corey" <[email protected]>
wrote in message
Meaning Once the selection has been done,
and the user moves on to the next item,
the value(NOT in the combobox it self)
but the value that populated the combobox
(eg. Say combobox list was sheet4 C2:C26. and C25 was selected then
...Sheet4 C25) has the value changed to Strikethrough.

I added this but it changed ALL(i, 2) values to strikethrough:


Private Sub ComboBox6_DropButtonClick()
Application.ScreenUpdating = False
If ComboBox6.ListCount > 0 Then Exit Sub
'Place the References in here for the Roll Numbers and Lengths
Dim lastcell As Long
Dim myrow As Long

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 = ComboBox28.Text
And
.Cells(myrow, 1).Offset(-1, 6).Text = ComboBox1.Text And .Cells(myrow,
1).Offset(0, 0).Value = ComboBox5.Text And
IsNumeric(Trim(Mid(.Cells(myrow,
1), 2))) = True Then
For i = 2 To 22
If Cells(myrow, 3).Offset(i, 0).Font.Strikethrough
=
False And Cells(myrow, 3).Offset(i, 0).Value <> "" Then
ComboBox6.AddItem Cells(myrow, 3).Offset(i, 0)
Cells(myrow, 3).Offset(i,
0).Font.Strikethrough = True ' <================= Here. But ALL values
changed. But i need to give user option to replace the value if wrongly
selected. That is why i was thinking of Combobox6 Exit section????
End If
Next i
End If
End If
Next
End With

Application.ScreenUpdating = True
End Sub


Hope i cleared that up a bit.

Corey....
Click to expand...
 
Is there not a code like:
if Combobox6.SelectionSource <>"" then
Combobox6.SelectionSource.Font.Strikethrough=TRUE


Corey....
Corey said:
When i cut/paste the code to the click section instead of the
dropbuttonclick i get NO list then ??
Click to expand...
 
Back
Top