I need to create barcodes in Excel. For the ID in every row of the data I want to create a barcode. The code should only get displayed when I click on the ID cell. The code should get displayed in a popup window, so I can scan it from the screen. To create the barcodes I think to use the Font Code 128, if possible.
How to create this popup?
Can one do it with user forms? Or is there something simpler? (one can not use the font code 128 in a textbox, it is not available there)
I know only some basics in VBA coding.
For now I don't need VBA code, but a simple advice what to do.
The ID Cell will be part of the range to be monitored by the Worksheet_SelectionChange event (in this example I defined this range as B2:B30)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B2:B30")) Is Nothing Then
Set rngTarget = Target
Call fnSplashScreen_Show
End If
End Sub
Then in your UserForm, here named frmSS (a splashscreen-like userform), add a Label, here named lblCode128, formatting its font as Code 128 Regular (this is the font name here), and choose an adequate font size (here I tested ok for '36'). You'll have to resize the label dimensions to achieve a good one.
The suggested code to put in a module is as follows:
Option Explicit
Public rngTarget As Excel.Range
Const intSecondsToShow As Integer = 5
Sub fnSplashScreen_Show()
Application.OnTime Now, "fnSplashScreen_Close"
Do
DoEvents
Load frmSS
frmSS.lblCode128 = rngTarget.Value
frmSS.Show
Loop Until True
End Sub
Sub fnSplashScreen_Close()
Dim datWaitTime As Date
datWaitTime = TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + intSecondsToShow)
Application.Wait datWaitTime
On Error Resume Next
Unload frmSS
End Sub
These two routines deal with the Wait method delaying the unload of the Userform for 5 seconds (see Const intSecondsToShow)