Category Archives: Tutorials

Create An Excel VBA UserForm

Create An Excel VBA UserForm

OK, so let’s begin. We will create an Excel VBA UserForm, an Excel Trial Dialog  with information icon and timer enabled OK button, and then add in the VBA Code.

  • First Open Excel and press ALT+F11 to enter the VBA Editor.
  • Go to the File Menu and choose Insert–>UserForm.  A window will open with a blank palette for the Excel VBA Userform
  • Name the new UserForm MsgBoxCountdown and set its Height property to 132 and its Width property to 242.
  • Add one Label to the Excel VBA UserForm and name it lbTrialMsg
  • Set its Caption property to This Message only appears in the Trial Version of XXXX (without the quotes) – this will be our Trial or Nag Message to the user. We will prefix this Caption with a passed in bespoke Message.
  • Add one further Label to the Excel VBA UserForm and name it lbCountDown
  • Set its Caption property to This Trial Dialog can be closed in (without quotes) – this will be our Countdown Message to the user.
  • Now add an Image Control and load in an image resembling a Question Mark – you can easily create or adopt an image for this purpose.
  • Finally add a Command Button and name it btnOK, set its Caption to OK – this will be the button that will be disabled until the timer interval has elapsed and the user is allowed to press it.

Tip: Why not add a Frame Control just over half the Height and the full Width of the Excel VBA UserForm to hold the lbCountDown Label and btnOK Button and then set the BackColor of the UserForm itself to &H80000005& – this divides the UserForm giving it a really pleasing aesthetic quality

You should now have a nice looking Excel VBA UserForm and be in a position to add in the VBA Code.

Double-click your OK Button to enter the VBA Code Editor for the UserForm Module. Amend the Subroutine generated to the following Code – you can paste over it if preferred:

'=================================
' btnOK_Click, closes the UserForm
'=================================
Private Sub btnOK_Click()
Unload Me
End Sub

Now add the following Code at the top of the Module – these are the Windows API’S we will be using to re-style the Dialog Window and the Interval Variable that we have set to 5 (seconds) before we enable the OK Button:

Option Explicit Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long Const WS_SYSMENU = &H80000 Const GWL_STYLE = (−16)
'=============================================================== ' Interval, set this to the time before the OK Button is enabled
'===============================================================
Private Const Interval = 5

OK, next we will add the UserForm QueryClose Event Handler. This can be used to simply trap a Close attempt on the Red Cross if you don’t want to remove it when we get to the UserForm Activate Event Handler in a minute. So add the following Code:

'================================================================'
UserForm_QueryClose, workaround if you don't want to hide the Red Close Cross
'================================================================
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
On Error GoTo QueryCloseErrorHandler
Application.EnableCancelKey = xlErrorHandler
If CloseMode = 0 Then Cancel = True
MsgBox "Oops, the X in this Dialog has been disabled, please use the OK Button on the form", vbCritical, "Kiosk 4.1"
End If
Exit Sub
QueryCloseErrorHandler:
Resume Next
End Sub

Nearly there, now we need to add in the UserForm Activate Event Handler Code. This is the work-horse of the VBA Code and I will go through it in a minute. Anyhow, add in the following Code (you can please yourself whether you add in the comments):

'================================================================
' UserForm_Activate, strange little error handling routine, it goes...
' − style the userform to remove the red cross ' − add a restart point for hack attacks
' − set up an error handler & tell Excel to use it ' − disable the OK Button
' − start the countdown
' − on hack (CTR+Break) goto restart − that will begin the whole process over
' − if time up enable OK Button, OK will Unload the UserForm
' − in the interim DoEvents will allow you to still move the Dialog
'================================================================
Private Sub UserForm_Activate()
On Error Resume Next
Dim hwnd, lStyle As Long
hwnd = FindWindow("ThunderDFrame", Me.Caption)
lStyle = GetWindowLong(hwnd, GWL_STYLE)
SetWindowLong hwnd, GWL_STYLE, lStyle And Not WS_SYSMENU
Me.lbTrialMsg.Caption = Me.Tag & Me.lbTrialMsg.Caption

