excelvbacode128

Create a barcode popup


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.

enter image description here


Solution

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