Simple Excel Macro

[fedas18]

Hi,

I am trying write a simple macro that is driving me crazy.

When I run the macro, I want cell k3 to be turned to "1".

Then I want to run another macro where I change k3 to "0".

I don't know if its possible to do this in the same macro. Like if you double click it or something. But having two bottons wouldn't even be a big deal.

I just need 1 macro that changes k3 to 1 and another that changes it to 0.

Thanks!

 

[GTO]

This would go in the worksheet's module:
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeDoubleClick(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    <br>    <SPAN style="color:#00007F">If</SPAN> Target.Address = "$K$3" <SPAN style="color:#00007F">Then</SPAN><br>        Cancel = <SPAN style="color:#00007F">True</SPAN><br>        Target.Value = IIf(Target.Value = 1, 3, 1)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address(False, False) = "K3" Then
    Cancel = True
    Select Case Target.Value
        Case 0: Target.Value = 1
        Case 1: Target.Value = 0
    End Select
End If
End Sub

then double click K3.

 

[GTO]

Sorry, the 3 stuck in my head from the .Address. Try:
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeDoubleClick(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    <br>    <SPAN style="color:#00007F">If</SPAN> Target.Address = "$K$3" <SPAN style="color:#00007F">Then</SPAN><br>        Cancel = <SPAN style="color:#00007F">True</SPAN><br>        Target.Value = Abs(CLng(<SPAN style="color:#00007F">Not</SPAN> <SPAN style="color:#00007F">CBool</SPAN>(Target.Value)))<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[fedas18]

For the second macro, I'm getting an error in the line highted in red

Sub Macro67()
'
' Macro67 Macro
'
'Keyboard Shortcut: Ctrl+p
'Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address(False, False) = "K3" Then
Cancel = True
Select Case Target.Value
Case 0: Target.Value = 1
Case 1: Target.Value = 0
End Select
End If
End Sub

 

[fedas18]

First macro I'm getting an compile error: invlaid inside procedure. Any idea what that means?

Sub Macro68()
'
' Macro68 Macro
'
Option Explicit

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

If Target.Address = "$K$3" Then
Cancel = True
Target.Value = Abs(CLng(Not CBool(Target.Value)))
End If
End Sub

 

[GTO]

Where did you put the macro?

 

[fedas18]

I think I put it in VBA.

I recored a macro. Didn't do anything in that macro. Then edited that blank macro with the code you provided

 

[fedas18]

Anyone have any other suggestions? Just looking for a simple macro that changes cell K3 to 1

 

[dannac]

Vog's code works for me.