In attempting to delete some hidden data I keep getting a File Not Found error message. What is going wrong?
	Is there any way to hide or deactivate the cell pointer on a worksheet?
	How can I cycle through all the workbooks in a specified directory, open them, perform some operation on them and then close them?
	Is it possible to set up a macro such that when a value is entered in a worksheet, the cell to the right side is filled with the entered value plus 5?
	How do I determine if a worksheet is empty?
	What are some tips on handling data entry on a worksheet?

I am trying to use the Kill function to delete some hidden data worksheets that I created for my application. However, I keep getting a File Not Found error message. What is going wrong?
Keywords: Kill, Functions
Posted November 26, 1996

The Kill function will not operate on hidden files. Therefore you need to use the SetAttr to remove the hidden property from these files before trying to use Kill to delete them. Here's a quick example:

Sub KillHiddenFile()
    SetAttr "d:\files\temp.xls", vbNormal
    Kill "d:\files\temp.xls"
End Sub

I have a home worksheet for my application set up with various buttons. If you miss a button when attempting to click on it you select the cell underneath, and I don't like this. Is there any way to hide or deactivate the cell pointer on this worksheet?
Keywords: User Interface
Posted November 26, 1996

In your Auto_Open routine, set Application.DataEntryMode=xlStrict and the user won't be able to select anything on the worksheet other than your controls. Cancel it whenever you need to allow cell selection with Application.DataEntryMode = xlOff.

How can I cycle through all the workbooks in a specified directory, open them, perform some operation on them and then close them?
Keywords: Workbook Cycle Directory
Posted November 11, 1996

Here's a macro that will do this. Just supply your own path name to the sDirectory variable.

Sub LoopFiles()

    Dim sDirectory As String
    Dim sSpec As String
    Dim sBook As String
    Dim oBook As Workbook
        
    sDirectory = "d:\files"
    sSpec = "*.xls"
    sBook = Dir(sDirectory & "\" & sSpec)

    Do
        Set oBook = Workbooks.Open(sDirectory & "\" & sBook)
            ''' Do your processing on oBook here.
        oBook.Save
        oBook.Close
        sBook = Dir()
    Loop Until sBook = ""
    
End Sub
	

Is it possible to set up a macro such that when a value is entered in a worksheet, the cell to the right side is filled with the entered value plus 5?
Keywords: Automatic Entry
Posted October 14, 1996

You need to write a simple macro and link it to the required worksheet(s) in the Auto_Open() macro.  

   ''' Assign macro to run whenever a value is typed into a cell in ShtData.
   ''' To do this for all worksheets, do it in a loop, or set it at Application level.
Sub Auto_Open()
      ''' Enclose in single quotes to pass a parameter to the macro
   ThisWorkbook.Worksheets("ShtData").OnEntry = "'SetPlusToRight(5)'"
End Sub

Sub SetPlusToRight(dValueToAdd As Double)
   If IsNumeric(ActiveCell.Value) Then
      ActiveCell.Offset(0, 1).Value = ActiveCell.Value + dValueToAdd
   End If
End Sub
	

How do I determine if a worksheet is empty?
Keywords: Check Worksheet Empty
Posted September 11, 1996

Three different methods are shown below in increasing order of preference (that is, the last method is the best). 

  ''' Method 1: Checks if A1 is the last used cell and A1 is empty
Function IsSheetEmpty1(ws As Worksheet)
IsSheetEmpty1 = ws.Cells.SpecialCells(xlLastCell).Address = "$A$1" _
   And IsEmpty(ws.Cells(1, 1))
End Function
			

  ''' Method 2: Checks if number of non-blank cells is zero
Function IsSheetEmpty2(ws As Worksheet)
IsSheetEmpty2 = (Application.CountA(ws.Cells) = 0)
End Function
			

  ''' Method 3: Checks if the worksheet is empty!
Function IsSheetEmpty(ws As Worksheet)
IsSheetEmpty = IsEmpty(ws.UsedRange)
End Function
			

What are some tips on handling data entry on a worksheet?
Keywords: Data Entry Input
Posted April 22, 1996

Whenever possible, use a dialog box to allow for easy, validated data entry. That said, you often face the need to accept input on a worksheet (typically when there are many fields to input). In such situations, you should attempt to make it easy as possible for your user to enter data and know of any errors. Here are a few ideas to get you started. You can also download our sample workbook that explains and illustrates each of these ideas, along with the pros and cons of different approaches.

1. Adopt a consistent color scheme. At the least, you need three different formats, namely, input cells, their labels (captions) and calculated display fields
2. Use Styles to apply cell formats instead of setting properties for selected cells
3. Make use of dropdowns, option button groups, check boxes and command buttons to minimize typing and make data entry less painful
4. It is possible to write short, generic routines to handle the above controls as shown in the sample workbook
5. For repetitive input choices, consider a data entry toolbar with buttons for typical entries
6. One way to implement graceful error handling is not to use the input fields directly in other formulas. Have another, hidden field which has a simple IF(...) formula to take care of invalid input.

Back to Top

Back to the FAQ Table of Contents

Do they have reserved parking for non-handicap people at the Special Olympics?