Archive for VBA Advanced

Add Label to Userform Programmatically

November 29, 2008 at 22:55 · Filed under Automation, Userforms, VBA Advanced, VBA Examples

Read the rest of this entry »

Leave a Comment

Archives

November 16, 2008 at 20:22 · Filed under Automation, Charting, Downloads, Formatting, Tutorials, Userforms, VBA, VBA Advanced, VBA Code Library, VBA Examples

  1. ActiveX Programming
  2. Add an Image to Cell Comments
  3. Add Sheet Navigation CommandBar to an Excel file or Application
  4. Auto-Generate a File Name
  5. Automate File Download from Internet
  6. Automate WebLogin…
  7. Convert Decimal to RGB Color Value
  8. Copy Ranges from Different Sheets into one Sheet
  9. Create HyperlinkObject on Sheet
  10. Create LogFile for Workbook Open
  11. Create Sheet “Table of Contents”
  12. Create ShortCut Link at the desired location
  13. Create UserForm at Runtime using Code
  14. Display Userform with no Title Bar (Splash Screen)
  15. Downloads
  16. Downloads from www.jkp-ads.com
  17. Export a Text File with Comma and Quote Delimiters
  18. Get elements from HTML form
  19. Get FileName with Path from User
  20. Get FileNames with Path from User
  21. Hyperlink Elements in HTML
  22. Ken’s Knowledge Base submissions
  23. Login to Rediffmail
  24. Move a File
  25. Multiple Cell Concatenation
  26. Other Site Links
  27. Read CSV Using VBA techniques
  28. Remove Unprintable Character & Spaces from cells
  29. Rename a File
  30. Rename Files using Codes (VBA)
  31. Resources
  32. Resources
  33. Search Folders and Subfolders for files
  34. Show Status on Userform While executing Code
  35. Site Contents
  36. socko.wordpress.com — Technorati Search
  37. Sort WorkSheets by Name
  38. Sorting Text with Worksheet Formula
  39. test
  40. Use of vbScript in Database Applications
  41. Userform Resizer – VBA
  42. Userforms
  43. VBA Code Library
  44. vbaX KB Links
  45. Verify that a File Exists
  46. Web Forms manipulated by Visual Basic
  47. Write to & Read from Sequential Text Files

Leave a Comment

Create UserForm at Runtime using Code

October 23, 2008 at 01:53 · Filed under Userforms, VBA Advanced, VBA Examples

– 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

Display Userform with no Title Bar (Splash Screen)

September 1, 2008 at 16:46 · Filed under Userforms, VBA Advanced, VBA Examples

– 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

September 1, 2008 at 16:09 · Filed under Userforms, VBA Advanced, VBA Examples

-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

Code to Create FileHandle on Users’ Desktop

August 29, 2008 at 03:25 · Filed under VBA Advanced, VBA Examples

by Selva V Pasupathy, HSBC Global Resourcing, Hyderabad
While at my workplace, I came across a situation when to get input from users, we had to create a file but without giving physical access to the users to that file. The code below helps to create a vbs file on desktop and you may ask the users to click on the vbs file to open the workbook and they can start the input through userform. I set application visible property as false, so user will be able to see only userform, and it will make you get a feel of a standalone application. I am not sure whether this will be a successful attempt, but there is nothing wrong in giving this a try. 

Sub Create_Opener()
Dim fso, txt As Object
Dim myFile As String
Dim i As Integer
  Call Get_CodeLines
  myFile = File_on_DeskTop
  Set fso = CreateObject("scripting.filesystemobject")
  Set txt = fso.createtextfile(myFile, 2, True)
  For i = 1 To 7
    txt.WriteLine CodeLines(i)
  Next i
  txt.Close
  Set txt = Nothing
  Set fso = Nothing
  MsgBox "A Handle for this File has been " & _
        "successfully created on desktop " & _
        "with filename " & vbNewLine & _
        File_on_DeskTop
End Sub


Private Sub Get_CodeLines()
CodeLines(1) = "'  The following lines of " & _
                  the codes act as an Opener to this file"
CodeLines(2) = "dim xlApp"
CodeLines(3) = "dim myFile"
CodeLines(4) = "myFile=" & Chr(34) & ThisWorkbook.Path & "\" & _
                  ThisWorkbook.Name & Chr(34)
CodeLines(5) = "set xlApp= CreateObject(" & Chr(34) & _ 
                 "excel.application" & Chr(34) & ")"
CodeLines(6) = "xlApp.workbooks.open(myFile)"
CodeLines(7) = "xlApp.VISIBLE=false"
End Sub

Function File_on_DeskTop() As String
File_on_DeskTop = CreateObject("WScript.Shell"). _
                   SpecialFolders("Desktop") & _
                   "\" & "DataInput.vbs"
End Function

Leave a Comment