PDA

View Full Version : Run Macro when a character is entered



Emoncada
03-19-2015, 10:01 AM
I use a barcode Scanner would like for a macro to run when it detects the second "*"
The barcodes usually start with a "*" and also end with a "*".

Is it possible for it to know when the second "*" is entered to run a macro?

MINCUS1308
03-19-2015, 10:28 AM
.

MINCUS1308
03-19-2015, 10:34 AM
.

mperrah
03-19-2015, 10:48 AM
You can run a macro on a sheet change event within a specific row or column.
This code takes 4 specific key strokes and alters the value, uses sendkeys.
It moves the focus to the next cell with offset select after each key stroke



Option Explicit
Sub onkeyOn()

With Application
.Calculate
.ScreenUpdating = False
.Calculation = xlCalculationManual
.OnKey "1", "action_p"
.OnKey "2", "action_f"
.OnKey "3", "action_n"
.OnKey "4", "action_"
End With
End Sub
Sub onkeyOff()
With Application
.Calculate
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.OnKey "1"
.OnKey "2"
.OnKey "3"
.OnKey "4"
End With
End Sub
Sub action_p()
If Selection.Count > 1 Then Exit Sub
Call insert_letter("p")
End Sub
Sub action_f()
If Selection.Count > 1 Then Exit Sub
Call insert_letter("f")
End Sub
Sub action_n()
If Selection.Count > 1 Then Exit Sub
Call insert_letter("n")
End Sub
Sub action_()
If Selection.Count > 1 Then Exit Sub
Call insert_letter("")
End Sub
Sub insert_letter(vletter As String)
If ActiveSheet.Name = "Data" Then
' if not inside target - on target sheet
If ActiveCell.Column < 18 _
Or ActiveCell.Column > 44 _
Or ActiveCell.Row > 1000 Then
SendKeys "{F2}"
If vletter = "p" Then
vletter = "1"
ElseIf vletter = "f" Then
vletter = "2"
ElseIf vletter = "n" Then
vletter = "3"
ElseIf vletter = "" Then
vletter = "4"
End If
ActiveCell.Value = vletter
SendKeys "{F2}"
Else
' if not outside target
ActiveCell.Value = vletter
ActiveCell.Offset(, 1).Select
End If
ElseIf ActiveSheet.Name <> "Data" Then
' if not active sheet data
SendKeys "{F2}"
If vletter = "p" Then
vletter = "1"
ElseIf vletter = "f" Then
vletter = "2"
ElseIf vletter = "n" Then
vletter = "3"
ElseIf vletter = "" Then
vletter = "4"
End If
ActiveCell.Value = vletter
SendKeys "{F2}"
End If
End Sub

mperrah
03-19-2015, 10:55 AM
This one puts a check box (Marlett font letter "a") on click
place code in sheet (not module)



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim CheckmarkCells As Range

Set CheckmarkCells = Range("A3:A1000")

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, CheckmarkCells) Is Nothing Then
With Target
If .Font.Name = "Arial" Then
.Font.Name = "Marlett"
.Value = "a"
.Offset(0, 1).Select
Else
If .Font.Name = "Marlett" And .Value = "a" Then
.ClearContents
.Font.Name = "Arial"
.Offset(0, 1).Select
End If
End If
End With
End If

End Sub

Emoncada
03-19-2015, 10:58 AM
Sorry I forgot to mention, I need this to work on a Userform not from a spreadsheet.

MINCUS1308
03-19-2015, 11:00 AM
.

MINCUS1308
03-19-2015, 11:22 AM
this code should be placed in the userform module

This is good for a userform named UserForm1 and a input box named TextBox1


Private Sub TextBox1_Change()

If Len(Trim(CStr(UserForm1.TextBox1.Value))) > 2 Then
If Left(Trim(CStr(UserForm1.TextBox1.Value)), 1) = "*" And Right(Trim(CStr(UserForm1.TextBox1.Value)), 1) = "*" Then
'YOUR MACRO GOES HERE
MsgBox "Something was entered with a * on either side of it." 'AFTER YOU ENTER YOU MACRO DELETE THIS LINE
End If
End If

End Sub

MINCUS1308
03-23-2015, 09:19 AM
if one of these solutions solved your problem this thread should be marked as solved by going to the top of the thread and clicking 'Thread Tools' and then selecting 'Solved'.