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:
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”
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”. For a detailed view of how to do this, check out Tip 055, which discussed a Before_Print macro. Follow those instructions, but 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
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:
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
Long-time MrExcel readers may recall the old Challenge of the Month contest. I’ve posted a new Challenge of the Month, the first in several years. It is a tough one. Anyone is welcome to take a stab at the problem to win some cool prizes. Check it out.