Archive for Userforms

Add Label to Userform Programmatically

Read the rest of this entry »

Advertisements

Leave a Comment

Archives

Leave a Comment

Create UserForm at Runtime using Code

– Selva V Pasupathy, HSBC Global Resourcing, Hyderabad

The following code, I found at http://www.eng-tips.com, is the process to create a UserForm dynamically using a macro in VBA.

  1. From the VBE (open Excel and hit ALT+F11)you need to set a reference to the extensibility add-in. To do this, go to Tools/References and find the add-in Micrsoft
    Visual Basic for Applications Extensibility
  2. Create a macro module in the project file. To do this, right-click on VBAProject and
    select Insert->Module.
  3. Right-click the module and select View Code.
  4. Paste this code into the window:

Option Explicit

Sub MakeForm()
  Dim TempForm As Object ' VBComponent
  Dim FormName As String
  Dim NewButton As MSForms.CommandButton
  Dim TextLocation As Integer
  '   ** Additional variable
  Dim X As Integer
  
'Locks Excel spreadsheet and speeds up form processing
  Application.VBE.MainWindow.Visible = False
  Application.ScreenUpdating = False
  
'Create the UserForm
  Set TempForm = ThisWorkbook.VBProject.VBComponents. _
            Add(vbext_ct_MSForm)
  
'Set Properties for TempForm
  With TempForm
    .Properties("Caption") = _
        "HSBC Global Resourcing "
    .Properties("Width") = 400
    .Properties("Height") = 300
  End With
  FormName = TempForm.Name
  
'Add a CommandButton
  Set NewButton = TempForm.Designer.Controls _
    .Add("forms.CommandButton.1")
  With NewButton
    .Caption = "Click Me"
    .Width = 60
    .Height = 24
    .Left = TempForm.Properties("InsideWidth") - 2 - 60
    .Top = TempForm.Properties("insideheight") - 2 - 24
  End With
'Add an event-hander sub for the CommandButton
  With TempForm.CodeModule
'** Delete This: TextLocation = _
      .CreateEventProc("Click","CommandButton1")
  
'** Add/change next 5 lines
'This code adds the commands/event handlers to the form
    X = .CountOfLines
    .InsertLines X + 1, _
          "Sub CommandButton1_Click()"
    .InsertLines X + 2, _
          "MsgBox ""Hello!"""
    .InsertLines X + 3, "Unload Me"
    .InsertLines X + 4, "End Sub"
  End With
  
  '   Show the form
  VBA.UserForms.Add(FormName).Show
  '
  '   Delete the form
  ThisWorkbook.VBProject.VBComponents. _
          Remove VBComponent:=TempForm
End Sub


VBE Programming – www.vbaexpress.com

Leave a Comment

Clear Controls on Userform

– Selva V Pasupathy, HSBC Global Resourcing, Hyderabad
The following code can be used to clear all textboxes and comboboxes on userform.

HOW SHOULD I USE THE FOLLOWING CODE

Private Sub Clear_all()
    For I = 0 To UserForm1.Controls.Count - 1

        If UCase(TypeName(UserForm1.Controls(I))) = "TEXTBOX" Then
            UserForm1.Controls(I).Value = ""
        End If
        If UCase(TypeName(UserForm1.Controls(I))) = "COMBOBOX" Then
            UserForm1.Controls(I).Value = ""
        End If
    Next I
End Sub

Leave a Comment

Display Userform with no Title Bar (Splash Screen)

– Selva V Pasupathy, HSBC Global Resourcing, Hyderabad

Normally when a userform is initiated you would see it with a blue title bar by default everytime. But if you want to use the userform as a title form, then one would be interested in removing the titlebar. One more userful Tip I came across at Colo’s Excel Junk Room – Masaru Kaji. The following code shows userform with no titlebar and closes after few seconds.

Picture on a userform with no title bar would show like this


HOW SHOULD I USE THE FOLLOWING CODE

Copy the following code in standard code module.

'///place these procedures on a standard module
Option Explicit

Public Const GWL_STYLE = -16
Public Const WS_CAPTION = &HC00000
Public Declare Function GetWindowLong _
                       Lib "user32" Alias "GetWindowLongA" ( _
                       ByVal hWnd As Long, _
                       ByVal nIndex As Long) As Long
Public Declare Function SetWindowLong _
                       Lib "user32" Alias "SetWindowLongA" ( _
                       ByVal hWnd As Long, _
                       ByVal nIndex As Long, _
                       ByVal dwNewLong As Long) As Long
Public Declare Function DrawMenuBar _
                       Lib "user32" ( _
                       ByVal hWnd As Long) As Long
Public Declare Function FindWindowA _
                       Lib "user32" (ByVal lpClassName As String, _
                       ByVal lpWindowName As String) As Long
'____________________________________________________________
Sub Form_Show()
    'Hide Excel
    Application.Visible = False
    'To close a form automatically
    Application.OnTime Now, "Form_Close"
    UserForm1.Show
End Sub
'____________________________________________________________
Sub Form_Close()
     'To close a form automatically
    Dim datWaitTime As Date
    datWaitTime = TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 3)
    Application.Wait datWaitTime
    Unload UserForm1
    Application.Visible = True
