JACC Cost effectiveness inclusion

http://content.onlinejacc.org/article.aspx?articleid=1853961

Add a hyperlink to Excel using VBA

To add a hyperlink to a website using VBA, use the code below.

In summary, the syntax for adding a hyperlink is as follows and can be seen in the examples below:

• Method: Worksheets("Sheet1").Hyperlinks.Add
• Anchor: refers to the location where you want the hyperlink.
• Address: refers to the web address (or document address) you want to hyperlink to
• TextToDisplay: refers to what you want the text that holds the link to display. ( See bottom example if you want to keep the text that is already in the cell you are adding the link to)

'Adds a link to http://excelvbaprograms.blogspot.com/ in Row 1, Column A to text "Link me out"
Sub WebLink()
Worksheets("Sheet1").Hyperlinks.Add Anchor:=Cells(1, 1), Address:= _
"http://excelvbaprograms.blogspot.com/" _
, TextToDisplay:="Link me out" 'whatever you want the link text to display
End Sub

'Adds a link to http://excelvbaprograms.blogspot.com/ to text currently displayed in Row 1, Column A
Sub WebLink()
Worksheets("Sheet1").Hyperlinks.Add Anchor:=Cells(1, 1), Address:= _
"http://excelvbaprograms.blogspot.com/" _
, TextToDisplay:=Worksheets("Sheet1").Cells(1, 1).Value 'Will add a link to text currently in cell
End Sub

Note: Cells(1, 1) is in the format Cells(row, column) so, for example, column A is referred to by 1, Column B would be 2, Column C would be 3, etc.

How To Name a Shape in VBA for Excel

A post I made earlier required the coder to refer to a shape.  Here is an easy way to determine the name of the shape or to set the name of the shape. 

Naming a shape is just like naming a cell.  If you click the shape, a box in the top left corner of your excel workbook should give the name of the shape.  By the same token, you can name the shape by overwriting the current, generic, name in the same box.  It is exactly like naming a cell.

Using a Multiple-Selection List Box In an Excel Worksheet

The following code uses a command button in conjunction with a List Box to output all of the selected entries into the active worksheet.

1. Make sure the control toolbar has the "edit" button selected (the button to the far left on the control toolbar - the one with the ruler and pencil)
2. Create a list box in the worksheet.
3. Populate the list box using the following steps:
   
• Make sure that you are in control edit mode (click the symbol with the pencil and the ruler to the far left of the control toolbar- when edit mode is turned on, when you mouse over the list box, you will be able to click and drag the list box and should not be able to select any of the items within the list box)
• Right click the list box (while it is in edit mode)
• Click "properties"
• On the ListFillRange, type in the range which you want the list box to display. For example, If you want to type in the values you want displayed in the list box in Cells A1 through A10, type A1:A10
4. Right-click the list box and set the Name property to ListBox1 (it may already be set to this)
5. Set the MultiSelect property to 1 - fmMultiSelectMulti or 2 - fmMultiSelectExtended.
6. Create a command button that will be used as an "Enter" button that will be used to create an output from your list. Create this button next to your list box (the code below is specifically for a command button on the same page as the list box)
7. While the control tool bar is set to Edit mode, right-click the Command Button, click properties and make sure the name property is set to CommandButton1
8. Right-click the command button again and click View Code (or you can double-click the command button while in edit mode).
9. Now, while in VBA enter the following code so that your subroutine looks as follows:
   

Private Sub CommandButton1_Click()
Dim dblCounter As Double
Dim dblNumEntries As Double
Dim dblRowCount As Double
dblRowCount = 1
dblNumEntries = ListBox1.ListCount

'This code clears column 3 to get ready for list box output
For dblCounter = 1 To dblNumEntries
Application.ActiveSheet.Cells(dblCounter, 3).Value = ""
Next dblCounter

'This code adds the sleected list box output to column 3 (C)
For dblCounter = 0 To dblNumEntries - 1
If ListBox1.Selected(dblCounter) = True Then
Application.ActiveSheet.Cells(dblRowCount, 3).Value = ListBox1.List(dblCounter, 0)
dblRowCount = dblRowCount + 1
End If
Next dblCounter

