1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel DoubleClick to add additional data to a cell

Discussion in 'Business Applications' started by blass3175, Aug 5, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. blass3175

    blass3175 Thread Starter

    Joined:
    Jun 18, 2013
    Messages:
    18
    Hi all,
    I am having problems with code.
    What I want to do is add a check mark to the right of an integer in a cell when I double click on the cell. I want to repeat the check mark addition indefinitely after the integer (integer, check mark, check mark, check mark, check mark, etc). The check mark I want is the uppercase letter "P" in Windings2 font.
    I want to do this in any cell in Column C (3) that already contains any integer between 1 and 1000.
    I am basing this on code that I already used in another post that I had: Excel Input box question, posted on July 2. http://forums.techguy.org/business-applications/1102607-excel-inputbox-question.html#post8727301
    What I'm having problems doing is defining the integers using Dim code. I want to define "check" as any integer between 1 and 1000. I think once I get the syntax right, the code (hopefully) will work.
    Any help would be greatly appreciated.
    Thanks!
     

    Attached Files:

  2. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Hi

    try this and see if it suits....

    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     
        If Target.Row < 1000 And Target.Column = 3 Then
            Cancel = True
            If Target.Value > 1 And Target.Value < 1000 Or Right(Target.Value, 1) = "P" Then
                ActiveCell.FormulaR1C1 = ActiveCell & "P"
                Selection.Font.Name = "Calibri"
                    For i = 1 To Len(ActiveCell)
                        If Mid(ActiveCell, i, 1) = "P" Then
                              With ActiveCell.Characters(Start:=i, Length:=1).Font
                                  .Name = "Wingdings 2"
                                  .FontStyle = "Bold"
                              End With
                        End If
                    Next i
            End If
        End If
    End Sub
     
  3. blass3175

    blass3175 Thread Starter

    Joined:
    Jun 18, 2013
    Messages:
    18
    It works!!
    Thanks again XCubed, you are the greatest!!
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Remember, if you want to include the values of 1 and 1000, not just everything in between, you'd need to adjust this line slightly, like so...
    Code:
            If Target.Value >= 1 And Target.Value <= 1000 Or Right(Target.Value, 1) = "P" Then
    Also, since we're using a worksheet event, this line is somewhat inefficient...
    Code:
    ActiveCell.FormulaR1C1 = ActiveCell & "P"
    ... and should be ...
    Code:
    Target.Value = Target.Value & "P"
    Same with the next line...
    Code:
    Selection.Font.Name = "Calibri"
    There's no need to use the Selection object here when you already have the Target object (for all references of ActiveCell and Selection).

    Also, don't forget to declare your variables...
    Code:
    Dim i As Long
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1105348

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice