Macro fix for Check Mark

Posted on 2014-01-11
Last Modified: 2014-01-12
EE Pros,

I have a slight problem with a Macro that I have where some change I have made has caused it to issue a "Type Mismatch" error.  I have attached the file....please take a look.  When you double click one of the black boxes, you automatically get a "check mark" that fills the box.  That is presently where I'm receiving the error.  It's minor; but I don't know how to troubleshoot it.

Thank you in advance,

Question by:Bright01
LVL 22

Assisted Solution

Flyster earned 100 total points
ID: 39773986
You get a type mismatch error because the routine "Private Sub Worksheet_BeforeDoubleClick" makes the value of the target cell "=Value". So when your code gets to this line, "Total = Total + .Offset(0, 5).Value", it is trying to add text to text. The simple solution is to rem out that line. (Place a single quote in front of the line of code.) I wasn't able to figure out what number or formula the code was trying to produce.

LVL 81

Accepted Solution

byundt earned 400 total points
ID: 39773996
I modified your Worksheet_BeforeDoubleClick sub using structured indenting (so I could follow the logic). I also used an ElseIf to eliminate the need for one If block. And I turned off events to avoid triggering the Worksheet_Change event sub when cell values were cleared or set.

You should note the statement where I commented "Value is not a defined name". This causes a #REF! error value to be placed in column J, which in turn caused the run-time error Type Mismatch that you were complaining about (in the sumup sub). Flyster also pointed out this same statement.

Please discuss how you want to handle the formula in column J, as I just put something in there to avoid the error.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Column = 5 Then

    Application.EnableEvents = False
    With ActiveCell
        If .Value = "P" Then
            .Value = ""
            .Offset(0, 5).ClearContents
        ElseIf .Offset(0, 1).Value <> "" Then
            .Font.Name = "Wingdings 2"
            .Value = "P"
            .Offset(0, 5).FormulaR1C1 = ""            '"=Value"    'Value is not a defined name
        End If
    End With
    'These two lines below make it a realtime update vs. using a button and the sub "sumup"
    Cancel = True
    Application.EnableEvents = True
End If
End Sub

Open in new window

I also modified the sumup sub by using structured indenting so I could follow the logic, and by adding a test for an error value in the statement that caused the run-time error
Total = Total + .Offset(0, 5).Value
Sub sumup()
Const Input_Range = 9
Const Output_Range = 3
Const Output_CS = 17
Const Output_CE = 18
Const Input_CM = 5

Dim O_R As Long
'Output Row
Dim I_R As Long
'Input Row
Dim Total As Double


'**** Clear the previous output ****
O_R = Cells(Rows.Count, Output_CS).End(xlUp).Row
If O_R >= Output_Range Then
    'Column and Row Output
    Range(Cells(Output_Range, Output_CS), Cells(O_R, Output_CE)).ClearContents
End If
'Column Explanation
O_R = Output_Range

'Where the checkmark is
I_R = Cells(Rows.Count, Input_CM).End(xlUp).Row
If I_R >= Input_Range Then
    'Input row
    For I = Input_Range To I_R
        With Cells(I, Input_CM)
            If .Value = "P" Then
                'Output results here
                Cells(O_R, Output_CS).Value = .Offset(0, 1).Value
                Cells(O_R, Output_CE).Formula = "=" & .Offset(0, 5).Address
                Cells(O_R, Output_CE).NumberFormatLocal = _
                        "_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"
                If Not IsError(.Offset(0, 5).Value) Then Total = Total + .Offset(0, 5).Value
                O_R = O_R + 1
            End If
        End With
    Cells(Output_Range - 1, Output_CS).Value = "Total"
    With Cells(Output_Range - 1, Output_CE)
        .FormulaR1C1 = "=SUM(R[1]C:R[" & O_R & "]C)"
        .NumberFormatLocal = _
                "_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"
    End With
    'MsgBox "Please select what you wanted! Thanks!"
End If

End Sub

Open in new window


Author Closing Comment

ID: 39774943
Thanks guys!  The code that byundt provided fixed the issue.  I'm integrating it now.  The issue with the other cells is work in process and I've got to get further down the path before matching up the values that emerge in the model.  

Fixing one challenge at a time makes for good Excel education and training!

Thanks for the Teamwork on this one.


Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question