I don't necessarily support this alternative, and I don't think it is popular among the old schools either, but it is effective nonetheless. There is a certain Hyperlink event which could be modified to bamboozle Excel and make it work to your advantage. The trick is to create a hyperlink to the same cell from within itself, and then use the event macro.
I have attached a modified version of the same here. The green line is just
added as an embellishment. You can ignore that if not needed.
*Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
With Target.Parent
If .Address(0, 0) = "G15" Then
Target.ScreenTip = "Click to reverse direction of the motor"
.Value = Abs(CLng(Not -.Value))
End If
End With
End Sub
Regards,
Sam Mathai Chacko (GL)
*
On Sun, Oct 16, 2011 at 12:06 PM, Cab Boose <swch...@gmail.com> wrote:
> Hi Don & Sam
>
> Don. appreciate your comment. I had not made it clear what I was looking
> for. Apologies.
>
> Sam, yes it is working great with dbl click or right click. It does not
> work with a single click, which is what I prefer. In use the cell would be
> selected and then may also need to click once again to change the cell to
> requirement.
>
> Your comments would be appreciated.
>
> Thanks
>
> Charlie Harris
>
> On Sun, Oct 16, 2011 at 12:13 AM, dguillett1 <dguille...@gmail.com> wrote:
>
>> Your request was “when I SELECT the cell”. So, as Sam says, use another
>> event such as doubleclick.
>>
>> Don Guillett
>> SalesAid Software
>> dguille...@gmail.com
>>
>> *From:* Cab Boose <swch...@gmail.com>
>> *Sent:* Friday, October 14, 2011 8:32 PM
>> *To:* excel-macros@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$ Toggle a Cell value
>>
>> Hi Don and Sam
>>
>> Thanks for your input.
>>
>> I use Win7 Excel and on a laptop.
>>
>>
>> Don, your code work ok but small hiccup. The code/color changes ok when
>> I select a different cell and then come back to actual cell again. Color
>> and number change as required. However I would like to be able to leave the
>> cursor in the active cell, somethimes, and each time I tap the cell on the
>> laptop pad, it should change. That is without having to go to another
>> cell first. In other words how do you get Excel to know when you select
>> cell and then select again without moving. Maybe if we can after the first
>> selection and change of color/code Excel places the active cell into a
>> default cell nearby, and if the direction needs to be changed again I need
>> to select that cell again. Or is there a way of refreshing the g15 cell
>> without moving.
>>
>> Sam, your code brings up a run time error '13' type mismatch. The
>> color is ok by format, but does the code allow for changing the value in the
>> cell back and forth from 1 to 0 and 0 to 1 .
>>
>>
>> Thankyou all
>>
>> Charlie Harris
>>
>>
>>
>>
>>
>> On Sat, Oct 15, 2011 at 6:36 AM, Sam Mathai Chacko <samde...@gmail.com>wrote:
>>
>>> This should do it
>>>
>>>
>>> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>>>
>>> If Target.Address(0, 0) = "G15" Then
>>> Target.Value = Abs(CLng(Not -Target.Value))
>>> End If
>>>
>>> End Sub
>>>
>>> Use a conditional format for coloring the cell.
>>>
>>> If you do not want to add a conditional format (which is faster than VBA
>>> by the way), and do the coloring also through VBA, then just add the
>>> following line within the If End IF statement in the VBA above.
>>>
>>> Target.Interior.ColorIndex = 4 - Target.Value
>>>
>>> Regards,
>>>
>>> Sam Mathai Chacko
>>>
>>>
>>> On Fri, Oct 14, 2011 at 6:09 PM, dguillett1 <dguille...@gmail.com>wrote:
>>>
>>>> Right click sheet tab>view code>copy/paste this to make the changes
>>>> in col G
>>>>
>>>> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>>>> If Target.Column <> 7 Or Target.Count > 1 Then Exit Sub
>>>> If Target = 1 Then
>>>> Target = 0
>>>> Target.Interior.ColorIndex = 4
>>>> Else
>>>> Target = 1
>>>> Target.Interior.ColorIndex = 3
>>>> End If
>>>> End Sub
>>>>
>>>> Don Guillett
>>>> SalesAid Software
>>>> dguille...@gmail.com
>>>>
>>>> *From:* Cab Boose <swch...@gmail.com>
>>>> *Sent:* Friday, October 14, 2011 2:33 AM
>>>> *To:* excel-macros@googlegroups.com
>>>> *Subject:* $$Excel-Macros$$ Toggle a Cell value
>>>>
>>>> Hi eveyrone
>>>>
>>>> See attached sheet.
>>>>
>>>> For a 12 volt motor speed direction, Forward or Reverse I want to have
>>>> a value of a cell to toggle back and forth between 0 and 1 and cell color
>>>> also to change each time between green and red.
>>>>
>>>> Prefer to use a cell than use a toggle button, unless a toggle button
>>>> can change colors etc and the value of 0 or 1 from the toggle button can
>>>> be
>>>> used to export etc
>>>>
>>>> Your comments would be appreciated.
>>>>
>>>> Charlie Harris
>>>> --
>>>>
>>>> ----------------------------------------------------------------------------------
>>>> Some important links for excel users:
>>>> 1. Follow us on TWITTER for tips tricks and links :
>>>> http://twitter.com/exceldailytip
>>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>>
>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>
>>>> <><><><><><><><><><><><><><><><><><><><><><>
>>>> Like our page on facebook , Just follow below link
>>>> http://www.facebook.com/discussexcel
>>>> --
>>>>
>>>> ----------------------------------------------------------------------------------
>>>> Some important links for excel users:
>>>> 1. Follow us on TWITTER for tips tricks and links :
>>>> http://twitter.com/exceldailytip
>>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>>
>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>
>>>> <><><><><><><><><><><><><><><><><><><><><><>
>>>> Like our page on facebook , Just follow below link
>>>> http://www.facebook.com/discussexcel
>>>>
>>>
>>>
>>>
>>> --
>>> Sam Mathai Chacko
>>>
>>> --
>>>
>>> ----------------------------------------------------------------------------------
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
Sam Mathai Chacko
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Toggle Cell Value.xlsm
Description: Binary data
