Results 1 to 5 of 5

Thread: [RESOLVED] Slow the user down?

  1. #1

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,427

    Resolved [RESOLVED] Slow the user down?

    I have a situation where I want to cycle through several values in a cell and my code is in the Worksheet_SelectionChange event. I want it to behave like a checkbox in that I can repeatedly change it's value by clicking it. My values however are in a cell and to simulate a click you need to leave the cell and come back to it. My solution to that was after the cell is selected and changed (via code) I would turn off ScreenUpdating, select a cell via code that was off-screen, and then turn screen updating back on. That works fine except if I "click"" the cell too fast. When that happens the off-screen cell is selected but I'm left there. Is there any way to prevent that?

    Here's the code. It affects A1 to A5.
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    On Error GoTo ErrorRoutine
    
    If Target.Cells.Count = 1 Then
        ' Cycle between checkmark, ! and X
        If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
            Select Case Asc(Target)
                Case 80 ' It's a checkmark so change to !
                    With Target.Font
                        .Name = "Calibri"
                        .FontStyle = "Bold"
                    End With
                    Target = "!"
                Case 33 ' it's an ! so change it to X
                    With Target.Font
                        .Name = "Calibri"
                        .FontStyle = "Bold"
                    End With
                    Target = "X"
                Case 88 ' It's an X so change it to a checkmark
                    With Target.Font
                        .Name = "Wingdings 2"
                        .FontStyle = "Bold"
                    End With
                    Target = "P" ' This is a checkmark in the Wingding 2's font
            End Select
            Application.ScreenUpdating = False
            Target.Offset(0, 999).Select
            Application.ScreenUpdating = True
        End If
    End If
    
    Exit Sub
    ErrorRoutine:
    If Err.Number = 5 Then
        ' The cell is blank so make it a checkbox
        With Target.Font
            .Name = "Wingdings 2"
            .FontStyle = "Bold"
        End With
        Target = "P" ' This is a checkmark in the Wingding 2's font
        Application.ScreenUpdating = False
        Target.Offset(0, 999).Select
        Application.ScreenUpdating = True
    End If
    End Sub

  2. #2
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Slow the user down?

    Why not use the WorkSheet_BeforeDoubleclick event instead. Yea, you need to double-click for each cycle, but that is a lot less problematic than what you are doing.

    The following increments the value in a cell. Modify to suit your needs.
    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Static count As Integer
        If Not Intersect(Target, Range("c1")) Is Nothing Then
            count = count + 1
            Target.Value2 = count
            Cancel = True ' cancels edit on the cell
        End If
    End Sub

  3. #3

  4. #4
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Slow the user down?

    Quote Originally Posted by MartinLiss View Post
    Thanks. I had tried that already but putting my code in the Worksheet_BeforeDoubleClick didn't fix the problem.
    ???
    Quote Originally Posted by MartinLiss View Post
    ... My solution to that was after the cell is selected and changed (via code) I would turn off ScreenUpdating, select a cell via code that was off-screen, and then turn screen updating back on. That works fine except if I "click"" the cell too fast. When that happens the off-screen cell is selected but I'm left there. Is there any way to prevent that?
    You fix it by not doing it. This works fine for me.

    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        If Not Intersect(Target, Range("a1:a5")) Is Nothing Then
            Select Case Asc(Target)
                Case 80 ' It's a checkmark so change to !
                    With Target.Font
                        .Name = "Calibri"
                        .FontStyle = "Bold"
                    End With
                    Target = "!"
                Case 33 ' it's an ! so change it to X
                    With Target.Font
                        .Name = "Calibri"
                        .FontStyle = "Bold"
                    End With
                    Target = "X"
                Case 88 ' It's an X so change it to a checkmark
                    With Target.Font
                        .Name = "Wingdings 2"
                        .FontStyle = "Bold"
                    End With
                    Target = "P" ' This is a checkmark in the Wingding 2's font
            End Select
            Cancel = True ' cancels edit on the cell
        End If
    End Sub
    Last edited by TnTinMN; Jun 19th, 2015 at 03:36 PM.

  5. #5

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width