Archive for VBA Advanced
November 16, 2008 at 20:22
· Filed under Automation, Charting, Downloads, Formatting, Tutorials, Userforms, VBA, VBA Advanced, VBA Code Library, VBA Examples
Permalink
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 www.eng-tips.com, is the process to create a UserForm dynamically using a macro in VBA.
- 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
- Create a macro module in the project file. To do this, right-click on VBAProject and
select Insert->Module.
- Right-click the module and select View Code.
- 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
Permalink
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.
|
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
Permalink
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
Permalink
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
|
Permalink