restart: err.Clear
On Error GoTo TrialErrorHandler
Application.EnableCancelKey = xlErrorHandler
Me.btnOK.Enabled = False Dim t As Single
t = Timer
Do DoEvents
If err.Number = 18 Then GoTo restart
If Round(t + Interval − Timer, 0) > 0 Then
If err.Number = 18 Then GoTo restart    Me.lbCountDown.Caption = "This Trial Dialog can be closed in " & Round(t + Interval − Timer, 0)
Else
If err.Number = 18 Then GoTo restart
Me.lbCountDown.Caption = ""
End If
Loop
While t + Interval > Timer
Me.btnOK.Enabled = True
Exit Sub
TrialErrorHandler: Resume Next
End Sub

The first part of the Code tells Excel that if it hits an error to skip over regardless of what error has occurred. Not normally good practice but we do not want the Excel Debug box to ever be made available to a user.

We then style the Dialog Window to remove the red cross Close Button using the Windows API calls. NB: For Office 2000 and later we use the class name ThunderDFrame (for Office 97, it isThunderXFrame). The lbTrialMsg Caption is then set to include the UserForm Tag message that we set before we show the UserForm plus the message we set earlier. In other words we can call this UserForm from anywhere in our Application passing in a Key Relevant Message that is prefixed onto our pre-set one.

We then add a Goto point called restart: This will be where we jump to when a user presses the CTRL+Break Keystroke combination. This also sets up Excel to use another Goto point for our Error Handling and then tells Excel that we wish to only use that point for all errors whatever they may be.

Next we disable the OK Button. We set the variable ‘t’ to the current Timer time and begin looping until our interval has expired – the interval variable was set to 5 (seconds) earlier. In the interim we use DoEvents to allow the Dialog to be moved around and the lbCountDown Caption to be updated with our Countdown Message. When the interval expires we then clear the lbCountDown Caption.

At the same time we still trap the CTRL+Break Keystroke combination and then enable the OK Button as the flow moves out of the Do loop. We then exit the Subroutine. Used in conjunction with some VBA Module protection you have a useful little Countdown Message / Nag Dialog.

If you don’t want to re-style the Dialog Window, simple remove the API’S and allow the UserForm QueryClose Event Handler to trap the Red Close Cross.

OK, finally, double-click the ThisWorkbook Module and enter the following:

Option Explicit
'================================================================
' DemonstrateMsgBoxCountdown, run this to see the Countdown Dialog '================================================================
Public Sub DemonstrateMsgBoxCountdown()
MsgBoxCountdown.Tag = "(YOU CLICKED A FEATURE):"
MsgBoxCountdown.Show
End Sub

OK, now choose Debug–>Compile VBAProject to compile your Code and check for any errors. Click anywhere in the Code you entered above and press F5 to Run the Sub/UserForm.

You should see your UserForm appear. Try pressing Ctrl+Break and watch the Timer interval increase back up to 5 seconds.

That’s it.  I hope you liked this article on Creating an Excel VBA UserForm.

Mark Kubiszyn

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.

Creating Excel VBA Macros

How to Create Excel VBA Macros.

Have you used a voice recording?  Creating Excel VBA Macros can be that easy.  The main benefit of a voice recording device is that everything you spoke once can be repeated over and over again without you physically speak again. Your voice is on the tape. You can even be heard without you being there. The Excel Macro Recorder feature is similar. The difference is that you store Excel actions with Excel VBA Macros instead of your voice. These actions can be repeated again and again without your physical intervention for each step in Excel. For example: Select a given data region, insert a table, and create a Pivot Table

  • Select a heading of a given table and apply a particular formatting
  • Delete all the empty sheets
  • Delete all sheets except the current one
  • Arrange cells in a particular layout
  • And more…

An audio tape is a set of recorded sounds. A Macro is a set of recorded instructions.

What Kind of Instructions Can Be in an Excel Macro?