End Sub
'____________________________________________________________
Sub HideTitleBar(frm As Object)
    Dim lngWindow As Long
    Dim lFrmHdl As Long
    lFrmHdl = FindWindowA(vbNullString, frm.Caption)
    lngWindow = GetWindowLong(lFrmHdl, GWL_STYLE)
    lngWindow = lngWindow And (Not WS_CAPTION)
    Call SetWindowLong(lFrmHdl, GWL_STYLE, lngWindow)
    Call DrawMenuBar(lFrmHdl)
End Sub
'____________________________________________________________

Copy following code into the userform code.

'//Place these procedures on the UserForm1 module
Option Explicit
Private Sub UserForm_Initialize()
    HideTitleBar Me
End Sub
'____________________________________________________________
Private Sub UserForm_Click()
'Close this userform
    Unload Me
End Sub
'____________________________________________________________


To download Example file CLICK HERE


Leave a Comment

Saving userform in Excel as an image

-Selva V Pasupathy, HSBC Global Resourcing, Hyderabad 1
Original source: Colo’s Excel Junk Room – Masaru Kaji

Last week I was working on an excel application where I wanted several images of the userform at various stages so that I can prepare SOP for documentation. It was difficult for me because on my system Print Screen button was not working and I did not know how to activate that. While browsing hte net and going through colo;s junk room, I came across the code below which saves a userform as an image when you double click on the userform. With API, this code pastes an image of the form into a worksheet of the new workbook, then save it as a HTML file. When the Excel workbook is saved as a html file, all image files will be placed in the different folder.

HOW SHOULD I USE THE FOLLOWING CODE

Place the following code in a userform module.

Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, _
                                              ByVal bScan As Byte, _
                                              ByVal dwFlags As Long, _
                                              ByVal dwExtraInfo As Long)
Private Const VK_LMENU = &HA4
Private Const VK_SNAPSHOT = &H2C
Private Const VK_CONTROL = &H11
Private Const VK_V = &H56
Private Const VK_0x79 = &H79
Private Const KEYEVENTF_EXTENDEDKEY = &H1
Private Const KEYEVENTF_KEYUP = &H2
'__________________________________________________
Private Sub UserForm_DblClick _
                    (ByVal Cancel As MSForms.ReturnBoolean)
    Dim sAppOs As String
    Dim wks As Worksheet
    'get oparating system
    sAppOs = Application.OperatingSystem

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    If Mid(sAppOs, 18, 2) = "NT" Then
    ' WinNT,Windows2000,WindowsXP - Using Win32API
Call keybd_event(VK_LMENU, VK_V, _
                          KEYEVENTF_EXTENDEDKEY, 0)
Call keybd_event(VK_SNAPSHOT, VK_0x79, _
                          KEYEVENTF_EXTENDEDKEY, 0)
Call keybd_event(VK_LMENU, VK_V, _
                          KEYEVENTF_EXTENDEDKEY Or _
                          KEYEVENTF_KEYUP, 0)
Call keybd_event(VK_SNAPSHOT, VK_0x79, _
                          KEYEVENTF_EXTENDEDKEY _
                          Or KEYEVENTF_KEYUP, 0)
    Else
    ' Windows95,Windows98,WindowsME
Call keybd_event(VK_SNAPSHOT, 0, _
                   KEYEVENTF_EXTENDEDKEY, 0)
Call keybd_event(VK_SNAPSHOT, 0, _
                   KEYEVENTF_EXTENDEDKEY Or KEYEVENTF_KEYUP, 0)
    End If
    DoEvents
    Unload Me
    Set wks = Workbooks.Add.Sheets(1)
    Application.Goto wks.Range("A1")
    ActiveSheet.Paste
    wks.SaveAs Filename:="C:\myfile.htm", FileFormat:=xlHtml
    wks.Parent.Close False

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "Have a look at C:\myfile.files folder."
End Sub
'__________________________________________________

To download example file CLICK HERE

Leave a Comment

Displaying A Chart In A Userform

– Selva V Pasupathy, HSBC Global Resourcing

Original Source: John Walkenbach

I was pretty impressed when I came across a page where John has mentioned how you can use userform to show all the chrts in a spreadsheet on a userform and you can move betwee different charts with the help of a commandbutton.

Image showing how your userform with chart will look like

HOW SHOULD I USE THE FOLLOWING CODE

Copy the code below and paste in code section of a userform

Dim ChartNum As Integer

Private Sub UserForm_Initialize()
    ChartNum = 1
    UpdateChart
End Sub

Private Sub PreviousButton_Click()
    If ChartNum = 1 Then ChartNum = 3 Else ChartNum = ChartNum - 1
    UpdateChart
End Sub

Private Sub NextButton_Click()
    If ChartNum = 3 Then ChartNum = 1 Else ChartNum = ChartNum + 1
    UpdateChart
End Sub

Private Sub CloseButton_Click()
    Unload Me
End Sub

Private Sub UpdateChart()
    Set CurrentChart = Sheets("Charts").ChartObjects(ChartNum).Chart
    CurrentChart.Parent.Width = 300
    CurrentChart.Parent.Height = 150

'   Save chart as GIF
    Fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
    CurrentChart.Export FileName:=Fname, FilterName:="GIF"

'   Show the chart
    Image1.Picture = LoadPicture(Fname)
End Sub

Copy the code below and paste in a code module.

Sub ShowChart()
    UserForm1.Show
End Sub

Download the example file from here CLICK HERE

Leave a Comment

Older Posts »