Hi Sam Works very well. Thankyou Sam and Don for your inputs.
REgards Charlie On Sun, Oct 16, 2011 at 9:55 PM, Sam Mathai Chacko <samde...@gmail.com>wrote: > 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 > -- ---------------------------------------------------------------------------------- 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
