Data Form

  • Thread starter Thread starter Melanie
  • Start date Start date
M

Melanie

Hi,

I have put together a workbook with 4 tabs (one for each
week) for our salesmen to keep track of their sales
calls. I found a macro that would allow the dataform to
open only for the first sheet when the file is opened. I
would need the form to appear when they change tabs. Is
this possible? Is it possible to have them only enter the
information on the dataform and not the workbook?

Thanks for your help.
 
When you use Data|Form, excel looks in a certain range (either a range named
database or cells a1:b2) for data. If it doesn't find it, it blows up good.

Here's an article that explains how the problem occurs in VBA (with solution)
http://support.microsoft.com/default.aspx?scid=KB;en-us;q110462
XL: ShowDataForm Method Fails If Data Can't Be Found

So you could use something like this under the ThisWorkbook module:

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
With Sh
' .Range("a1").CurrentRegion.Name = "'" & .Name & "'!database"
.ShowDataForm
End With
End Sub

I commented out the naming of the data. If your data doesn't start in A1:B2,
you'll want to name your range.
 
Melanie,

the DataForm acts as a data input tool for the current region. You need to
switch between sheets, move to an appropriate area and then open the data
form.

Alternatively, put all your data on one sheet and filter for the correct
week.

To do what you ask is possible, but why don't you just add a button to the
toolbar?

Steve
 
Back
Top