Clickable cells and macros

[PerryK]

I am working on an excel VBA program that I am trying to use clickable cells instead of command buttons. Is it possible and if so how do I make it so that if I make a column clickable the assigned macro will work only in the same row as the cell that was clicked. For example if K5 is clicked, it will post the current time in O5 and the words "IN PROGRESS" in M5. I will need to do this in about 300 rows and I am hoping there is an efficient way to do this. Below is the code I have so far for making a column clickable cells and one of the macros I was testing.

Option Explicit
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("D5:D300")) Is Nothing Then
            Call INPROGRESS1
        End If
    End If
End Sub
[\code]
The macro [code]
Sub INPROGRESS1()
'
' INPROGRESS1 Macro
'


'
    Range("M5").Select
    ActiveCell.FormulaR1C1 = "IN PROGRESS"
    Range("M6").Select
    Sheet1.Cells(5, 15).Value = Format$(Now, "hh:nn:ss")
End Sub
[\code]

Thank you for your time and assistance. I truly appreciate it.

I don't know if its important, but I do want to say I have been doing this with command buttons and someone suggested using clickable cells to be more efficient so that I don't have to make over 1200 command buttons. Thanks again

 

[Fluff]

How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Column <> 11 Then Exit Sub
   Cancel = True
   Target.Offset(, 2).Value = "IN PROGRESS"
   Target.Offset(, 4).Value = Time
End Sub

 

[PerryK]

Thank you so much that works perfectly. Is there a way I can do it for multiple columns on the same sheet, but with different outcomes? The entire sheet will have 3 buttons. Column K is TIME IN and works as you have shown me. Column L will be TIME OUT and work exactly as you have shown me but with the word COMPLETE replacing the IN PROGRESS. Column N will be a HOLD button. I tried to add the extra lines and it works just not like how I want it to

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Column < K > 11 Then Exit Sub
   Cancel = True
   Target.Offset(, 2).Value = "IN PROGRESS"
   Target.Offset(, 4).Value = Time
   If Target.Column < L > 12 Then Exit Sub
   Cancel = True
   Target.Offset(, 2).Value = "COMPLETE"
   Target.Offset(, 4).Value = Time
   If Target.Column < N > 14 Then Exit Sub
   Cancel = True
   Target.Offset(, -1).Value = "PARTIAL HOLD"
   Target.Offset(, 3).Value = Time
End Sub

This makes put in all the data at the same time no matter which one of those columns I click in. How can I make it so only the data from one column shows up when that column is clicked? Thank you again.

 

[Fluff]

Is this what you want

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Column = 11 Then
      Cancel = True
      Target.Offset(, 2).Value = "IN PROGRESS"
      Target.Offset(, 4).Value = Time
   ElseIf Target.Column = 12 Then
      Cancel = True
      Target.Offset(, 2).Value = "COMPLETE"
      Target.Offset(, 4).Value = Time
   ElseIf Target.Column = 14 Then
      Cancel = True
      Target.Offset(, -1).Value = "PARTIAL HOLD"
      Target.Offset(, 3).Value = Time
   End If
End Sub

 

[smcaulay]

I am still very new to VBA, but I use the code below to select a cell (using single click), copy its contents and copy to another sheet and works well.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cancel = True
    If Selection.Count = 1 Then
        If Not Application.Intersect(Target, Sheets("Start_Dev").Range("Table69[Supplier]")) Is Nothing Then
            'Sheets("Start_Dev").Unprotect
            Target.Copy
            'Sheets("Start_Dev").Protect
            Sheets("CheckList").Select
            Sheets("CheckList").Range("A1:B1").Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
            Sheets("CheckList").Range("B22").Rows.AutoFit
            Sheets("CheckList").Range("B23").Rows.AutoFit
        End If
    End If
End Sub

 

[Fluff]

@smcaulay
that code has (almost) nothing to do with what the OP is asking for.

 

[PerryK]

Thank you so much that works perfectly, I truly appreciate your guidance.

 

[Fluff]

Glad to help & thanks for the feedback

 

[PerryK]

Fluff I was slightly premature. I used your code in my test book and it worked perfectly, when I put it in my actual excel book I am getting a "runtime error 424 object required" when I click on debug line 13 is highlighted. Please advise.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Column = 11 Then
      Cancel = True
      Target.Offset(, 2).Value = "IN PROGRESS"
      Target.Offset(, 4).Value = Time
   ElseIf Target.Column = 12 Then
      Cancel = True
      Target.Offset(, 1).Value = "COMPLETE"
      Target.Offset(, 4).Value = Time
   ElseIf Target.Column = 14 Then
      Cancel = True
      Target.Offset(, -1).Value = "PARTIAL HOLD"
      Target.Offset(, 3).Value = Time
   End If
End Sub

Everything is the same except i had to change line 8 from a 2 to a 1 as I was off by a column, otherwise nothing is changed. Thank you again.

 

[Fluff]

There's nothing wrong with the code.
Do you have any other Event code in that sheet?
Also does it only happen if you double click col N, or does it happen in the other 2 columns?