Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code, AND where to place it !!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code, AND where to place it !!
"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? -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code, AND where to place it !!
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.... "Jim Cone" wrote in message ... "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? -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code, AND where to place it !!
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" 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.... "Jim Cone" wrote in message ... "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? -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code, AND where to place it !!
When i cut/paste the code to the click section instead of the
dropbuttonclick i get NO list then ?? "Jim Cone" wrote in message ... 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" 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.... "Jim Cone" wrote in message ... "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? -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code, AND where to place it !!
Is there not a code like:
if Combobox6.SelectionSource <"" then Combobox6.SelectionSource.Font.Strikethrough=TRUE Corey.... "Corey" wrote in message ... When i cut/paste the code to the click section instead of the dropbuttonclick i get NO list then ?? "Jim Cone" wrote in message ... 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" 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.... "Jim Cone" wrote in message ... "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? -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code, AND where to place it !!
Add the list in the DropButtonClick event.
Do the cell strikethrough using the ComboBox_Click event. -- Jim Cone San Francisco http://www.officeletter.com/blink/specialsort.html "Corey" wrote in message Is there not a code like: if Combobox6.SelectionSource <"" then Combobox6.SelectionSource.Font.Strikethrough=TRUE Corey.... "Corey" wrote in message ... When i cut/paste the code to the click section instead of the dropbuttonclick i get NO list then ?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to place value in another cell | Excel Programming | |||
template code in one place | Excel Programming | |||
where do i place the code ? | Excel Programming | |||
Wherre best to place code | New Users to Excel | |||
Where to place Code question | Excel Programming |