Auto open Data Form

[ypukpete]

I wish to auto open excel's built in dataform and if possible hide the
worksheet it relates to.
I have tried Dave Peterson's...
Sub Autpen
Set wks = Worksheets("Sheet2")
With wks
Application.DisplayAlerts = False
..ShowDataForm
Application.DisplayAlerts = True
End With
End Sub

But had no success, the code breaks at .ShowDataForm
I would like to show the dataform and hide worksheet2 if possible
I am using excel 2000
Thanks in advance for your help with this.

 

[FSt1]

do you have data on sheet2? if not the macro will crash at .showdataform.

regards
FSt1

 

[ypukpete]

Yes..the data I am using is in Sheet2 and the dataform is operating OK

 

[john]

Give this approach a try:

Sub Autpen()

Set wks = Worksheets("Sheet2")

Application.DisplayAlerts = False

On Error GoTo myerror
With wks

.Activate
.Range("A1").Select

.ShowDataForm

End With

myerror:
If Err > 0 Then
MsgBox (Error(Err))
Err.Clear
End If

Application.DisplayAlerts = True

End Sub

 

[ypukpete]

Hi John
Tried your approach
get MsgBox saying....Method"ShowDataForm"of object_Worksheet Failed

 

[Dave Peterson]

I bet your data isn't where excel expects it to be.

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

 

[ypukpete]

Thanks John
Your code led to the error report
Thanks Dave, you won your bet, moved the sheet to A1:B2
both methods work fine now.
Can I now ask how to hide Sheet2 with the DataForm displayed?

 

[Dave Peterson]

Just hide it manually (when you're wearing your developer's hat).

Your code doesn't need to have the worksheet visible to work.

If you only have that single sheet in the workbook, I'd add another worksheet
(named Instructions) with a button from the Forms toolbar that runs that macro.

And add some instructions for those hard to fill out fields.

Thanks John
Your code led to the error report
Thanks Dave, you won your bet, moved the sheet to A1:B2
both methods work fine now.
Can I now ask how to hide Sheet2 with the DataForm displayed?

 

[ypukpete]

Thanks for advice
must wear my developers hat more often
might make life easier.
Regards

 

[john]

sorry did not respond to your questions but I have been having problems
accessing this site although Dave appears to have solved your problem.

You can amend the code I posted to hide the sheet as follows:


Sub Autpen()

Set wks = Worksheets("Sheet2")

Application.DisplayAlerts = False

On Error GoTo myerror
With wks

.Activate
.Range("A1").Select
.Visible = False
.ShowDataForm

End With

myerror:
If Err > 0 Then
MsgBox (Error(Err))
Err.Clear
End If

Application.DisplayAlerts = True

End Sub
--
jb

Thanks for advice
must wear my developers hat more often
might make life easier.
Regards

 

[ypukpete]

Thanks John
This will complete and tidy up my project

 

[Dave Peterson]

Just curious...

Why would you want to hide the worksheet in code? Why not just prepare the
"look and feel" of the workbook the way you like before sharing with the other
users?

 

[ypukpete]

Yes i think I got on the wrong track a bit.
The data I imported (a client database) was from an older system that was
real ugly (Sheet2)
I edited it to fit the project I was doing but still was not a good look.
Hence my decision to hide it.
I have hidden sheet2 in its properties window
and have the dataform displaying in a new sheet with some instructions on
how to use it.
which is what you suggested.
The project has a form for adding and finding new data including the dataform
plus a few pivot tables.
The users wanted something user friendly they could understand.
However sometimes you can't see the wood for the trees which is why I
initially
wanted to hide it using VB
Thanks for your interest and help.
Regards

 

[Dave Peterson]

If you want to hide things from the users, I think it's better to set it up
before hand.

But for me, I'd rather see the sheet and do my editing there. It's nice to just
copy|paste, filldown, ... all that stuff.

And if your data ever grows too big (wide) for Data|Form, you may want to look
at John Walkenbach's enhanced data form:
http://spreadsheetpage.com/index.php/dataform/home

Yes i think I got on the wrong track a bit.
The data I imported (a client database) was from an older system that was
real ugly (Sheet2)
I edited it to fit the project I was doing but still was not a good look.
Hence my decision to hide it.
I have hidden sheet2 in its properties window
and have the dataform displaying in a new sheet with some instructions on
how to use it.
which is what you suggested.
The project has a form for adding and finding new data including the dataform
plus a few pivot tables.
The users wanted something user friendly they could understand.
However sometimes you can't see the wood for the trees which is why I
initially
wanted to hide it using VB
Thanks for your interest and help.
Regards
--
ypukpete

Just curious...

Why would you want to hide the worksheet in code? Why not just prepare the
"look and feel" of the workbook the way you like before sharing with the other
users?

 

[ypukpete]

Sorry for not getting back
Enhanced form looks good but havn't tried it yet.
Yes my project could have been done better.
I will put it down to experience
Thanks for your help
Regards

--
ypukpete

If you want to hide things from the users, I think it's better to set it up
before hand.

But for me, I'd rather see the sheet and do my editing there. It's nice to just
copy|paste, filldown, ... all that stuff.

And if your data ever grows too big (wide) for Data|Form, you may want to look
at John Walkenbach's enhanced data form:
http://spreadsheetpage.com/index.php/dataform/home

Yes i think I got on the wrong track a bit.
The data I imported (a client database) was from an older system that was
real ugly (Sheet2)
I edited it to fit the project I was doing but still was not a good look.
Hence my decision to hide it.
I have hidden sheet2 in its properties window
and have the dataform displaying in a new sheet with some instructions on
how to use it.
which is what you suggested.
The project has a form for adding and finding new data including the dataform
plus a few pivot tables.
The users wanted something user friendly they could understand.
However sometimes you can't see the wood for the trees which is why I
initially
wanted to hide it using VB
Thanks for your interest and help.
Regards