Link to home
Start Free TrialLog in
Avatar of Bright01
Bright01Flag for United States of America

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
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Which column(s) do you want to have the double-click toggle the characters?
Avatar of Bright01

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.
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Martin!  Perfect!  Much thanks......... sooner or later, I'm going to become better at this.

B.
You're welcome.
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.
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.
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.
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

Open in new window

Martin,

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

Open in new window