| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Enter text
I have a worksheet that users will have to insert X into cells for
items to order. I would like a function that if the cell is blank then add "X" but if their is "X" in then remove it to a blank. It is like a toggle on or off. Any ideas Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Enter text
Try this in ThisWorkbook
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) If ActiveCell.Value = "X" Then ActiveCell.Value = "" ElseIf ActiveCell.Value < "X" Then If ActiveCell.Value < "" Then Exit Sub ElseIf ActiveCell.Value = "" Then ActiveCell.Value = "X" End If Else End If End Sub "rpick60" wrote in message ... I have a worksheet that users will have to insert X into cells for items to order. I would like a function that if the cell is blank then add "X" but if their is "X" in then remove it to a blank. It is like a toggle on or off. Any ideas Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Enter text
And if you want more range control... give this one a try...
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) If Selection.Row <= 10 Then If Selection.Column = 1 Then If ActiveCell.Value = "X" Then ActiveCell.Value = "" ElseIf ActiveCell.Value < "X" Then If ActiveCell.Value < "" Then Exit Sub ElseIf ActiveCell.Value = "" Then ActiveCell.Value = "X" End If Else End If End If End If End Sub "Mark Ivey" wrote in message ... Try this in ThisWorkbook Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) If ActiveCell.Value = "X" Then ActiveCell.Value = "" ElseIf ActiveCell.Value < "X" Then If ActiveCell.Value < "" Then Exit Sub ElseIf ActiveCell.Value = "" Then ActiveCell.Value = "X" End If Else End If End Sub "rpick60" wrote in message ... I have a worksheet that users will have to insert X into cells for items to order. I would like a function that if the cell is blank then add "X" but if their is "X" in then remove it to a blank. It is like a toggle on or off. Any ideas Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Enter text
We can shorten your function a little bit...
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) Cancel = True If InStr("X", ActiveCell.Value) Then _ ActiveCell.Value = Chr(88 - Asc(ActiveCell.Value & Chr(0))) End Sub Note: I added Cancel = True in the routine so that edit mode is not activated in case that option is set. Rick "Mark Ivey" wrote in message ... Try this in ThisWorkbook Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) If ActiveCell.Value = "X" Then ActiveCell.Value = "" ElseIf ActiveCell.Value < "X" Then If ActiveCell.Value < "" Then Exit Sub ElseIf ActiveCell.Value = "" Then ActiveCell.Value = "X" End If Else End If End Sub "rpick60" wrote in message ... I have a worksheet that users will have to insert X into cells for items to order. I would like a function that if the cell is blank then add "X" but if their is "X" in then remove it to a blank. It is like a toggle on or off. Any ideas Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Enter text
Following through using the technique from my previous post...
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) Cancel = True If Not Intersect(Range("A1:A10"), ActiveCell) Is Nothing Then _ If InStr("X", ActiveCell.Value) Then _ ActiveCell.Value = Chr(88 - Asc(ActiveCell.Value & Chr(0))) End Sub Rick "Mark Ivey" wrote in message ... And if you want more range control... give this one a try... Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) If Selection.Row <= 10 Then If Selection.Column = 1 Then If ActiveCell.Value = "X" Then ActiveCell.Value = "" ElseIf ActiveCell.Value < "X" Then If ActiveCell.Value < "" Then Exit Sub ElseIf ActiveCell.Value = "" Then ActiveCell.Value = "X" End If Else End If End If End If End Sub "Mark Ivey" wrote in message ... Try this in ThisWorkbook Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) If ActiveCell.Value = "X" Then ActiveCell.Value = "" ElseIf ActiveCell.Value < "X" Then If ActiveCell.Value < "" Then Exit Sub ElseIf ActiveCell.Value = "" Then ActiveCell.Value = "X" End If Else End If End Sub "rpick60" wrote in message ... I have a worksheet that users will have to insert X into cells for items to order. I would like a function that if the cell is blank then add "X" but if their is "X" in then remove it to a blank. It is like a toggle on or off. Any ideas Thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Enter text
Outstanding method to streamline this code Rick...
I bow to the master... "Rick Rothstein (MVP - VB)" wrote in message ... Following through using the technique from my previous post... Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) Cancel = True If Not Intersect(Range("A1:A10"), ActiveCell) Is Nothing Then _ If InStr("X", ActiveCell.Value) Then _ ActiveCell.Value = Chr(88 - Asc(ActiveCell.Value & Chr(0))) End Sub Rick "Mark Ivey" wrote in message ... And if you want more range control... give this one a try... Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) If Selection.Row <= 10 Then If Selection.Column = 1 Then If ActiveCell.Value = "X" Then ActiveCell.Value = "" ElseIf ActiveCell.Value < "X" Then If ActiveCell.Value < "" Then Exit Sub ElseIf ActiveCell.Value = "" Then ActiveCell.Value = "X" End If Else End If End If End If End Sub "Mark Ivey" wrote in message ... Try this in ThisWorkbook Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) If ActiveCell.Value = "X" Then ActiveCell.Value = "" ElseIf ActiveCell.Value < "X" Then If ActiveCell.Value < "" Then Exit Sub ElseIf ActiveCell.Value = "" Then ActiveCell.Value = "X" End If Else End If End Sub "rpick60" wrote in message ... I have a worksheet that users will have to insert X into cells for items to order. I would like a function that if the cell is blank then add "X" but if their is "X" in then remove it to a blank. It is like a toggle on or off. Any ideas Thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Enter text
More an "interesting alternative" as opposed to an outstanding betterment, I
would think. Rick "Mark Ivey" wrote in message ... Outstanding method to streamline this code Rick... I bow to the master... "Rick Rothstein (MVP - VB)" wrote in message ... Following through using the technique from my previous post... Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) Cancel = True If Not Intersect(Range("A1:A10"), ActiveCell) Is Nothing Then _ If InStr("X", ActiveCell.Value) Then _ ActiveCell.Value = Chr(88 - Asc(ActiveCell.Value & Chr(0))) End Sub Rick "Mark Ivey" wrote in message ... And if you want more range control... give this one a try... Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) If Selection.Row <= 10 Then If Selection.Column = 1 Then If ActiveCell.Value = "X" Then ActiveCell.Value = "" ElseIf ActiveCell.Value < "X" Then If ActiveCell.Value < "" Then Exit Sub ElseIf ActiveCell.Value = "" Then ActiveCell.Value = "X" End If Else End If End If End If End Sub "Mark Ivey" wrote in message ... Try this in ThisWorkbook Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) If ActiveCell.Value = "X" Then ActiveCell.Value = "" ElseIf ActiveCell.Value < "X" Then If ActiveCell.Value < "" Then Exit Sub ElseIf ActiveCell.Value = "" Then ActiveCell.Value = "X" End If Else End If End Sub "rpick60" wrote in message ... I have a worksheet that users will have to insert X into cells for items to order. I would like a function that if the cell is blank then add "X" but if their is "X" in then remove it to a blank. It is like a toggle on or off. Any ideas Thanks |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Enter text
Thanks for the help guys but I cannot get it to work. I do have
another program run on this worksheet and it is a Private Sub Worksheet_SelectionChange(ByVal Target As Range). I have not added it to this sub but to a new one under this one. I also need it to work in cells C8:L8 What am I missing On Dec 13, 11:21 pm, "Rick Rothstein \(MVP - VB\)" wrote: More an "interesting alternative" as opposed to an outstanding betterment, I would think. Rick "Mark Ivey" wrote in message ... Outstanding method to streamline this code Rick... I bow to the master... "Rick Rothstein (MVP - VB)" wrote in .. . Following through using the technique from my previous post... Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) Cancel = True If Not Intersect(Range("A1:A10"), ActiveCell) Is Nothing Then _ If InStr("X", ActiveCell.Value) Then _ ActiveCell.Value = Chr(88 - Asc(ActiveCell.Value & Chr(0))) End Sub Rick "Mark Ivey" wrote in message .. . And if you want more range control... give this one a try... Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) If Selection.Row <= 10 Then If Selection.Column = 1 Then If ActiveCell.Value = "X" Then ActiveCell.Value = "" ElseIf ActiveCell.Value < "X" Then If ActiveCell.Value < "" Then Exit Sub ElseIf ActiveCell.Value = "" Then ActiveCell.Value = "X" End If Else End If End If End If End Sub "Mark Ivey" wrote in message ... Try this in ThisWorkbook Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) If ActiveCell.Value = "X" Then ActiveCell.Value = "" ElseIf ActiveCell.Value < "X" Then If ActiveCell.Value < "" Then Exit Sub ElseIf ActiveCell.Value = "" Then ActiveCell.Value = "X" End If Else End If End Sub "rpick60" wrote in message ... I have a worksheet that users will have to insert X into cells for items to order. I would like a function that if the cell is blank then add "X" but if their is "X" in then remove it to a blank. It is like a toggle on or off. Any ideas Thanks- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Enter text
Sorry Guys but I got it, I did not see it was for the workbook. I
changed to the worksheet and got to work. Thanks for the help On Dec 14, 12:01 am, rpick60 wrote: Thanks for the help guys but I cannot get it to work. I do have another program run on this worksheet and it is a Private Sub Worksheet_SelectionChange(ByVal Target As Range). I have not added it to this sub but to a new one under this one. I also need it to work in cells C8:L8 What am I missing On Dec 13, 11:21 pm, "Rick Rothstein \(MVP - VB\)" wrote: More an "interesting alternative" as opposed to an outstanding betterment, I would think. Rick "Mark Ivey" wrote in message ... Outstanding method to streamline this code Rick... I bow to the master... "Rick Rothstein (MVP - VB)" wrote in .. . Following through using the technique from my previous post... Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) Cancel = True If Not Intersect(Range("A1:A10"), ActiveCell) Is Nothing Then _ If InStr("X", ActiveCell.Value) Then _ ActiveCell.Value = Chr(88 - Asc(ActiveCell.Value & Chr(0))) End Sub Rick "Mark Ivey" wrote in message .. . And if you want more range control... give this one a try... Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) If Selection.Row <= 10 Then If Selection.Column = 1 Then If ActiveCell.Value = "X" Then ActiveCell.Value = "" ElseIf ActiveCell.Value < "X" Then If ActiveCell.Value < "" Then Exit Sub ElseIf ActiveCell.Value = "" Then ActiveCell.Value = "X" End If Else End If End If End If End Sub "Mark Ivey" wrote in message ... Try this in ThisWorkbook Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) If ActiveCell.Value = "X" Then ActiveCell.Value = "" ElseIf ActiveCell.Value < "X" Then If ActiveCell.Value < "" Then Exit Sub ElseIf ActiveCell.Value = "" Then ActiveCell.Value = "X" End If Else End If End Sub "rpick60" wrote in message ... I have a worksheet that users will have to insert X into cells for items to order. I would like a function that if the cell is blank then add "X" but if their is "X" in then remove it to a blank. It is like a toggle on or off. Any ideas Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Enter text
If you look at the header for our event procedures, you will see the word
"Workbook" in it. Mark chose to use this event and I simply did the same. Using the Workbook event allows the subroutine to run from any worksheet in the workbook. If you would like to do the same, double click on ThisWorkbook in the list of projects inside the VBA editor and put the subroutine code (either Mark's or mine) in the code window that pops up. If you would like to restrict the functionality to a single worksheet, double click it in the list of projects, paste the code there (as I am assuming you have already done) and change the header to this... Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) and all should work fine then. Rick "rpick60" wrote in message ... Thanks for the help guys but I cannot get it to work. I do have another program run on this worksheet and it is a Private Sub Worksheet_SelectionChange(ByVal Target As Range). I have not added it to this sub but to a new one under this one. I also need it to work in cells C8:L8 What am I missing On Dec 13, 11:21 pm, "Rick Rothstein \(MVP - VB\)" wrote: More an "interesting alternative" as opposed to an outstanding betterment, I would think. Rick "Mark Ivey" wrote in message ... Outstanding method to streamline this code Rick... I bow to the master... "Rick Rothstein (MVP - VB)" wrote in .. . Following through using the technique from my previous post... Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) Cancel = True If Not Intersect(Range("A1:A10"), ActiveCell) Is Nothing Then _ If InStr("X", ActiveCell.Value) Then _ ActiveCell.Value = Chr(88 - Asc(ActiveCell.Value & Chr(0))) End Sub Rick "Mark Ivey" wrote in message .. . And if you want more range control... give this one a try... Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) If Selection.Row <= 10 Then If Selection.Column = 1 Then If ActiveCell.Value = "X" Then ActiveCell.Value = "" ElseIf ActiveCell.Value < "X" Then If ActiveCell.Value < "" Then Exit Sub ElseIf ActiveCell.Value = "" Then ActiveCell.Value = "X" End If Else End If End If End If End Sub "Mark Ivey" wrote in message ... Try this in ThisWorkbook Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) If ActiveCell.Value = "X" Then ActiveCell.Value = "" ElseIf ActiveCell.Value < "X" Then If ActiveCell.Value < "" Then Exit Sub ElseIf ActiveCell.Value = "" Then ActiveCell.Value = "X" End If Else End If End Sub "rpick60" wrote in message ... I have a worksheet that users will have to insert X into cells for items to order. I would like a function that if the cell is blank then add "X" but if their is "X" in then remove it to a blank. It is like a toggle on or off. Any ideas Thanks- Hide quoted text - - Show quoted text - |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Enter text
This is pretty cool, what you have going on with the X toggle.
Just curious why not use Target instead of ActiveCell, and why not fully qualify Range("A1:A10")? -- Tim Zych SF, CA "Rick Rothstein (MVP - VB)" wrote in message ... Following through using the technique from my previous post... Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) Cancel = True If Not Intersect(Range("A1:A10"), ActiveCell) Is Nothing Then _ If InStr("X", ActiveCell.Value) Then _ ActiveCell.Value = Chr(88 - Asc(ActiveCell.Value & Chr(0))) End Sub Rick "Mark Ivey" wrote in message ... And if you want more range control... give this one a try... Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) If Selection.Row <= 10 Then If Selection.Column = 1 Then If ActiveCell.Value = "X" Then ActiveCell.Value = "" ElseIf ActiveCell.Value < "X" Then If ActiveCell.Value < "" Then Exit Sub ElseIf ActiveCell.Value = "" Then ActiveCell.Value = "X" End If Else End If End If End If End Sub "Mark Ivey" wrote in message ... Try this in ThisWorkbook Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean) If ActiveCell.Value = "X" Then ActiveCell.Value = "" ElseIf ActiveCell.Value < "X" Then If ActiveCell.Value < "" Then Exit Sub ElseIf ActiveCell.Value = "" Then ActiveCell.Value = "X" End If Else End If End Sub "rpick60" wrote in message ... I have a worksheet that users will have to insert X into cells for items to order. I would like a function that if the cell is blank then add "X" but if their is "X" in then remove it to a blank. It is like a toggle on or off. Any ideas Thanks |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Enter text
This is pretty cool, what you have going on with the X toggle.
Have you already deciphered how it works (and why the Instr function test works) or was your statement a request for an explanation? Just curious why not use Target instead of ActiveCell, and why not fully qualify Range("A1:A10")? Yes, I should have used Target instead of ActiveCell. When I first developed the code, I simply used a Sub Test() procedure to do it, hence the use of ActiveCell. When I saw that you had used the DoubleClick event, I then changed to that header also, copied my working code into it and forgot all about changing ActiveCell to Target. I'm not exactly sure what you mean by "fully qualified"; could you please explain. Just so you know, the reason I am unsure of what you mean is that, as you might be able to tell from my sign-on name, I come from (well, actually, still actively participate in) the compiled VB world and, while I've been attempting to help out here in the Excel world for a number of months now, some terms and reference are still unclear to me. I am attempting to learn them as I come across them, but I am not sure I have seen the term "fully qualified" with reference to a Range yet. Rick |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Enter text
When I saw that you had used the DoubleClick event...
Not "you", of course; but, rather, Mark. Rick |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Enter text
Have you already deciphered how it works (and why the Instr function test
works) or was your statement a request for an explanation? I get it, just commenting on it. I'm not exactly sure what you mean by "fully qualified"; could you please explain. Including additional referencing that refers to a specific worksheet, e.g. Sh.Range("A1") or Target.Parent.Range("A1"). Range("A1") by itself refers to the active sheet, except in a worksheet module, where it refers to that sheet. The unqualified range reference in the Workbook_SheetBeforeDoubleClick event should not in practice cause a problem since a double-clicked cell must occur on a sheet that is active (unless there is a way to programmatically send a double-click to a non-active worksheet), but would instead be more crucial for say the Workbook_SheetChange event. (I've said sheet so many times it's beginning to morph into sounding like profanity). -- Tim Zych SF, CA "Rick Rothstein (MVP - VB)" wrote in message ... This is pretty cool, what you have going on with the X toggle. Have you already deciphered how it works (and why the Instr function test works) or was your statement a request for an explanation? Just curious why not use Target instead of ActiveCell, and why not fully qualify Range("A1:A10")? Yes, I should have used Target instead of ActiveCell. When I first developed the code, I simply used a Sub Test() procedure to do it, hence the use of ActiveCell. When I saw that you had used the DoubleClick event, I then changed to that header also, copied my working code into it and forgot all about changing ActiveCell to Target. I'm not exactly sure what you mean by "fully qualified"; could you please explain. Just so you know, the reason I am unsure of what you mean is that, as you might be able to tell from my sign-on name, I come from (well, actually, still actively participate in) the compiled VB world and, while I've been attempting to help out here in the Excel world for a number of months now, some terms and reference are still unclear to me. I am attempting to learn them as I come across them, but I am not sure I have seen the term "fully qualified" with reference to a Range yet. Rick |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Enter text
Have you already deciphered how it works (and why the Instr function test
works) or was your statement a request for an explanation? I get it, just commenting on it. Good... in that case, I'm glad you liked it. I'm not exactly sure what you mean by "fully qualified"; could you please explain. Including additional referencing that refers to a specific worksheet, e.g. Sh.Range("A1") or Target.Parent.Range("A1"). Range("A1") by itself refers to the active sheet, except in a worksheet module, where it refers to that sheet. I kind of thought that is what you might have meant... it's sort of like the path for a filename. The unqualified range reference in the Workbook_SheetBeforeDoubleClick event should not in practice cause a problem since a double-clicked cell must occur on a sheet that is active (unless there is a way to programmatically send a double-click to a non-active worksheet), but would instead be more crucial for say the Workbook_SheetChange event. As you said, I didn't see any problem with not qualifying it given how it was being used. (I've said sheet so many times it's beginning to morph into sounding like profanity). LOL Rick |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Enter text
This is pretty cool, what you have going on with the X toggle.
Have you already deciphered how it works (and why the Instr function test works) or was your statement a request for an explanation? I get it, just commenting on it. Maybe I should have mentioned earlier... one of the things I am "famous" for over in the compiled VB world are my one-liners. Rick |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to select cells in column enter data then press enter | New Users to Excel | |||
| Excel Macro to enter same text in comment box file's property box of 132 different files | Excel Programming | |||
| In Excel, option to enter text in cells the same as text boxes | Excel Worksheet Functions | |||
| Keypress for enter in text box | Excel Programming | |||
| Macro to enter formula with text | Excel Programming | |||
Linear Mode
