Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Required
I have a spreadsheet that on opening for first time requires the input of
text (a venue) in one cell and the Date in another. On opening, I'd like to be prompted to input the venue then prompted to input the date. Appreciate help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Required
in the development environment (ALT+F11)
from the Project Explorer, right click the ThisWorkbook object and select View Code - in fact double clicking it opens its code window. select 'Workbook' in th eobject list Excel adds the _Open event add the code and edit it appropriately Private Sub Workbook_Open() Worksheets("Sheet2").Range("A1") = InputBox("Please enter the date") End Sub "barrowhill" wrote: I have a spreadsheet that on opening for first time requires the input of text (a venue) in one cell and the Date in another. On opening, I'd like to be prompted to input the venue then prompted to input the date. Appreciate help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Required
Patrick,
Thanks. Not quite what I had in mind. Wanted a message box to appear into which I'd input my text. On clicking "OK" this would then be written to the cell (B2). I'd then be given another message box into which I'd input the date. This would then be entered into cell E2 when "OK" clicked. "Patrick Molloy" wrote: in the development environment (ALT+F11) from the Project Explorer, right click the ThisWorkbook object and select View Code - in fact double clicking it opens its code window. select 'Workbook' in th eobject list Excel adds the _Open event add the code and edit it appropriately Private Sub Workbook_Open() Worksheets("Sheet2").Range("A1") = InputBox("Please enter the date") End Sub "barrowhill" wrote: I have a spreadsheet that on opening for first time requires the input of text (a venue) in one cell and the Date in another. On opening, I'd like to be prompted to input the venue then prompted to input the date. Appreciate help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Required
in the development environment (ALT+F11)
from the Project Explorer, right click the ThisWorkbook object and select View Code - in fact double clicking it opens its code window. A short-cut for this procedure (assuming you are *not* using XL2007) is to right-click the XL icon immediately to the left of the File menu item and select View Code... the VB editor will open up and you will be placed directly inside the code window for ThisWorkbook. -- Rick (MVP - Excel) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Required
Except for the the cell being assigned to (you didn't tells us that in your
first posting, but you should be able to change that on your own) and the fact that only one question was asked (you should be able to add the second question easy enough by just duplication the first one), what about Patrick's solution didn't work for you? -- Rick (MVP - Excel) "Patrick Molloy" wrote in message ... in the development environment (ALT+F11) from the Project Explorer, right click the ThisWorkbook object and select View Code - in fact double clicking it opens its code window. select 'Workbook' in th eobject list Excel adds the _Open event add the code and edit it appropriately Private Sub Workbook_Open() Worksheets("Sheet2").Range("A1") = InputBox("Please enter the date") End Sub "barrowhill" wrote: I have a spreadsheet that on opening for first time requires the input of text (a venue) in one cell and the Date in another. On opening, I'd like to be prompted to input the venue then prompted to input the date. Appreciate help. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Required
Except for the the cell being assigned to (you didn't tells us that in your
first posting, but you should be able to change that on your own) and the fact that only one question was asked (you should be able to add the second question easy enough by just duplication the first one), what about Patrick's solution didn't work for you? -- Rick (MVP - Excel) "barrowhill" wrote in message ... Patrick, Thanks. Not quite what I had in mind. Wanted a message box to appear into which I'd input my text. On clicking "OK" this would then be written to the cell (B2). I'd then be given another message box into which I'd input the date. This would then be entered into cell E2 when "OK" clicked. "Patrick Molloy" wrote: in the development environment (ALT+F11) from the Project Explorer, right click the ThisWorkbook object and select View Code - in fact double clicking it opens its code window. select 'Workbook' in th eobject list Excel adds the _Open event add the code and edit it appropriately Private Sub Workbook_Open() Worksheets("Sheet2").Range("A1") = InputBox("Please enter the date") End Sub "barrowhill" wrote: I have a spreadsheet that on opening for first time requires the input of text (a venue) in one cell and the Date in another. On opening, I'd like to be prompted to input the venue then prompted to input the date. Appreciate help. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Required
Sorry, I posted this to your message by mistake.
-- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Except for the the cell being assigned to (you didn't tells us that in your first posting, but you should be able to change that on your own) and the fact that only one question was asked (you should be able to add the second question easy enough by just duplication the first one), what about Patrick's solution didn't work for you? -- Rick (MVP - Excel) "Patrick Molloy" wrote in message ... in the development environment (ALT+F11) from the Project Explorer, right click the ThisWorkbook object and select View Code - in fact double clicking it opens its code window. select 'Workbook' in th eobject list Excel adds the _Open event add the code and edit it appropriately Private Sub Workbook_Open() Worksheets("Sheet2").Range("A1") = InputBox("Please enter the date") End Sub "barrowhill" wrote: I have a spreadsheet that on opening for first time requires the input of text (a venue) in one cell and the Date in another. On opening, I'd like to be prompted to input the venue then prompted to input the date. Appreciate help. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Required
Rickm
Should of said I was using Excel 2003. In answer to your question, No and Yes !. I've added the main code line with obviously changed parameters to and existing procedure. On opening I don't get the input box requesting my text/date. Hence Answer No. If I run in debug mode and run to curser where curser is set after 'End Sub' it works. Hence answer Yes. Perplexed!?! "Rick Rothstein" wrote: Sorry, I posted this to your message by mistake. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Except for the the cell being assigned to (you didn't tells us that in your first posting, but you should be able to change that on your own) and the fact that only one question was asked (you should be able to add the second question easy enough by just duplication the first one), what about Patrick's solution didn't work for you? -- Rick (MVP - Excel) "Patrick Molloy" wrote in message ... in the development environment (ALT+F11) from the Project Explorer, right click the ThisWorkbook object and select View Code - in fact double clicking it opens its code window. select 'Workbook' in th eobject list Excel adds the _Open event add the code and edit it appropriately Private Sub Workbook_Open() Worksheets("Sheet2").Range("A1") = InputBox("Please enter the date") End Sub "barrowhill" wrote: I have a spreadsheet that on opening for first time requires the input of text (a venue) in one cell and the Date in another. On opening, I'd like to be prompted to input the venue then prompted to input the date. Appreciate help. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Required
Do you perhaps have your security settings set to not run macros?
-- Rick (MVP - Excel) "barrowhill" wrote in message ... Rickm Should of said I was using Excel 2003. In answer to your question, No and Yes !. I've added the main code line with obviously changed parameters to and existing procedure. On opening I don't get the input box requesting my text/date. Hence Answer No. If I run in debug mode and run to curser where curser is set after 'End Sub' it works. Hence answer Yes. Perplexed!?! "Rick Rothstein" wrote: Sorry, I posted this to your message by mistake. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Except for the the cell being assigned to (you didn't tells us that in your first posting, but you should be able to change that on your own) and the fact that only one question was asked (you should be able to add the second question easy enough by just duplication the first one), what about Patrick's solution didn't work for you? -- Rick (MVP - Excel) "Patrick Molloy" wrote in message ... in the development environment (ALT+F11) from the Project Explorer, right click the ThisWorkbook object and select View Code - in fact double clicking it opens its code window. select 'Workbook' in th eobject list Excel adds the _Open event add the code and edit it appropriately Private Sub Workbook_Open() Worksheets("Sheet2").Range("A1") = InputBox("Please enter the date") End Sub "barrowhill" wrote: I have a spreadsheet that on opening for first time requires the input of text (a venue) in one cell and the Date in another. On opening, I'd like to be prompted to input the venue then prompted to input the date. Appreciate help. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Required
Rick,
Thanks for your continued help............... Found the problem - me. In copying code I put in wrong place !!. Dumbo. Now working OK. Perhaps you can help on related matter. Having put code line in for input of venue, I've followed this immediately with another code line for input of date. I note the following...... 1. Having input venue this is not input to cell on clicking OK. It's immediatly followed by input box for date. Input date and click OK, both appear in respective cells no problem. What do I need to add to see venue in cell before being asked for date? 2. Cell format for date is eg. '19 September 2009'. I input as '19/09/09' expecting converstion as if I typed directly. Do I have to use correct format on input using input box method? "Rick Rothstein" wrote: Do you perhaps have your security settings set to not run macros? -- Rick (MVP - Excel) "barrowhill" wrote in message ... Rickm Should of said I was using Excel 2003. In answer to your question, No and Yes !. I've added the main code line with obviously changed parameters to and existing procedure. On opening I don't get the input box requesting my text/date. Hence Answer No. If I run in debug mode and run to curser where curser is set after 'End Sub' it works. Hence answer Yes. Perplexed!?! "Rick Rothstein" wrote: Sorry, I posted this to your message by mistake. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Except for the the cell being assigned to (you didn't tells us that in your first posting, but you should be able to change that on your own) and the fact that only one question was asked (you should be able to add the second question easy enough by just duplication the first one), what about Patrick's solution didn't work for you? -- Rick (MVP - Excel) "Patrick Molloy" wrote in message ... in the development environment (ALT+F11) from the Project Explorer, right click the ThisWorkbook object and select View Code - in fact double clicking it opens its code window. select 'Workbook' in th eobject list Excel adds the _Open event add the code and edit it appropriately Private Sub Workbook_Open() Worksheets("Sheet2").Range("A1") = InputBox("Please enter the date") End Sub "barrowhill" wrote: I have a spreadsheet that on opening for first time requires the input of text (a venue) in one cell and the Date in another. On opening, I'd like to be prompted to input the venue then prompted to input the date. Appreciate help. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Required
Put a DoEvents statement after the first quesion. For example...
Worksheets("Sheet2").Range("A1") = InputBox("Please enter the venue") DoEvents Worksheets("Sheet2").Range("A2") = InputBox("Please enter the date") -- Rick (MVP - Excel) "barrowhill" wrote in message ... Rick, Thanks for your continued help............... Found the problem - me. In copying code I put in wrong place !!. Dumbo. Now working OK. Perhaps you can help on related matter. Having put code line in for input of venue, I've followed this immediately with another code line for input of date. I note the following...... 1. Having input venue this is not input to cell on clicking OK. It's immediatly followed by input box for date. Input date and click OK, both appear in respective cells no problem. What do I need to add to see venue in cell before being asked for date? 2. Cell format for date is eg. '19 September 2009'. I input as '19/09/09' expecting converstion as if I typed directly. Do I have to use correct format on input using input box method? "Rick Rothstein" wrote: Do you perhaps have your security settings set to not run macros? -- Rick (MVP - Excel) "barrowhill" wrote in message ... Rickm Should of said I was using Excel 2003. In answer to your question, No and Yes !. I've added the main code line with obviously changed parameters to and existing procedure. On opening I don't get the input box requesting my text/date. Hence Answer No. If I run in debug mode and run to curser where curser is set after 'End Sub' it works. Hence answer Yes. Perplexed!?! "Rick Rothstein" wrote: Sorry, I posted this to your message by mistake. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Except for the the cell being assigned to (you didn't tells us that in your first posting, but you should be able to change that on your own) and the fact that only one question was asked (you should be able to add the second question easy enough by just duplication the first one), what about Patrick's solution didn't work for you? -- Rick (MVP - Excel) "Patrick Molloy" wrote in message ... in the development environment (ALT+F11) from the Project Explorer, right click the ThisWorkbook object and select View Code - in fact double clicking it opens its code window. select 'Workbook' in th eobject list Excel adds the _Open event add the code and edit it appropriately Private Sub Workbook_Open() Worksheets("Sheet2").Range("A1") = InputBox("Please enter the date") End Sub "barrowhill" wrote: I have a spreadsheet that on opening for first time requires the input of text (a venue) in one cell and the Date in another. On opening, I'd like to be prompted to input the venue then prompted to input the date. Appreciate help. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Required
Rick,
Did that but still same as before ? "Rick Rothstein" wrote: Put a DoEvents statement after the first quesion. For example... Worksheets("Sheet2").Range("A1") = InputBox("Please enter the venue") DoEvents Worksheets("Sheet2").Range("A2") = InputBox("Please enter the date") -- Rick (MVP - Excel) "barrowhill" wrote in message ... Rick, Thanks for your continued help............... Found the problem - me. In copying code I put in wrong place !!. Dumbo. Now working OK. Perhaps you can help on related matter. Having put code line in for input of venue, I've followed this immediately with another code line for input of date. I note the following...... 1. Having input venue this is not input to cell on clicking OK. It's immediatly followed by input box for date. Input date and click OK, both appear in respective cells no problem. What do I need to add to see venue in cell before being asked for date? 2. Cell format for date is eg. '19 September 2009'. I input as '19/09/09' expecting converstion as if I typed directly. Do I have to use correct format on input using input box method? "Rick Rothstein" wrote: Do you perhaps have your security settings set to not run macros? -- Rick (MVP - Excel) "barrowhill" wrote in message ... Rickm Should of said I was using Excel 2003. In answer to your question, No and Yes !. I've added the main code line with obviously changed parameters to and existing procedure. On opening I don't get the input box requesting my text/date. Hence Answer No. If I run in debug mode and run to curser where curser is set after 'End Sub' it works. Hence answer Yes. Perplexed!?! "Rick Rothstein" wrote: Sorry, I posted this to your message by mistake. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Except for the the cell being assigned to (you didn't tells us that in your first posting, but you should be able to change that on your own) and the fact that only one question was asked (you should be able to add the second question easy enough by just duplication the first one), what about Patrick's solution didn't work for you? -- Rick (MVP - Excel) "Patrick Molloy" wrote in message ... in the development environment (ALT+F11) from the Project Explorer, right click the ThisWorkbook object and select View Code - in fact double clicking it opens its code window. select 'Workbook' in th eobject list Excel adds the _Open event add the code and edit it appropriately Private Sub Workbook_Open() Worksheets("Sheet2").Range("A1") = InputBox("Please enter the date") End Sub "barrowhill" wrote: I have a spreadsheet that on opening for first time requires the input of text (a venue) in one cell and the Date in another. On opening, I'd like to be prompted to input the venue then prompted to input the date. Appreciate help. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Required
Post the code you have so far so we can test it on our own systems; or, at
least, see where it differs from what we assume it to be. -- Rick (MVP - Excel) "barrowhill" wrote in message ... Rick, Did that but still same as before ? "Rick Rothstein" wrote: Put a DoEvents statement after the first quesion. For example... Worksheets("Sheet2").Range("A1") = InputBox("Please enter the venue") DoEvents Worksheets("Sheet2").Range("A2") = InputBox("Please enter the date") -- Rick (MVP - Excel) "barrowhill" wrote in message ... Rick, Thanks for your continued help............... Found the problem - me. In copying code I put in wrong place !!. Dumbo. Now working OK. Perhaps you can help on related matter. Having put code line in for input of venue, I've followed this immediately with another code line for input of date. I note the following...... 1. Having input venue this is not input to cell on clicking OK. It's immediatly followed by input box for date. Input date and click OK, both appear in respective cells no problem. What do I need to add to see venue in cell before being asked for date? 2. Cell format for date is eg. '19 September 2009'. I input as '19/09/09' expecting converstion as if I typed directly. Do I have to use correct format on input using input box method? "Rick Rothstein" wrote: Do you perhaps have your security settings set to not run macros? -- Rick (MVP - Excel) "barrowhill" wrote in message ... Rickm Should of said I was using Excel 2003. In answer to your question, No and Yes !. I've added the main code line with obviously changed parameters to and existing procedure. On opening I don't get the input box requesting my text/date. Hence Answer No. If I run in debug mode and run to curser where curser is set after 'End Sub' it works. Hence answer Yes. Perplexed!?! "Rick Rothstein" wrote: Sorry, I posted this to your message by mistake. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Except for the the cell being assigned to (you didn't tells us that in your first posting, but you should be able to change that on your own) and the fact that only one question was asked (you should be able to add the second question easy enough by just duplication the first one), what about Patrick's solution didn't work for you? -- Rick (MVP - Excel) "Patrick Molloy" wrote in message ... in the development environment (ALT+F11) from the Project Explorer, right click the ThisWorkbook object and select View Code - in fact double clicking it opens its code window. select 'Workbook' in th eobject list Excel adds the _Open event add the code and edit it appropriately Private Sub Workbook_Open() Worksheets("Sheet2").Range("A1") = InputBox("Please enter the date") End Sub "barrowhill" wrote: I have a spreadsheet that on opening for first time requires the input of text (a venue) in one cell and the Date in another. On opening, I'd like to be prompted to input the venue then prompted to input the date. Appreciate help. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Required
Rick,
Some more explanation to previous post............ With VBE open the "This Workbook" object in project window contains the code ......... Worksheets("Sheet2").Range("A1") = InputBox("Please enter the venue") DoEvents Worksheets("Sheet2").Range("A2") = InputBox("Please enter the date") ..............in Private Sub Workbook_Open() procedure I created "Module1" and copied the same procedure to this I also created another procedure in Module1 with only the code specified above. a new tool bar button was created -'Input Venue & Date' and (macro) assigned to this procedure. Spreadsheet saved thinking all bases covered. Opening; input message box appears. Type in venue and OK. Venue name does not appear in cell. Next message box appears. Type in date and OK. Venue and date appear in respective cells. Clear cells contents Click newly created button. Input message box appears. Type in venue and OK. Venue name appears in cell. Next message box appears. Type in date and OK. date appears in cell. Same code in "This Workbook" object, Module1 and assigned to toolbar button. Only works with button ? My actual code is.............. Private Sub Workbook_Open() ' On Error Resume Next Application.ScreenUpdating = False ' Workbooks("Book1").Close ThisWorkbook.Activate ' ' Remove unnecessary Tool Bars on Opening ' Application.CommandBars("Formatting").Visible = False Application.CommandBars("Standard").Visible = False ' Application.CommandBars("BUGSModifiedStableford"). Visible = True ' Worksheets("Gross Stableford").Range("B2") = InputBox("Please Enter Venue") DoEvents Worksheets("Gross Stableford").Range("B4") = InputBox("Please Enter Date") End Sub "Rick Rothstein" wrote: Put a DoEvents statement after the first quesion. For example... Worksheets("Sheet2").Range("A1") = InputBox("Please enter the venue") DoEvents Worksheets("Sheet2").Range("A2") = InputBox("Please enter the date") -- Rick (MVP - Excel) "barrowhill" wrote in message ... Rick, Thanks for your continued help............... Found the problem - me. In copying code I put in wrong place !!. Dumbo. Now working OK. Perhaps you can help on related matter. Having put code line in for input of venue, I've followed this immediately with another code line for input of date. I note the following...... 1. Having input venue this is not input to cell on clicking OK. It's immediatly followed by input box for date. Input date and click OK, both appear in respective cells no problem. What do I need to add to see venue in cell before being asked for date? 2. Cell format for date is eg. '19 September 2009'. I input as '19/09/09' expecting converstion as if I typed directly. Do I have to use correct format on input using input box method? "Rick Rothstein" wrote: Do you perhaps have your security settings set to not run macros? -- Rick (MVP - Excel) "barrowhill" wrote in message ... Rickm Should of said I was using Excel 2003. In answer to your question, No and Yes !. I've added the main code line with obviously changed parameters to and existing procedure. On opening I don't get the input box requesting my text/date. Hence Answer No. If I run in debug mode and run to curser where curser is set after 'End Sub' it works. Hence answer Yes. Perplexed!?! "Rick Rothstein" wrote: Sorry, I posted this to your message by mistake. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Except for the the cell being assigned to (you didn't tells us that in your first posting, but you should be able to change that on your own) and the fact that only one question was asked (you should be able to add the second question easy enough by just duplication the first one), what about Patrick's solution didn't work for you? -- Rick (MVP - Excel) "Patrick Molloy" wrote in message ... in the development environment (ALT+F11) from the Project Explorer, right click the ThisWorkbook object and select View Code - in fact double clicking it opens its code window. select 'Workbook' in th eobject list Excel adds the _Open event add the code and edit it appropriately Private Sub Workbook_Open() Worksheets("Sheet2").Range("A1") = InputBox("Please enter the date") End Sub "barrowhill" wrote: I have a spreadsheet that on opening for first time requires the input of text (a venue) in one cell and the Date in another. On opening, I'd like to be prompted to input the venue then prompted to input the date. Appreciate help. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Required
one issue is that you're using
ON ERROR RESUME NEXT this is hiding any issues. so, in the workbook_open event handler, add ON ERROR GOTO 0 just before ThisWorkbook.Activate I suspect that there may be a typo in your worksheet name 'Gross Stableford' ? If the value can't be saved, it would raise an error - by switchign off the trapping, you'll see it now. Expect error #1004 if the range can't be found you don't need DOEVENTS if this was the issue Q2. what is your PCs local date setting? you're inputting a UK date style, but maybe your input box is convertign to a US date. You haven't told us what actually gets put into the cell on the sheet. "barrowhill" wrote: Rick, Some more explanation to previous post............ With VBE open the "This Workbook" object in project window contains the code ........ Worksheets("Sheet2").Range("A1") = InputBox("Please enter the venue") DoEvents Worksheets("Sheet2").Range("A2") = InputBox("Please enter the date") .............in Private Sub Workbook_Open() procedure I created "Module1" and copied the same procedure to this I also created another procedure in Module1 with only the code specified above. a new tool bar button was created -'Input Venue & Date' and (macro) assigned to this procedure. Spreadsheet saved thinking all bases covered. Opening; input message box appears. Type in venue and OK. Venue name does not appear in cell. Next message box appears. Type in date and OK. Venue and date appear in respective cells. Clear cells contents Click newly created button. Input message box appears. Type in venue and OK. Venue name appears in cell. Next message box appears. Type in date and OK. date appears in cell. Same code in "This Workbook" object, Module1 and assigned to toolbar button. Only works with button ? My actual code is.............. Private Sub Workbook_Open() ' On Error Resume Next Application.ScreenUpdating = False ' Workbooks("Book1").Close ThisWorkbook.Activate ' ' Remove unnecessary Tool Bars on Opening ' Application.CommandBars("Formatting").Visible = False Application.CommandBars("Standard").Visible = False ' Application.CommandBars("BUGSModifiedStableford"). Visible = True ' Worksheets("Gross Stableford").Range("B2") = InputBox("Please Enter Venue") DoEvents Worksheets("Gross Stableford").Range("B4") = InputBox("Please Enter Date") End Sub "Rick Rothstein" wrote: Put a DoEvents statement after the first quesion. For example... Worksheets("Sheet2").Range("A1") = InputBox("Please enter the venue") DoEvents Worksheets("Sheet2").Range("A2") = InputBox("Please enter the date") -- Rick (MVP - Excel) "barrowhill" wrote in message ... Rick, Thanks for your continued help............... Found the problem - me. In copying code I put in wrong place !!. Dumbo. Now working OK. Perhaps you can help on related matter. Having put code line in for input of venue, I've followed this immediately with another code line for input of date. I note the following...... 1. Having input venue this is not input to cell on clicking OK. It's immediatly followed by input box for date. Input date and click OK, both appear in respective cells no problem. What do I need to add to see venue in cell before being asked for date? 2. Cell format for date is eg. '19 September 2009'. I input as '19/09/09' expecting converstion as if I typed directly. Do I have to use correct format on input using input box method? "Rick Rothstein" wrote: Do you perhaps have your security settings set to not run macros? -- Rick (MVP - Excel) "barrowhill" wrote in message ... Rickm Should of said I was using Excel 2003. In answer to your question, No and Yes !. I've added the main code line with obviously changed parameters to and existing procedure. On opening I don't get the input box requesting my text/date. Hence Answer No. If I run in debug mode and run to curser where curser is set after 'End Sub' it works. Hence answer Yes. Perplexed!?! "Rick Rothstein" wrote: Sorry, I posted this to your message by mistake. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Except for the the cell being assigned to (you didn't tells us that in your first posting, but you should be able to change that on your own) and the fact that only one question was asked (you should be able to add the second question easy enough by just duplication the first one), what about Patrick's solution didn't work for you? -- Rick (MVP - Excel) "Patrick Molloy" wrote in message ... in the development environment (ALT+F11) from the Project Explorer, right click the ThisWorkbook object and select View Code - in fact double clicking it opens its code window. select 'Workbook' in th eobject list Excel adds the _Open event add the code and edit it appropriately Private Sub Workbook_Open() Worksheets("Sheet2").Range("A1") = InputBox("Please enter the date") End Sub "barrowhill" wrote: I have a spreadsheet that on opening for first time requires the input of text (a venue) in one cell and the Date in another. On opening, I'd like to be prompted to input the venue then prompted to input the date. Appreciate help. |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Required
Patrick,
Error Handling changes as you recommended, but no error shown on opening workbook. Request fro Venue input given but no data entered into cell on OK, Request for Date input box given. Both Venue and Date shown in respective Cells on OK (?) No typo - double checked spelling and any spaces before and after. I've removed the DoEvents. No change. The code (with, as shown, or without DoEvents Worksheets("Gross Stableford").Range("B2") = InputBox("Please Enter Venue") DoEvents Worksheets("Gross Stableford").Range("B4") = InputBox("Please Enter Date") Works perfectly if assigned to a button on my toolbar (useful if I mispell) but not when opening the workbook. ???? "Patrick Molloy" wrote: one issue is that you're using ON ERROR RESUME NEXT this is hiding any issues. so, in the workbook_open event handler, add ON ERROR GOTO 0 just before ThisWorkbook.Activate I suspect that there may be a typo in your worksheet name 'Gross Stableford' ? If the value can't be saved, it would raise an error - by switchign off the trapping, you'll see it now. Expect error #1004 if the range can't be found you don't need DOEVENTS if this was the issue Q2. what is your PCs local date setting? you're inputting a UK date style, but maybe your input box is convertign to a US date. You haven't told us what actually gets put into the cell on the sheet. "barrowhill" wrote: Rick, Some more explanation to previous post............ With VBE open the "This Workbook" object in project window contains the code ........ Worksheets("Sheet2").Range("A1") = InputBox("Please enter the venue") DoEvents Worksheets("Sheet2").Range("A2") = InputBox("Please enter the date") .............in Private Sub Workbook_Open() procedure I created "Module1" and copied the same procedure to this I also created another procedure in Module1 with only the code specified above. a new tool bar button was created -'Input Venue & Date' and (macro) assigned to this procedure. Spreadsheet saved thinking all bases covered. Opening; input message box appears. Type in venue and OK. Venue name does not appear in cell. Next message box appears. Type in date and OK. Venue and date appear in respective cells. Clear cells contents Click newly created button. Input message box appears. Type in venue and OK. Venue name appears in cell. Next message box appears. Type in date and OK. date appears in cell. Same code in "This Workbook" object, Module1 and assigned to toolbar button. Only works with button ? My actual code is.............. Private Sub Workbook_Open() ' On Error Resume Next Application.ScreenUpdating = False ' Workbooks("Book1").Close ThisWorkbook.Activate ' ' Remove unnecessary Tool Bars on Opening ' Application.CommandBars("Formatting").Visible = False Application.CommandBars("Standard").Visible = False ' Application.CommandBars("BUGSModifiedStableford"). Visible = True ' Worksheets("Gross Stableford").Range("B2") = InputBox("Please Enter Venue") DoEvents Worksheets("Gross Stableford").Range("B4") = InputBox("Please Enter Date") End Sub "Rick Rothstein" wrote: Put a DoEvents statement after the first quesion. For example... Worksheets("Sheet2").Range("A1") = InputBox("Please enter the venue") DoEvents Worksheets("Sheet2").Range("A2") = InputBox("Please enter the date") -- Rick (MVP - Excel) "barrowhill" wrote in message ... Rick, Thanks for your continued help............... Found the problem - me. In copying code I put in wrong place !!. Dumbo. Now working OK. Perhaps you can help on related matter. Having put code line in for input of venue, I've followed this immediately with another code line for input of date. I note the following...... 1. Having input venue this is not input to cell on clicking OK. It's immediatly followed by input box for date. Input date and click OK, both appear in respective cells no problem. What do I need to add to see venue in cell before being asked for date? 2. Cell format for date is eg. '19 September 2009'. I input as '19/09/09' expecting converstion as if I typed directly. Do I have to use correct format on input using input box method? "Rick Rothstein" wrote: Do you perhaps have your security settings set to not run macros? -- Rick (MVP - Excel) "barrowhill" wrote in message ... Rickm Should of said I was using Excel 2003. In answer to your question, No and Yes !. I've added the main code line with obviously changed parameters to and existing procedure. On opening I don't get the input box requesting my text/date. Hence Answer No. If I run in debug mode and run to curser where curser is set after 'End Sub' it works. Hence answer Yes. Perplexed!?! "Rick Rothstein" wrote: Sorry, I posted this to your message by mistake. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Except for the the cell being assigned to (you didn't tells us that in your first posting, but you should be able to change that on your own) and the fact that only one question was asked (you should be able to add the second question easy enough by just duplication the first one), what about Patrick's solution didn't work for you? -- Rick (MVP - Excel) "Patrick Molloy" wrote in message ... in the development environment (ALT+F11) from the Project Explorer, right click the ThisWorkbook object and select View Code - in fact double clicking it opens its code window. select 'Workbook' in th eobject list Excel adds the _Open event add the code and edit it appropriately Private Sub Workbook_Open() Worksheets("Sheet2").Range("A1") = InputBox("Please enter the date") End Sub "barrowhill" wrote: I have a spreadsheet that on opening for first time requires the input of text (a venue) in one cell and the Date in another. On opening, I'd like to be prompted to input the venue then prompted to input the date. Appreciate help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help required .... | Excel Worksheet Functions | |||
VBA Help required...................! | Excel Programming | |||
VBA Help required..........! | Excel Programming | |||
Bit of help required. | Excel Programming | |||
New to VBA - help required please. | Excel Programming |