Clickable cells and macros

PerryK

New Member
Joined
May 8, 2018
Messages
27
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.

Code:
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
How about
Code:
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
 
Upvote 0
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
Code:
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.
 
Upvote 0
Is this what you want
Code:
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
 
Upvote 0
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.

Code:
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
 
Upvote 0
@smcaulay
that code has (almost) nothing to do with what the OP is asking for.
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
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.
Code:
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,581
Messages
6,114,461
Members
448,573
Latest member
BEDE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top