Actions like: changing the color of a cell, renaming a sheet, creating a table, sorting, filtering, etc. On a voice recorder, to reproduce your voice you hit the Play button on the recording device; on the other hand, to reproduce a set of actions on Excel you run a Macro. Your voice is recorded on a magnetic tape or digital memory, while your Excel actions are recorded in a text file, specifically in an Excel VBA Macros Sub procedure inside a text window called a “Module”. There is a hierarchy within Excel:

  • Workbook – the Excel file that contains Excel formulas, data, and charts
    • Sheet – a tab, or section of the Excel Workbook
      • Range – a single cell or group of cells
  • Form – the graphical screen display for Excel instructions and data
  • Module – an Excel container that holds Excel VBA instructions
    • Subprocedure or Sub – a section of Excel VBA code dedicated to perform certain actions.  Contains, data, instructions, calls to functions.  Does not return data or status to the calling program.
    • Function – a series of VBA lines of code that perform a particular task, and return data or status codes to the calling routine.
    • Object – The target of instructions in the Excel Libraries.  A container for Properties, Methods, and Events
      • Method – An activity that Excel is being told to do.  Examples: insert a cell, delete a worksheet
      • Property – an attribute of the object.  Data or parameters passed to or from an Excel SUB or Function that give direction to how that code runs.  Examples: color, name, size, location.
      • Event – A trigger that directs when the object is to run.  Example, a keypress, or mouse click are events.

How do you Record Instructions in a Macro?

If you are using Excel 2007 or 2010

  • Go to: View>Record Macro>Macro name:>Ok
  • Or click the status bar Record icon.
  • The Excel 2007/2010 status bar also indicates that Excel is in recording mode…

On Excel 2003

  • Go to: Tools>Macro>Record New Macro
  • And specify the Macro configuration (name, description, etc.)…
  • After pressing OK, the status bar indicates that Excel is in recording mode…

While this mode is activated, EVERYTHING you do on Excel will be recorded. This is not as good as it might appear; the same way your voice recording device registers your hesitations and background noise, the Excel recorder captures all your mistakes and incidental actions.

Editing Recorded Macros

This creates the need to edit recorded actions in Macros. Keep in mind that while Excel is in recording mode, its behavior is normal, with one exception: in the background, Excel creates a Module (Module1, Module2, Module3, ModuleN) and a Sub procedure with the name you specify on the “Record Macro” dialog. By default: Macro1, Macro2, Macro3, MacroN.

The Excel VBA Editor is opened with Alt+F11.  Locate the workbook in the Explorer panel on the left.  Within that Workbook you will have Sheets, Modules, and Forms.  Your recorded macros will be within one of the modules in the Modules organization. Make the necessary changes to the macro in the VBA Editor.  You can run the subroutine from there, or simply make and save your changes, then run the macro from within the spreadsheet window.

Author unknown:

Edited by Rich Moyer

Rich Moyer is Principal Consultant(retired) of Spaho Consulting

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.

Utilizing Excel Object Methods In VBA Programming

When writing code in Microsoft Excel Visual Basic for Applications (VBA), you need to programmatically manipulate the objects inherent in Excel, such as worksheets, charts and pivot tables. In VBA, each of these objects has properties and methods which can be used to manipulate the object and make it do what you need it to. Properties are the attributes or characteristics that the object possesses, whereas Excel Object Methods are actions associated with the object. For example, a workbook has a “name” property and an “open” method.

Excel Object Methods

Excel Object Methods are a little more sophisticated than properties in that they normally, though not always, require expansion in order to get a particular object to perform a particular action. This expansion is provided through the arguments which the programmer has to supply when using the method. Thus, in order to exit Excel altogether, we would use the “quit” method of the “Application” object, thus: “Application.Quit” without supplying any arguments.

Parameters

If, by contrast, we want to open a workbook, we need to supply at least one parameter: a string specifying the workbook to be opened. As you write your code, Excel will furnish a useful prompt: having entered the method, if you type an opening parenthesis, a “QuickInfo” tool tip will appear with a list of the parameters required by the method. This facility is much the same as the display of parameters when you enter a function in Excel.

Arguments

Any arguments which are displayed in square brackets are optional while those not in square brackets are obligatory. Thus, for example, when using the “Open” method of the “Workbook” object, the “Filename” parameter is obligatory while the “ReadOnly” parameter is optional. The “QuickInfo” tool tip supplies a list of every parameter, separated by commas, which the method can accept in the order that they must be supplied. If you do not wish to supply a given (optional) parameter, then you must still insert a comma to mark the position of the omitted argument.

