Re: $$Excel-Macros$$ Toggle a Cell value
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.comwrote: 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.comwrote: 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.comwrote: 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.comwrote: Right click sheet tabview codecopy/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
Re: $$Excel-Macros$$ Toggle a Cell value
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.comwrote: 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 tabview codecopy/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
Re: $$Excel-Macros$$ Toggle a Cell value
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.comwrote: 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.comwrote: Right click sheet tabview codecopy/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
Re: $$Excel-Macros$$ Toggle a Cell value
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 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 tabview codecopy/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 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
Re: $$Excel-Macros$$ Toggle a Cell value
Right click sheet tabview codecopy/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 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
Re: $$Excel-Macros$$ Toggle a Cell value
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 tabview codecopy/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 CellToggle.xls Description: MS-Excel spreadsheet
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.comwrote: 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 tabview codecopy/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
Re: $$Excel-Macros$$ Toggle a Cell value
Charlie, the reason why it throws an exception is because the cell should have either 1 or 0 in the first place. You probably are copying the formula over to your workbook. If yes, just put a 0 in G15 before you start using the macro. FOr your query regarding selection of cell, pls download the attachment. Have used the BeforeRightClick event, as well as the DoubleClick event. Either one event should trigger the macro, which means you don't have to select another cell and come back for the macro to trigger. Regards, Sam Mathai Chacko (GL) On Sat, Oct 15, 2011 at 3:32 AM, Cab Boose swch...@gmail.com wrote: 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.comwrote: 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 tabview codecopy/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
