| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto open Data Form
Thanks John
This will complete and tidy up my project -- ypukpete "john" wrote: 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto open Data Form
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 wrote: Thanks John This will complete and tidy up my project -- ypukpete "john" wrote: 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 -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto open Data Form
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 "Dave Peterson" wrote: 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 wrote: Thanks John This will complete and tidy up my project -- ypukpete "john" wrote: 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 -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto open Data Form
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 ypukpete wrote: 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 "Dave Peterson" wrote: 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 wrote: Thanks John This will complete and tidy up my project -- ypukpete "john" wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto open Data Form
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 "Dave Peterson" wrote: 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 ypukpete wrote: 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 "Dave Peterson" wrote: 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 wrote: Thanks John This will complete and tidy up my project -- ypukpete "john" wrote: 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 -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto saving data from a Word form to Excel | Excel Discussion (Misc queries) | |||
| Auto refreshing and printing a form with data from each row of a spreadsheet | Excel Discussion (Misc queries) | |||
| Auto fill form with data from worksheet | Excel Programming | |||
| edit data while form open | Excel Programming | |||
| Ascending data form auto-entry? | Excel Programming | |||
Linear Mode