Thus, for example, if you wanted to use the “Open” method of the “Workbook” object and supply the “Filename” and the “ReadOnly” parameters, you would type “Workbooks.Open(“c:\reports\main.xlsx”,, True)”. Since the “ReadOnly” parameter is the third, a comma is inserted to mark the position of the missing second parameter. VBA offers a very useful alternative method of entering arguments. You can enter the name of each parameter followed by “:=”. Using this technique, the order of parameters becomes unimportant and no reference has to be made to omitted parameters. Thus, in the example above, we could type “Workbooks.Open(FilePath:=”c:\reports\main.xlsx”, ReadOnly:=True)”.

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.

Using Excel VBA and Google Maps to Find the Distances Between Places

Project: Find the Distances Between Places with Excel VBA and Google Maps

My friend wanted to find out the distances between towns in South Africa. He asked me to give him a hand, as I used to do a lot of data processing in my first banking job. My buddy was going to use this to calculate transport costs, but the function I wrote using Excel VBA and Google Maps could be used for any number of applications.

Preparation

There are many ways to skin a cat. This was an unpaid job, with no glory component, so I just did it in what I thought would be the quickest way possible.

Method

I knew Google Maps could calculate distances and directions. So I fired it up and looked for the distance between two towns. I then looked at the source HTML to work out how to extract the distance data. Trying different towns, it soon became apparent that I needed to specify the state and country, as well as the town name. As town names are not unique. I then opened up Excel, made a sheet to calculate the query URLs, and then wrote a function using Excel VBA and Google Maps to piece all the parts together.

Results

The function seems to work most of the time. Sometimes it does not return anything, but that is because Google Maps does not return a distance, and that is because the place names are not specific enough.

Disclaimer

Use the getDistance function at your own risk. It might not work for you, but I won’t be supporting it. The Code:

Function getDistance(urlData As String)
Dim sHtml As String
Dim iStart As Integer
Dim iEnd As Integer
Dim lRow As Long
Dim searchStart As String
searchStart = "distance:"""
Dim browser As InternetExplorer

Set browser = CreateObject(“InternetExplorer.Application”) With Sheets(“Soya”)

