CODEWITHSUNDEEP
excelvba
shahin rahimi
shahin rahimi

Reputation: 71

what the correct if-condition for my excel file?

I want the specific cells to be functioning by double-clicking and if this cell is on the first column, and row bigger than 12.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Target.Columns = 1 And Target.Row >= 12 Then
        dateVariable = CalendarForm.GetDate
        Target = dateVariable
    End If

End Sub

but it seems the condition that I mentioned aren't correct. cuz code working without this condition properly.

Upvotes: 0

Views: 75

Answers (3)

CharlesPL
CharlesPL

Reputation: 331

As I can't comment on @Mathieu's answer (not enough reputation yet), you should also check if you only have one cell selected. This might not be much useful in case of a double click event, but, there will be a time that you will reuse this code to another intent (maybe for the Worksheet_SelectionChange event), where this event can be fired by a multiple selection. In this case, the Column value will return the left most column. If it fits the .Column =1 criteria, then all your selection will be filled with the date, which might be frustrating if you loose data in your table.

So, remember to simply add And Target.Count = 1 to your If statement.

PS : It's good practice to force the VBA IDE to required explicit variable declaration, I'm surprise Mathieu didn't point this out. Also, your use of the dateVariable variable is not necessary. Just use Target = CalendarForm.GetDate or even better : Target.Value = CalendarForm.GetDate

Upvotes: 1

Mathieu Guindon
Mathieu Guindon

Reputation: 71207

To supplement @BigBen's answer...

Range.Columns returns a Range representing the columns in the specified range (here Target). By checking If Target.Columns = 1 you're making an implicit default member call against the returned Range object, i.e. If Target.Columns.[_Defaut] = 1, which yields Target.Columns.Value, which is a 2D variant array - which can't be compared to a 1 literal.

Thus, the error you're getting is a type mismatch error, because VBA can't compare a 2D variant array to an integer literal and successfully evaluate a result.

You want Range.Column (singular), which returns the column number for the left-most cell in the specified Range.

Upvotes: 2

BigBen
BigBen

Reputation: 50007

You want Target.Column, not .Columns.

From the documentation:

  • Range.Column property:

    Returns the number of the first column in the first area in the specified range.

  • Range.Columns property:

    Returns a Range object that represents the columns in the specified range.

Upvotes: 1

Related Questions