Back

About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store
Podcast
Search
Contact
Home

 

 

Past Tip of the Day

 

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". 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
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

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.

By Bill Jelen on 18-Oct-2001

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

 

Excel is a registered trademark of the Microsoft® Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.