lRow = 2
While Not IsEmpty(.Cells(lRow, 6))
urlData =.Cells(lRow, 6)
browser.Navigate (urlData)
browser.Visible = True
While browser.ReadyState <> READYSTATE_COMPLETE
Debug.Print Now, "waiting"
DoEvents
Wend
sHtml = browser.Document.DocumentElement.innerhtml
Debug.Print sHtml i0 = InStr(1, sHtml, "distance:""")
If i0 > 0 Then i1 = InStr(i0 + Len(searchStart), sHtml, """")
If i1 > 0 Then getDistance = Mid(sHtml, i0 + Len(searchStart), i1 − i0 − Len(searchStart))
Else getDistance = "Not Found"
End If
Else getDistance = "not found"
End If

.Cells(lRow, 8) = getDistance
Debug.Print Now, getDistance
lRow = lRow + 1
Wend
End
With browser.Quit
End Function

Learn More!

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.

Microsoft Excel Experts

How To Find The Right Microsoft Excel Expert That Will Fit Your Goals and Budget  

by Roy Millor in Technology    (submitted 2012-06-30)

Are you searching for Microsoft Excel experts who can help fulfill your needs? If so, consider trying the Directory of Excel Experts!   The Directory of Excel Experts lets you quickly find the right Microsoft Excel experts who can meet your unique business criteria. Whether you need someone nearby in your city, or you’re looking for someone who specializes in a certain task, Directory of Excel Experts can help you out in any country, state, city, or zip code. You can also search for them to be within a set number of miles from your place of business!

Sort by Programs

You can also look for different Microsoft Excel experts depending on their qualifications, skills, and readiness to complete a particular task. You can see what programs they are experts in, like VB.net, Word, SQL Server, Access, etc. You can even combine the searches (for example, finding a VBA specialist that is also an Access programmer) and look for one within 10 miles of you! The Directory of Excel Experts lets its members look for almost any combination of qualifiers imaginable.

Search Results

In the results, you can set it up so you can see the Microsoft Excel experts’ office hours, contact information, what applications they are working in, what their URL is, and much more. Over time, the possibilities will dramatically increase,  as y more qualified Excel experts register with the service.

Toll Free

The Directory of Excel Experts is a free service! You’ll find that the Directory of Excel Experts is available for free, with no strings attached. That’s all – they don’t require pay for their directory and search services. The Directory doesn’t manage the project at all – they stay 100% out of all of that! Everything – the pricing, deals, etc. – is between you and the Microsoft Excel experts, which simplifies things quite a bit. You can contact one expert, or you can contact a dozen – it’s all up to you.If you really like the work done by a particular resource, you can post your review of the Microsoft Excel experts and your interaction with them. As you go along, you’ll find that the specific areas of their experience greatly vary, as does the frequency of their Excel use. Many Microsoft Excel experts are currently becoming professional programmers, developers, and consultants, taking their talents to do freelance work.  Make sure not to forget that thousands of these folks are entry level Excel hobbyists and may be some power users, but not officially qualified folks. Looking for a Microsoft Excel Expert try Directory of Excel Experts now. Use and distribution of this article is subject to our Publisher Guidelines whereby the original author’s information and copyright must be included.

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.

Creating Excel Macros

Macros are basically a set of functions and commands that help Excel users in performing the same task again and again easily. Creating Excel macros for the execution of repetitious tasks will help in any business organization. Macros not only save time, but it also improve consistent data maintenance and reduce errors.

Spreadsheets in every organization contain lots of raw data.  This data is analyzed with the use of various logical calculations called formulas. When recording a macro, each formula is then substituted with VBA (Visual Basic for Applications) program code.

Most users can start creating Excel macros simply  by using the Macro Recorder in Excel.  Many simple macros can be created without the intervention of an Excel VBA programmer.

Here are few tips that will help you learn how to record Excel macros.

1. Macros need to be recorded before they are run.

Your Excel options must be changed to grant permission for macros to be executed.  To allow the recording of macros, you need to enable the macro option from the tool menu.

2. Look for the Record New Macro option in the menu.

As soon as you select the option, a dialog box opens, where you need to type a name for the macro you are recording.

3. Just after typing the name click the OK button and the recording will start automatically.

4. The Excel user then needs to execute each of the functions that you need the macros to perform.

The commands and functions that you input while recording are all recorded, even the mistakes. Macros are also capable of performing multiple tasks. \

5. Once the functions are recorded, you need to click the Stop Recording button that is present in the small window opened showing that recording is active.

Once the recording is stopped the macros are saved in the VBA Editor.

6. To run the macros you can run them from the Macros dialog, or you can create a button to be placed on the toolbar.

To create a button, you need to select Customize Button from the tool menu and in the command tab click Macros. Once the dialog box appears, choose the name of your macro and press the OK button. Now the button will remain on the tool bar, just one click away to perform that task.

7. By creating a shortcut on the keyboard, you can run the macro by pressing the required keys on the keyboard.

To create a shortcut you can select Option from the macro menu. Just after selecting, a window will open, where you are prompted to press the desired shortcut key. You can select a key combination that is convenient to you, but remember that some shortcut keys are already assigned to important functions.  For example, you may not want to reassign a shortcut such as Ctrl+P, which inherently brings up the Print menu.  There are also predefined shortcuts for pasting (Ctrl+c) and cutting (Ctrl+x). After selecting the key combination you want, press the OK button.

Macros are easy to use and can be effectively used for formatting cells for text, putting data in certain cells by matching column and rows and for adding formulas while creating spreadsheet.

Once the macros are recorded, Excel VBA programmers can make changes as required, depending on the needs of the organization.

by Schew Maker

in Computers / Databases    (submitted 2011-04-15) Use and distribution of this article is subject to our Publisher Guidelines whereby the original author’s information and copyright must be included.

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.

Excel VBA Workbook Events – Macros

These sub procedures that deal with certain Excel VBA Workbook Events need to be stored in the code window for the workbook object.   These short VBA snippets are installed in the workbook itself, in the code window of the ThisWorkbook object.

  • snippetsOpen with Excel – open the target spreadsheet into which these macros are to be installed.
  • Open the Excel VBA Editor with Alt+F11
  • Right Click ThisWorkbook
  • Select View Code.  A new code window  will appear

What are Excel VBA Workbook Events?

Whether you know it or not, every workbook triggers events for even the most routine tasks such as opening, closing, saving, or exiting.   By preempting (or redefining) the default function, you can change the default behavior of Excel to do different tasks.

Prevents a Save As

There are situations that you do not want the user to create a copy of the workbook you have provided.   All changes made to  the spreadsheet must be saved back to the  original location.  This is done by redefining the Excel VBA Workbook Event for SaveAs.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then MsgBox "You cannot perform a SaveAs on this file. Save the file using the existing save settings." Cancel = True
End If
End Sub

Clean Up the Workspace

This macro arranges the open workbooks in tiled format  when a workbook is selected.  This helps the user to clean up and organize the workspace whenever they open a new workbook.  The VBA Workbook Event preempted is Workbook_Activate().

Private Sub Workbook_Activate()
MsgBox "Open workbooks will now be tiled"
Application.Windows.Arrange
xlArrangeStyleTiled
End Sub

Organize the Order of Worksheets

When a user opens a new worksheet, the last worksheet opened becomes last worksheet in the workbook.  The Excel VBA Workbook event preempted is Workbook_NewSheet()

Private Sub Workbook_NewSheet(ByVal Sh As Object)
MsgBox “The new sheet will appear as the last sheet in this workbook."
Sh.Move After:=Sheets(Sheets.Count)
End Sub

Display Sheet and Range Info in Status Bar

The Status Bar display is optional, but I find it particularly useful: it can contain lots of information, and provide some shortcuts.  I keep it turned on all the time.  This Excel VBA Workbook Event Macro displays the sheet name and address of the selected range in the status bar.  This comes in handy when working with multiple workbooks and worksheets, and when scrolling so the active cell is off the screen.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Excel.Range)
Application.StatusBar = "You are in " & Sh.Name & " and have clicked in the cell range " & Target.Address
End Sub

Code By: Chester Tugwell Article Directory: http://www.articledashboard.com

Chester Tugwell is a freelance Microsoft Office trainer and owner of Blue Pecan Computer Training based in Sussex, UK. He provides a comprehensive set of Excel training courses as well as other Microsoft Office training options. More free computer training materials are available via the Blue Pecan website.

Narrative and explanation of the subroutines are by Rich Moyer

Rich Moyer is Principal Consultant(retired) of Spaho Consulting


Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.

Using Message Boxes and Input Boxes in Excel VBA

If you want to make your macros interactive then one way of doing this is to introduce message boxes and input boxes. These allow you to display messages that require a response or ask the user to enter values before the macro can proceed any further.

Simple greeting using a message box

Create an empty workbook and the switch to the Visual Basic Editor (VBE) by clicking
Tools > Macro > Visual Basic Editor
or by using the shortcut key ALT F11.

If you are using Excel 2007 click on the Developer ribbon and then click on the Visual Basic Editor button.

Once in the VBE environment you will need to create a module to hold your function. Click Insert > Module In the code window enter the following procedure

Sub Greeting()
MsgBox “Welcome to my spreadsheet”
End Sub

Press F5 to run the macro

Passing a variable to a message box

In the same procedure as above we will create an input box variable called MyName and declare the variable as a string. We will then concatenate the message “Welcome to my spreadsheet” with the value in the MyName variable. This joins the message in the message box with a value the user enters in an input box.

Sub Greeting()
Dim MyName As String
MyName = InputBox("What's your name?")
MsgBox "Hi There " & MyName & "."
End Sub

Press F5 to run the macro

Adding more information to a message box

Now we are going to use Date, Time and CurDir functions to add more information to our message box. Adjust your code as shown below.

Sub Greeting()
Dim MyName As String
MyName = InputBox("What's your name?")
MsgBox "Hi There " & MyName & ". Today's date is " & Date & " and the time is " & Time & ". This file is saved in " & CurDir & "."
End Sub

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.