Selecting Files Within VBA

October 18, 2001 - by Bill Jelen

Renato from Italy asks:

How can I open a workbook in VBA choosing from a list? When I record a macro, it hard-codes the name of the selected file in the macro.

There is a command in VBA called GetOpenFileName. It displays the File Open box. You can naviagate to a directory, select the file and then click Open. At this point, the command does not open the file, it merely passes the name back to your program. Here is an example of the code in use:

Sub TestIt()
	NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")
	If NewFN = False Then
		' They pressed Cancel
		MsgBox "Stopping because you did not select a file"
		Exit Sub
	Else
		Workbooks.Open Filename:=NewFN
	End If
End Sub

Roger asks:

How can I get a macro to run before a file is closed or saved?

The macro needs to be entered on the code pane associated with "ThisWorkbook". From the right dropdown, select either BeforeClose or BeforeSave.

Ken writes:

I have a worksheet event handler that loops, at least 16 times. What is going on?

Ken's handler was simple - if the entry was non-numeric it would change the entry to upper case. Here is the problem. When he changed the value of the entry to upper case, that is another worksheet change and the event would fire again. Every time the change event would fire, Ken would change the worksheet and the macro was getting called recursively, until the call stack ran out of memory.

The solution is to temporarily stop events from running while you change the value to upper case. You can do this by changing the value of Application.EnableEvents to False. Here is the corrected macro:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
	If Not IsNumeric(Target.Value) Then
		Application.EnableEvents = False
		Target.Value = UCase(Target.Value)
		Application.EnableEvents = True
	End If
End Sub

Andy from the U.K. asks today's most interesting question.

I have a VBA routine in one workbook that creates several other workbooks. I would like to be able to dynamically add a hyperlink in each new workbook that will point back to the workbook that generated the new workbooks.

Andy - this is a cool idea. Without the benefit of seeing your code, I can imagine something like this would work:

Sub Testit()
	Creator = ActiveWorkbook.FullName
	'.... Andy's code to create the new workbook....
	ActiveSheet.Hyperlinks.Add Anchor:=Range("A1"), Address:= _
			Creator, TextToDisplay:="Click Here to Return to " & Creator
End Sub