Is there a way to display negative time values?
	Why does Excel display the 2000th row when I drag the scroll bar down even though I have deleted all information beyond the 100th row?
	Can I provide worksheet buttons to zoom in and zoom out?
	How do I change part of the text in a text box to bold?
	How do I remember and restore the window to the same position after a user has scrolled to another position?
	How do I place a message in the Status Bar while a macro is running?
	Can I freeze the screen display while my macro is running?

Is there a way to display negative time values? I have a column showing the time difference between two events. Sometimes this contains negative values but all that Excel displays is "######".
Keywords: Time Negative Values
Posted July 22, 1996

One way to do it is to substitute the following formula instead of a simple subtraction. Suppose you have column C displaying the difference between columns A and B, then C1 will have:

=IF(A1>B1, A1-B1, TEXT((A1-B1)*(-1),"-h:mm:ss")
			

Why does Excel display the 2000th row when I drag the scroll bar down even though I have deleted all information beyond the 100th row?
Keywords: Display Scroll Bar
Posted July 22, 1996

Save the file and try again. Excel updates its scrolling pointers when you save the workbook. In large worksheets with complex formulas and many names, you might occasionally find that Excel stubbornly scrolls down to an unused area. In such rare cases, you will have to recreate the worksheet by copying the formulas and formats to a new worksheet. If you desire to scroll to unused areas, you can do so by keeping the Shift key pressed while dragging the scroll bar.

Can I provide worksheet buttons to zoom in and zoom out?
Keywords: Zoom Buttons
Posted April 29, 1996

You can, using the instructions given below. However, we recommend that you provide toolbar buttons to your users due to the following advantages:

• It's easy to implement. Just place the two (or three, if you use the dropdown) built-in buttons in your custom toolbar
• The same buttons give the zoom facility for all sheets in all workbooks
• When the buttons placed on the worksheet are used, they will change size along with the sheet, making it hard for your user to locate them and click

To provide ZoomIn and ZoomOut buttons on a worksheet, place two buttons on your worksheet. You can set the captions on the buttons, or, copy and paste the built-in toolbar button images on top of the buttons. Place the following code in a module and assign the ZoomInBtn_Click() and ZoomOutBtn_Click() macros to the corresponding buttons.

Option Base 1
Dim mvZooms As Variant       ''' Variant to hold array of zoom values
Sub ZoomInBtn_Click()
Dim iIndex As Integer
   If Not IsArray(mvZooms) Then InitZoomValues  ''' Initialize first time
   For iIndex = 1 To UBound(mvZooms)
      If mvZooms(iIndex) > ActiveWindow.Zoom Then
         ActiveWindow.Zoom = mvZooms(iIndex)
         Exit Sub
      End If
   Next iIndex
End Sub
Sub ZoomOutBtn_Click()
Dim iIndex As Integer
   If Not IsArray(mvZooms) Then InitZoomValues  ''' Initialize first time
   For iIndex = UBound(mvZooms) To 1 Step -1
      If mvZooms(iIndex) < ActiveWindow.Zoom Then
         ActiveWindow.Zoom = mvZooms(iIndex)
         Exit Sub
      End If
   Next iIndex
End Sub
Sub InitZoomValues()
      ''' Initialize array with desired "step" values
   mvZooms = Array(25, 50, 100) ', 200, 300, 400)
End Sub
			

How do I change part of the text in a text box to bold?
Keywords: Text Box Property Font
Posted April 22, 1996

Use the Characters method of the TextBox object to access the individual characters in the text box. The Characters method returns a Characters object which allows us to format portions of the full text string.

You can specify the starting position and the length of the required substring. If you do not pass the length parameter, all the characters following the specified starting character will be returned. If you omit the starting position, it is taken as 1 (one). There are a few examples below in this document and in a downloadable Excel workbook.

   With ActiveSheet.TextBoxes("tbOptions")
         ''' Make third and fourth characters bold
      .Characters(start:=3, length:=2).Font.Bold = True
         ''' Make fifth character onwards italic
      .Characters(5).Font.Italic = True
   End With
   With ActiveWorkbook.Worksheets("Sheet1")
         ''' Delete first four characters. Start is assumed to be 1
      .Cells(1, 1).Characters(length:=4).Delete
         ''' Insert ABC before the second character
      .Cells(2, 1).Characters(2, 0).Insert "ABC"
   End With
			

We’ve prepared a workbook with code samples which you can download free. This workbook contains information and re-usable code on:

• How to refer to local and global names in code
• How to find the name, if any, of a cell range

Download Baarns GetName Example Workbook Now! (34K)
Information provided in this document is provided "as is" without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The user assumes the entire risk as to the accuracy and the use of this information.

How do I remember and restore the window to the same position after a user has scrolled to another position?
Keywords: Restore Window Position
Posted April 13, 1996

You may have tried the approach of storing the ActiveCell and then using the Activate method to make that cell active again. This may sometimes work but the cell might not be in the same position within the window.

To remember the position in the window, save the ScrollRow and ScrollColumn properties of the Window object. To restore that position, use the Application.GoTo method with True as the second parameter. Your code might be something like:

Dim miRow As Integer, miCol As Integer   '-- Module level variables
Sub Remember()
    miRow = ActiveWindow.ScrollRow
    miCol = ActiveWindow.ScrollColumn
End Sub
Sub GoBack()
    Application.Goto ActiveSheet.Cells(miRow, miCol), True
End Sub
			

How do I place a message in the Status Bar while a macro is running?
Keywords: Status Bar Application Message Display
Posted March 22, 1996

The following simple code will handle it:

Application.Statusbar = "Processing your request..."
	

When you are finished and want Excel to use the statusbar, turn off your custom handling using the following:

Application.Statusbar = False	

If you say

Application.Statusbar = ""
	

the statusbar will display a blank message (nothing) until you change it or restart Excel.

Can I freeze the screen display while my macro is running?
Keywords: Screen Refresh Freeze Display
Posted January 16, 1996

The following code will freeze the screen and often speed up longer macros considerably:

Application.ScreenUpdating=False
	

When the macro finishes, screen updating is automatically turned back on. Generally speaking, you don't turn the screen back on (=True) unless you want to have the user to see specific activity.

An exception to this would be if you display a dialog box in the middle of your code. If the screen updating is turned off when a dialog displays and your user moves the box, the background won't repaint properly.

Back to Top

Back to the FAQ Table of Contents

There cannot be a crisis today; my schedule is already full.