If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Hi Julie
For some unknown reason I cannot find your last message to me or my last message to you. My problem with the code was using "NewInput" instead of "New Input" which was the correct name of my worksheet. Perhaps those messages go somewhere else after so many entries in a newsgroup regarding the same subject? Hope this gets to you. Vince -----Original Message----- wrote in message ... -----Original Message----- "VJ7777" wrote in message ... -----Original Message----- Hi Vince you can also put code against the THISWORKBOOK object on the workbook_open event (double click on ThisWorkbook in the VBE window, choose Workbook from the lhs drop down & Open from the right), you'll need to edit the code to: If IsEmpty(Sheets("NewInput").Range("B2")) then Sheets("NewInput").Range("B2").Value = Format (Now, "dd mmmm yyyy") End If hope this helps Cheers JulieD "VJ7777" wrote in message ... -----Original Message----- Hi Vince to place the code in the workbook, right mouse click on any sheet tab and choose view code. This will display the VB Editor ... on the top left there should be a little area with folders & sheets & the name of your workbook in bold - if not, choose View / Project Explorer from the menu. under the project with your workbook name in bold, you will see "NewInput" and the rest of your sheets - double click on the NewInput one, and a white piece of paper should appear on the right hand side of the screen , up the top are two drop down boxes, choose worksheet from the lhs one and Activate from the right then the words Private Sub Worksheet_Activate() End Sub should appear .. place my code between these two lines. Change the A1 to B2 You can "switch" between the code window & your workbook by using Alt & F1l .... switch back, ensure B2 on the NewInput sheet has nothing in it, switch to another sheet, come back to the NewInput sheet the date should be filled in. Now you can test out both bits of code i gave you to decide which one is the one you want. (by the way, it is always a good idea to make a backup testing any code etc) With regards to your other questions, i'm still having a look at them, but i've got a few other things on at the moment and havent' really had time to sit down & think about them properly. If you haven't done so already you might like to do a search of google (groups.google.com - advanced search: search string changing workbook names formulas ... search groups microsoft.public.excel* ), i would do it for you and see if i come up with anything but my internet explorer's not working at the moment Hope this helps Cheers JulieD wrote in message ... -----Original Message----- Hi can't help (yet) with the other questions but 4. Finally, on another subject, in a cell, the formula "=Today()" enters today's date in a cell automatically. Is there a way enter today's date automatically the first time a worksheet in a workbook is handled but this date is never changed no matter how many times the worksheet is modified? you can put code against each sheet (itself) in the vbe window similar to Private Sub Worksheet_Activate() If IsEmpty(Range("A1")) Then 'change cell address as required Range("A1").Value = Now End If End Sub or Private Sub Worksheet_Activate() If IsEmpty(Range("A1")) Then 'change cell address as required Range("A1").Value = Format(Now, "dd mmmm yyyy") End If End Sub Hope this helps Cheers JulieD "VJ7777" wrote in message news:243901c48de1$92864e90 ... Please forgive me if I don't use proper Newsgroup protocol. This is my first time. I have developed a comprehensive dealership system using Excel. I probably should have used Access but I don't want to redo the system yet. Everything works swimmingly except: 1. I use customer name to name each workbook (which contains multiple worksheets of forms and data pertaining to that customer): For example: "Jones," "Smith" (or "Smith.John"), etc. I have created reports which have a row for each customer and ten or more columns of data for each customer. I get the info for each column from the customer workbook and worksheet using formulas like the one illustrated below (ignore the complexity of the formula; I merely want to automatically substitute "Smith" for "Jones" from a separate master list of customer names): Prospective Business Report (Sample Column Headings): Name Product PurchaseDate Cost Sell etc., etc. Example of one of many formulas: IF('[Jones.xls]Buyer Progress Record'! $B$12="failed","F",IF('[Jones.xls]Buyer Progress Record'! $B$8="Cash Sale",'[Jones.xls]Buyer Progress Record'! $B$8,IF('[Jones.xls]Buyer Progress Record'! $B$11" ?","$"&'[Jones.xls]Buyer Progress Record'!$B$11,IF('[Jones.xls]Buyer Progress Record'! $B$10=" ?","?",IF('[Jones.xls] Buyer Progress Record'! $B$10$AA$2,"! ! ! ! !",'[Jones.xls] Buyer Progress Record'!$B$10))))) Everything works well so long as I copy down and replace (i.e. replace "Jones" with "Smith") to add a new customer row to the report. I cannot find a way to use a master list of customer names to automatically change "Jones" to "Smith" to "Green," etc. 2. I would like to produce a Contact Report by Salesperson (using the above mentioned master list of customer names) except that there would be multiple rows for each customer. The source, Buyer Progress Record worksheet, contains up to 40 lines of contacts in Rows 28 through 68 and, perhaps, only one or two rows might contain data at any moment in time (Row 28 and Row 29; the remaining rows will be used as time marches on). The desired report would look like this and only rows for each customer that contain data: Contact Report - Salesperson Murphy - Aug 17, 2004 Customer Name "Jones" Date of Contact Date of Next Contact Description Date of Contact Date of Next Contact Description Date of Contact Date of Next Contact Description Customer Name "Smith" Date of Contact Date of Next Contact Description Date of Contact Date of Next Contact Description Date of Contact Date of Next Contact Description Date of Contact Date of Next Contact Description Date of Contact Date of Next Contact Description Customer Name "Green" Date of Contact Date of Next Contact Description 3. It would be nice to be able to add to the master list of customer names automatically by having the computer look to see if any new customer workbooks have been added to the "Customers 2004" folder each day. 4. Finally, on another subject, in a cell, the formula "=Today()" enters today's date in a cell automatically. Is there a way enter today's date automatically the first time a worksheet in a workbook is handled but this date is never changed no matter how many times the worksheet is modified? Thank you in advance for any help you can render. Hi, Julie: Thank you , very much for your response. I must confess complete lack of knowledge on how to start implementing your code. I have been in the computer industry since the beginning (about th etimeofEniac.Itaught myself how to read and modify Fortran code when I was product manager of a capital investment evaluation model for Fortune 500 companies using Computer Sciences' time sharing system. I taught myself how to program in Basic when I had the first Apple computer and, for fun, designed my own General Ledger system. On the other hand, I have pulled out the Visual Basic book multiple times and, as I did tonight, asked myself why I should punish myself when Microsoft's explanation never seems to explain a place to begin. If it isn't asking too much, could you please tell me what steps to take to simply make your code work in cell "B2" of worksheet "New Input" of workbook "aaCustomer." Maybe, if you do that, I can get the concept of how to begin to use VB - certainly I have never gotten a clue from the Microsoft VB book. Your message seemed to indicate that you might have answers to my other questions - and I suspect VB will be a major part of your answer. If so, I will need to know how to intergrate them into Excel; your response regarding to how to implement this will be very necessary to understanding your other solutions. Thanks again, Vince .Julie, thanks a million. I'll try the google search later today. One quick question: The date appears OK after I switch to another worksheet and back again, but, suppose one doesn't go to another worksheet in today's data entry session; how does today's date get into the cell. I've tried saving the workbook and re- opening it but the cell remains blank unless I switch to another worksheet and back again Again, I really appreciate your help!!! Vince .Hi Julie: I'm afraid I am asking too much of you - it isn't your task in life to train me in VB. I scan the book and it seems to avoid simple tasks. I've tried Google for a list of commands (for example: goto - or moveto, etc. - so I can say 'if whatever, goto "a1")' and cannot find such a list. If you point me in the right direction I'll go there and study. Having started life as a bookkeeping machine salesperson I am strong on applications and look to programming as a list of simple commands to copy into a program to get my job done. Having said that, the code you gave me generated "Run- time error '9': Subscript out of range." Here is the code I entered: Private Sub Workbook_Open() If IsEmpty(Sheets("NewInput").Range("B2")) Then Sheets("NewInput").Range("B2").Value = Format (Now, "dd mmmm yyyy") End If End Sub If you will help one more time with the above questions I'll go it alone from that point. But will hope you can solve the first three questions in my original posting when you get the time. (I'll do the Google search you recommended before or during the Labor Day weekend.) Again, thank you very much. Vince Hi Vince i'm happy to help so don't worry about asking questions .. have you got a sheet in the workbook called "NewInput" as the code runs fine when i copy & paste it into a new workbook with a sheet called NewInput (no spaces) ... is the code in the THISWORKBOOK object of the project explorer and not in a module sheet is the format stuff all on one line? as to the other 3 questions i will get to them when i get a chance. Looking forward to hearing from you. Cheers JulieD .Hi Julie Please forgive my stupidity. While shaving I realized my worksheet is New_Input; not, NewInput. I made the change and it worked fine. Do you know a source for VB commands such as "goto"? Thanks again. Vince Hi Vince glad its working .. basically the way you need to approach VBA is to ask yourself what object am i looking at and what do i want to do with it, so if it's a cell (e.g. A1), then you need to know that cells are part of the range object and that range objects have a select method, therefore to select a cell you can use Range("A1").select however, if you want to select a cell on another sheet to need to make the sheet active & then select the cell Sheets("Sheet1").activate Range("A1").select however, you can populate a cell without selecting it Sheets("Sheet1").Range("A1").value = 5 will put the number 5 into cell A1 on Sheet1 the best place to find all this out is using the Object Browser in VBA (view / object browser) or type "microsoft excel object model" into Help in the VBE .. this will give you all the objects you can then click on them to find out their properties & methods. Hope this helps Cheers JulieD . |
#12
|
|||
|
|||
"VJ7777" wrote in message ... Hi Julie For some unknown reason I cannot find your last message to me or my last message to you. My problem with the code was using "NewInput" instead of "New Input" which was the correct name of my worksheet. Perhaps those messages go somewhere else after so many entries in a newsgroup regarding the same subject? Hope this gets to you. Vince i can still see them, here's my response to your last post: .Hi Julie Please forgive my stupidity. While shaving I realized my worksheet is New_Input; not, NewInput. I made the change and it worked fine. Do you know a source for VB commands such as "goto"? Thanks again. Vince Hi Vince glad its working .. basically the way you need to approach VBA is to ask yourself what object am i looking at and what do i want to do with it, so if it's a cell (e.g. A1), then you need to know that cells are part of the range object and that range objects have a select method, therefore to select a cell you can use Range("A1").select however, if you want to select a cell on another sheet to need to make the sheet active & then select the cell Sheets("Sheet1").activate Range("A1").select however, you can populate a cell without selecting it Sheets("Sheet1").Range("A1").value = 5 will put the number 5 into cell A1 on Sheet1 the best place to find all this out is using the Object Browser in VBA (view / object browser) or type "microsoft excel object model" into Help in the VBE .. this will give you all the objects you can then click on them to find out their properties & methods. Hope this helps Cheers JulieD |
#13
|
|||
|
|||
-----Original Message----- "VJ7777" wrote in message ... Hi Julie For some unknown reason I cannot find your last message to me or my last message to you. My problem with the code was using "NewInput" instead of "New Input" which was the correct name of my worksheet. Perhaps those messages go somewhere else after so many entries in a newsgroup regarding the same subject? Hope this gets to you. Vince i can still see them, here's my response to your last post: .Hi Julie Please forgive my stupidity. While shaving I realized my worksheet is New_Input; not, NewInput. I made the change and it worked fine. Do you know a source for VB commands such as "goto"? Thanks again. Vince Hi Vince glad its working .. basically the way you need to approach VBA is to ask yourself what object am i looking at and what do i want to do with it, so if it's a cell (e.g. A1), then you need to know that cells are part of the range object and that range objects have a select method, therefore to select a cell you can use Range("A1").select however, if you want to select a cell on another sheet to need to make the sheet active & then select the cell Sheets("Sheet1").activate Range("A1").select however, you can populate a cell without selecting it Sheets("Sheet1").Range("A1").value = 5 will put the number 5 into cell A1 on Sheet1 the best place to find all this out is using the Object Browser in VBA (view / object browser) or type "microsoft excel object model" into Help in the VBE .. this will give you all the objects you can then click on them to find out their properties & methods. Hope this helps Cheers JulieD . Hi Julie I haven't tried your last advice because I got a pinched nerve and couldn't sit down for a number of days. I could use my laptop by standing up at the kitchen counter which killed my back but I don't have Internet access in the kitchen. I won't be able to get back to the Excel project for several more days. I don't want to lose touch with you. Is there a way to move our correspondence forward or is there a quick way to get to what is now page 40 or so? And, is there a quick way to know when you respond to my questions? I do appreciate your help. Vince |
#14
|
|||
|
|||
Hi Vince
ouch .. .had one of those myself a few years back - not good ... it might be easier if you email just me direct - julied_ng at hcts dot net dot au Cheers JulieD "VJ7777" wrote in message ... -----Original Message----- "VJ7777" wrote in message ... Hi Julie For some unknown reason I cannot find your last message to me or my last message to you. My problem with the code was using "NewInput" instead of "New Input" which was the correct name of my worksheet. Perhaps those messages go somewhere else after so many entries in a newsgroup regarding the same subject? Hope this gets to you. Vince i can still see them, here's my response to your last post: .Hi Julie Please forgive my stupidity. While shaving I realized my worksheet is New_Input; not, NewInput. I made the change and it worked fine. Do you know a source for VB commands such as "goto"? Thanks again. Vince Hi Vince glad its working .. basically the way you need to approach VBA is to ask yourself what object am i looking at and what do i want to do with it, so if it's a cell (e.g. A1), then you need to know that cells are part of the range object and that range objects have a select method, therefore to select a cell you can use Range("A1").select however, if you want to select a cell on another sheet to need to make the sheet active & then select the cell Sheets("Sheet1").activate Range("A1").select however, you can populate a cell without selecting it Sheets("Sheet1").Range("A1").value = 5 will put the number 5 into cell A1 on Sheet1 the best place to find all this out is using the Object Browser in VBA (view / object browser) or type "microsoft excel object model" into Help in the VBE .. this will give you all the objects you can then click on them to find out their properties & methods. Hope this helps Cheers JulieD . Hi Julie I haven't tried your last advice because I got a pinched nerve and couldn't sit down for a number of days. I could use my laptop by standing up at the kitchen counter which killed my back but I don't have Internet access in the kitchen. I won't be able to get back to the Excel project for several more days. I don't want to lose touch with you. Is there a way to move our correspondence forward or is there a quick way to get to what is now page 40 or so? And, is there a quick way to know when you respond to my questions? I do appreciate your help. Vince |
#16
|
|||
|
|||
Hi Vince
both of those should have worked fine ... try again ... if they still don't work try hartley at techie dot com Cheers JulieD --snip -- as i would like to limit the number of posts with my email address on them due to spam |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Images in a database | Franz | General Discussion | 10 | October 7th, 2004 09:35 AM |
What is MDE | Charlie | General Discussion | 4 | August 24th, 2004 04:15 PM |
limiting access to reports | JMorrell | General Discussion | 3 | July 28th, 2004 03:52 PM |
limiting access to reports | JMorrell | Setting Up & Running Reports | 0 | July 27th, 2004 05:41 PM |
Size of Excel file Access db exports to. | Tasha | General Discussion | 2 | June 5th, 2004 01:48 PM |