End Sub

Note that this code will output the selected entries into the 3rd column (column C) of the active worksheet. You can easily change this by altering the "3" in this line of code:


Application.ActiveSheet.Cells(dblRowCount, 3).Value

You can also send the output else where by modify the Application.ActiveSheet portion of the code. For example if you want the output to go to "Sheet1" it would be coded as follows:


Worksheets("Sheet1").Cells(dblRowCount, 3).Value

If anyone has any questions about the analogous actions for a multi-column list box, feel free to post your question.

To use a list box in an Excel Worksheet that allows the user to make only one selection, implementation is much simpler, see:

Single Selection List Box in an Excel Worksheet

How to Create a List Box in an Excel Worksheet

Many people use list boxes within forms. Another use of the list box is within an Excel worksheet. This post explains how to add and populate a list box as well as how to refer to the output so that it can be used however you please.

1. Add a list box to your worksheet anywhere you want it.
2. Make sure that you are in control edit mode (click the symbol with the pencil and the ruler to the far left of the control toolbar- when edit mode is turned on, when you mouse over the list box, you will be able to click and drag the list box and should not be able to select any of the items within the list box)
   
3. Right click the list box (while it is in edit mode)
4. Click "properties"
5. Make sure the MultiSelect Property is set to 0 - fmMultiSelectSingle
6. On the ListFillRange, type in the range which you want the list box to display. For example, If you want to type in the values you want displayed in the list box in Cells A1 through A10, type A1:A10
7. Set the LinkedCell property to the cell in which you want the output display. For example, if you want the output displayed in cell A11, type A11 in this box.
8. Turn off the control edit mode (click the symbol with the pencil and the ruler to the far left of the control toolbar)

Now you can use the list box and the value of the selection will be put in your desired output cell!

The above example is only useful when you allow the user to select a single item from the list box. In cases where you want the user to be able to select multiple items (as often is the case) you must use VBA. For my next post, I will provide some VBA code to output multiple selected data.

Excel VBA: Moving a Shape to Specific Position

To move a shape to a specific position of an excel workbook, you can specify the location of the shape's top, left corner by using the following commands:

ActiveSheet.shapes(X).top = 100
ActiveSheet.shapes(X).left = 200

Each shape is referred to by a number so replace the X by that number. the first shape up is numbered 1, the second is numbered 2 and so on. The code for shape 1 would look like:

ActiveSheet.shapes(1).top = 100
ActiveSheet.shapes(1).left = 200

Each shape is also given a name such as "Rectangle 1" so an alternative to this code is:

ActiveSheet.shapes("Rectangle 1").top = 100
ActiveSheet.shapes("Rectangle 1").left = 200

Now, as far as exactly where the shapes will be located, you will likely have to play around with it to get it right, the location does not appear to correspond with pixels or cell locations, so just try different numbers and see where it ends up!

I will post code soon that will allow a user to identify a shape's name by clicking it. Leave a comment and I will likely post it sooner. In the meantime, you can record a macro to get the shapes name or keep track of the order in which you added the shapes so you can refer to them by number (or by "Rectangle 1" for the first rectangle you add, "Rectangle 2" for the second, etc.)

Allowing users to open additional Excel Workbooks while a form is open

I recently made an excel workbook that automatically opened a user form (UserForm) and allows the user to navigate through a series of user forms, thus never letting the user have access to the actual worksheets. This is useful to prevent erroneous data from being entered in the worksheets. I ran into a small problem, though. When users wanted to open other Excel workbooks in order to copy data, etc. the workbooks were not displaying. To solve this problem I used the Application.IgnoreRemoteRequests method.

By setting Application.IgnoreRemoteRequests = True upon opening the workbook, the user can now open other workbooks in a different Excel browser that will not interfere with your workbook.

Just be sure to set Application.IgnoreRemoteRequests = False when you close the workbook or you could run into problems!