Macro to Enter text

[rpick60]

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

 

[Mark Ivey]

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

 

[Mark Ivey]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

Outstanding method to streamline this code Rick...

I bow to the master...

 

[Rick Rothstein \(MVP - VB\)]

More an "interesting alternative" as opposed to an outstanding betterment, I
would think.

Rick

 

[rpick60]

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

 

[rpick60]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Tim Zych]

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")?

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

When I saw that you had used the DoubleClick event...

Not "you", of course; but, rather, Mark.

Rick

 

[Tim Zych]

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).

 

[Rick Rothstein \(MVP - VB\)]

Have you already deciphered how it works (and why the Instr function test

I get it, just commenting on it.

Good... in that case, I'm glad you liked it.

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

 

[Rick Rothstein \(MVP - VB\)]

This is pretty cool, what you have going on with the X toggle.

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