Bright01
asked on
Populating a Cell with specific Characters
EE Pros,
I've tried to put some old VB Code in place to provide a set of characters (rotation) you can select by doubleclicking on a specific cell within a specific Range. See the VB Editor for the code I put in and the Range Names for the 3 Cell Ranges (in Yellow in WS). Help please!?
Much thanks in advance.
B.
Check-Box.xlsm
I've tried to put some old VB Code in place to provide a set of characters (rotation) you can select by doubleclicking on a specific cell within a specific Range. See the VB Editor for the code I put in and the Range Names for the 3 Cell Ranges (in Yellow in WS). Help please!?
Much thanks in advance.
B.
Check-Box.xlsm
Which column(s) do you want to have the double-click toggle the characters?
ASKER
Greetings Martin. Thanks for picking this up. Most of the code I was working with is probably yours that you did for me previously.
What I'm trying to do in this "rendition" is target the cells via a range name. The column is "F" but again, was trying to extract the specific column by referring to a range name instead.
Make sense?
B.
What I'm trying to do in this "rendition" is target the cells via a range name. The column is "F" but again, was trying to extract the specific column by referring to a range name instead.
Make sense?
B.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Martin! Perfect! Much thanks......... sooner or later, I'm going to become better at this.
B.
B.
You're welcome.
ASKER
Martin,
In thoroughly testing the code, this code doesn't refer to the range names to determine when to fire off the correct characters. Can you take a re-look? I need it to only operate within the defined Range names.
Thank you,
B.
In thoroughly testing the code, this code doesn't refer to the range names to determine when to fire off the correct characters. Can you take a re-look? I need it to only operate within the defined Range names.
Thank you,
B.
I don't understand what you are trying to do with the range names. Double-clicking on column 6 works because as I said above I added a constant for column 6 and you can do the same for the other columns.
ASKER
Yes.... it works as long as I don't have anything in the adjoining cell. This would include cells outside the range that have nothing in the adjointing cell. That's why I had stipulated looking at only those cells in a Range.
B.
B.
Ah, OK. Replace the code with this. In this case it doesn't matter if you use COL_TOGGLE or 6.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Provides check button
Dim rngToggle As Range
Set rngToggle = Application.Union(Range("Text1"), Range("Text2"), Range("Text3"))
If Intersect(Target, rngToggle) Is Nothing Then
Exit Sub
End If
Select Case Target.Column
Case COL_TOGGLE '6 '5
With ActiveCell
If .Offset(0, 1).Value <> "" Then
Cancel = True
Toggle Target
' Target.Offset(0, 4).ClearContents
End If
End With
Case 4
If ActiveCell.Offset(0, -3).Value = "2" Then
Cancel = True
Toggle Target
End If
Case 3
If ActiveCell.Offset(0, -2).Value = "1" Then
Toggle Target
Cancel = True
End If
End Select
End Sub
Sub Toggle(cel As Range)
Application.ScreenUpdating = False
'ClearAllProtection ActiveSheet
Select Case cel.Value
Case CHECKMARK ' It's a checkmark so change to ?
cel.Font.Name = "Calibri"
cel = "?"
Case "?" ' it's an ? so change it to X
cel.Font.Name = "Wingdings 2"
cel = "O"
Case "O" ' It's an X so change it to a blank
cel = ""
Case "" ' It's blank so change it to a checkmark
cel.Font.Name = "Wingdings 2"
cel = CHECKMARK
'Application.EnableEvents = False
' If ActiveCell.Column = 3 Then
' Rows(ActiveCell.Row + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
' Sheets("Templates").Rows("1").Copy Destination:=Sheets(ActiveSheet.Name).Rows(ActiveCell.Row + 1)
' ElseIf ActiveCell.Column = 4 Then
' Rows(ActiveCell.Row + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
' Sheets("Templates").Rows("2").Copy Destination:=Sheets(ActiveSheet.Name).Rows(ActiveCell.Row + 1)
' End If
End Select
End Sub
ASKER
Martin,
When I apply this code, I get a "not Defined" on this line item: COL_TOGGLE.
B.
When I apply this code, I get a "not Defined" on this line item: COL_TOGGLE.
B.
In the workbook I previously attached there was Private Const COL_TOGGLE = 6 line. Put that at the top of the module or just use 6 instead of COL_TOGGLE in line 13 above.
I just realized that you could also do this. See line 2.
Select Case Target.Column
Case Range("Text1").Column
With ActiveCell
If .Offset(0, 1).Value <> "" Then
Cancel = True
Toggle Target
ASKER
Still cannot get it to work. Posted another question with the changes:
https://www.experts-exchange.com/questions/29017242/Troubleshooting-a-Macro-for-Doubleclick-a-Character-in-a-Range-Cell.html
https://www.experts-exchange.com/questions/29017242/Troubleshooting-a-Macro-for-Doubleclick-a-Character-in-a-Range-Cell.html