| 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
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 |
|
#5
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 |
|
#6
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 |
|
#7
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 |
|
#8
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 |
|
#9
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 |
| 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 | |||
Hybrid Mode
