Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 26
Default Auto open Data Form

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

--
ypukpete
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,942
Default Auto open Data Form

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

regards
FSt1

"ypukpete" wrote:

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

--
ypukpete

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 26
Default Auto open Data Form

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


"FSt1" wrote:

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

regards
FSt1

"ypukpete" wrote:

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

--
ypukpete

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,069
Default Auto open Data Form

Give this approach a try:

Sub Auto_Open()

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

--
jb


"ypukpete" wrote:

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

--
ypukpete

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 26
Default Auto open Data Form

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

--
ypukpete


"john" wrote:

Give this approach a try:

Sub Auto_Open()

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

--
jb


"ypukpete" wrote:

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

--
ypukpete



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Auto open Data Form

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...;en-us;q110462
XL: ShowDataForm Method Fails If Data Can't Be Found

ypukpete wrote:

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

--
ypukpete


--

Dave Peterson
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 26
Default Auto open Data Form

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


"Dave Peterson" wrote:

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...;en-us;q110462
XL: ShowDataForm Method Fails If Data Can't Be Found

ypukpete wrote:

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

--
ypukpete


--

Dave Peterson

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Auto open Data Form

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.



ypukpete wrote:

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

"Dave Peterson" wrote:

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...;en-us;q110462
XL: ShowDataForm Method Fails If Data Can't Be Found

ypukpete wrote:

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

--
ypukpete


--

Dave Peterson


--

Dave Peterson
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 26
Default Auto open Data Form

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


"Dave Peterson" wrote:

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.



ypukpete wrote:

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

"Dave Peterson" wrote:

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...;en-us;q110462
XL: ShowDataForm Method Fails If Data Can't Be Found

ypukpete wrote:

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

--
ypukpete

--

Dave Peterson


--

Dave Peterson

  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,069
Default Auto open Data Form

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 Auto_Open()

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


"ypukpete" wrote:

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


"Dave Peterson" wrote:

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.



ypukpete wrote:

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

"Dave Peterson" wrote:

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...;en-us;q110462
XL: ShowDataForm Method Fails If Data Can't Be Found

ypukpete wrote:

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

--
ypukpete

--

Dave Peterson


--

Dave Peterson



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto saving data from a Word form to Excel Savannah Dan Excel Discussion (Misc queries) 3 September 14th 09 11:59 PM
Auto refreshing and printing a form with data from each row of a spreadsheet Krondys Excel Discussion (Misc queries) 0 April 19th 06 02:44 PM
Auto fill form with data from worksheet mg_sv_r Excel Programming 2 January 17th 06 02:37 PM
edit data while form open questy Excel Programming 1 September 7th 04 01:48 AM
Ascending data form auto-entry? lykwid[_7_] Excel Programming 2 January 24th 04 02:38 PM


All times are GMT +1. The time now is 04:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"