add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
| 1772 | Not really a strange question. In fact, xl2002 now has those options built into the worksheet protect dialog. The bad news is for the most part, you can't have the user change these things without unprotecting the worksheet. The one exception in your list is group/ungroup. If you protect the sheet in code like (I'll include the .enableautofilter, just for heck of it): ActiveSheet.EnableAutoFilter = True ActiveSheet.EnableOutlining = True ActiveSheet.Protect contents:=True, userInterfaceOnly:=True If you go with the .enable stuff, it has to be reset each time you open the workbook. So put it in your auto_open/workbook_open code. And by using the userinterfaceonly option, you could create macros that replicate the formatting capabilities. The macro will do the work. The user calls the macro (button/dropdown from a menu). Johnny Trubshaw wrote: Hello, I hide all of the formulas in my spreadsheets at work by password protecting, to stop other people overwriting the formulas by mistake, however I would still like to have all of the formatting and certain menu items restored. IE, Font colour, font size fill colour, column width, group and ungroup. Strange question I grant you, but there is a good reason for it. Thanking you in advance Cordially Yours Johnny -- Dave Peterson ec35720@msn.com |
| 1776 | -- --- HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "ChasC" <cic29@columbia.edu wrote in message news:13d8701c226d6$707ca930$9ee62ecf@tkmsftngxa05... I have been accumulating files on Excel 95. Purchasing a laptop with Windows XP as well as Office XP. Can I transfer these files directly and continue working on my spread sheets? If the transfer is complex can someone direct me to some explanatory literature. Thanks |
| 1779 | Hi Chas, You shouldn't have a problem moving your Excel files to Windows XP / Office XP if you weren't doing anything really sophisticated. You will want to obtain fresh addins. One problem area will be any macros you installed on your Tools menu. Your toolbar buttons and menus may have to have the macros reassigned. Your biggest problem will be drivers for your printer, scanner, an external CD/RW and other peripherals. If they are not in the HCL (Hardware Compatibility List). If they are not -- in other words they are not automatically detected and installed -- do not install your old drivers. Instead get new drivers from the Manufacturer or by doing a search at Google. i.e. drivers Canon xxxxxx XP where xxxxxx is a model number and XP is for windowsXP Go to Control Panel, add/delete software, make up a list of software to that you need to install on your new machine. Again for the free stuff go out the web and get fresh copies. Your purchased software unless it is windows and DOS compatible will probably not run on WindowsXP. At least that was my experience with CoralDraw, and Hijaak both heavy into graphics and not compatible with WinNT and those versions would be so obsolete now anyway. Take a look at my backup.htm page for files that you will want to export or copy favorites, cookies, *.wap, *.xlb, personal.xls, *.acl things like that that are probably stored in system directories as opposed to your own data directories. --- HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "ChasC" <cic29@columbia.edu wrote in message news:13d8701c226d6$707ca930$9ee62ecf@tkmsftngxa05... I have been accumulating files on Excel 95. Purchasing a laptop with Windows XP as well as Office XP. Can I transfer these files directly and continue working on my spread sheets? If the transfer is complex can someone direct me to some explanatory literature. Thanks |
| 1784 | Here's a VBA procedure that should do the job. Sub ForEach() Dim a As Variant, b As Variant, c As Variant Dim aa As Variant, bb As Variant, cc As Variant Dim Row As Long a = Array(1, 2, 3) b = Array(10, 20) c = Array(5, 50, 500) Row = 1 For Each aa In a For Each bb In b For Each cc In c Cells(Row, 1) = aa Cells(Row, 2) = bb Cells(Row, 3) = cc Row = Row + 1 Next cc Next bb Next aa End Sub It write the data to the active worksheet, beginning in row 1. The a, b, and c arrays can have any number of values. John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Bob Bobb Robertson" <MartinAJ@Copper.net wrote in message news:eC1dJItJCHA.2568@tkmsftngp09... Is there an Excel command, function, or macro that mimic's REFLEX's (Borland's numeric database product circa 1990) "For Each ... " Function. That is, given several, say four fields, the "For Each" function would generate records For each A=1,2,3 For each B=10,20 For each C=5,50,500 would result in 3x2x3=18 records as follows A B C 1 10 5 2 10 5 3 10 5 1 20 5 2 20 5 3 20 5 1 10 50 2 10 50 3 10 50 1 20 50 2 20 50 3 20 50 1 10 500 2 10 500 3 10 500 1 20 500 2 20 500 3 20 500 Then you could define a "D" field that is a function of A, B, and C -- FrankJakob@Yahoo.com |
| 1805 | Hi Jenn, (failed in attempt to include an email copy) Although set up to for labels the things to watch out for with Mail Merge would be the same so you may find /dmcritchie/excel/mailmerg.htm useful. Requests for help with Mail Merge even in the Excel groups are normally answered by people who answer MS Word questions in the Word newsgroups. You will find references to some of the Word MVP web sites in the related area at bottom of my page listed above. Doesn't do much good to ask for an email copy if you provide an incorrect email address. In any case the conversation continues in newsgroup where it belongs. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "SueC" <claussu@em.agr.ca wrote ... You could do a mail merge in Word, and use your XL file as the data source. In Word, check out Tools Mail Merge and then select your XL file as the data source. "jenn" <emailalias@company.com wrote ... What would you do? Situation: I have to send the same letter to over 500 people. I have all of their names and addresses in an excel document. Can I do something to make printing the individual names and addresses on the letters easy? Tell me what you think or if you know any cool tricks. Let me know at: jkennedyymcametrodetroit.org Thanks. jenn. . |
| 1807 | -- --- HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Tng Hau" <thau@gwbakeries.com wrote in message news:1456701c22776$9ab87800$37ef2ecf@TKMSFTNGXA13... I am the only one in my office so far to have the XP version of Excel. My problem is that any file I saved in XP will be upgraded to the XP. When other users use my files and try to save them, they would get a message saying the file was created using a later version of Excel, if you click yes you will lose the new information, I told the users to go ahead click yes. But the message keeps coming back no matter how many times they do that. Does any one how to get rid of that? I could not find anything in the Excel XP help or in the Knowledge Base to help me solve this problem. Excel XP does not have an option to save file as Excel 97 type. It only has a "Excel 97-2000 & 5.0/95 workbook" which doubles the size of the file. Thanks. |
| 1808 | LOL. That was the second one today David -- Regards, Peo Sjoblom "" <dmcritchie@msn.com wrote in message news:uLcQkt3JCHA.2340@tkmsftngp12... -- --- HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Tng Hau" <thau@gwbakeries.com wrote in message news:1456701c22776$9ab87800$37ef2ecf@TKMSFTNGXA13... I am the only one in my office so far to have the XP version of Excel. My problem is that any file I saved in XP will be upgraded to the XP. When other users use my files and try to save them, they would get a message saying the file was created using a later version of Excel, if you click yes you will lose the new information, I told the users to go ahead click yes. But the message keeps coming back no matter how many times they do that. Does any one how to get rid of that? I could not find anything in the Excel XP help or in the Knowledge Base to help me solve this problem. Excel XP does not have an option to save file as Excel 97 type. It only has a "Excel 97-2000 & 5.0/95 workbook" which doubles the size of the file. Thanks. |
| 1809 | Hi Johnny, If it is a stuck key it would be F8 and you would have been told when rebooting. Having the correct / latest mouse driver is important. More information on some things that have been successful for one or more people describing similar problems. Keep you busy for a while and amuse any one watching you, but you'll seldom have to actually reboot. No single solution works for everybody and no single solutions works for anyone all of the time. /dmcritchie/excel/ghosting.txt I haven't had the problem (if it is ONE problem) for quite some time now. But I have upgraded my system and my mouse drivers. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Tim C" <timc@lainc.net wrote in message news:768bb943.0207090951.40d5bcc6@posting.google.com... It sounds like a stuck key on your keyboard. Try banging on each of the Shift, Alt and Ctrl keys a few times. Tim C "Johnny" <Johnnyjohn75@hotmail.com wrote: Hi Very strange problem with Excel when I move my mouse/curser the cells in the worksheet automatically get selected. And off late this has started happening with all the files. Every time this problem crops up, I have to re-start my desktop. I am using MS-Office 2000 Tried uninstalling, repairing, uninstalling and then re- installing again nothing works Does any one know the fix for this |
| 1821 | Thanks. I thought about that. But I have a lot of macros and forms and range names that could become a litte messy. -----Original Message----- the only way I heve solved it to copy the sheets and modules to a new workbook in Excel 97 and save that one. Regards Ron "Tng Hau" <thau@gwbakeries.com schreef in bericht news:1456701c22776$9ab87800$37ef2ecf@TKMSFTNGXA13... I am the only one in my office so far to have the XP version of Excel. My problem is that any file I saved in XP will be upgraded to the XP. When other users use my files and try to save them, they would get a message saying the file was created using a later version of Excel, if you click yes you will lose the new information, I told the users to go ahead click yes. But the message keeps coming back no matter how many times they do that. Does any one how to get rid of that? I could not find anything in the Excel XP help or in the Knowledge Base to help me solve this problem. Excel XP does not have an option to save file as Excel 97 type. It only has a "Excel 97-2000 & 5.0/95 workbook" which doubles the size of the file. Thanks. . |
| 1824 | Do a save as and in the file type box - what is listed there? Microsoft excel workbook (.xls)? "Tng Hau" <thau@gwbakeries.com wrote in message news:1491901c22784$c6a69f10$2ae2c90a@hosting.microsoft.com... : Thanks. I thought about that. But I have a lot of macros : and forms and range names that could become a litte messy. : : -----Original Message----- : the only way I heve solved it to copy the sheets and : modules to a new : workbook in Excel 97 and save that one. : : Regards Ron : : "Tng Hau" <thau@gwbakeries.com schreef in bericht : news:1456701c22776$9ab87800$37ef2ecf@TKMSFTNGXA13... : I am the only one in my office so far to have the XP : version of Excel. My problem is that any file I saved : in : XP will be upgraded to the XP. When other users use my : files and try to save them, they would get a message : saying the file was created using a later version of : Excel, if you click yes you will lose the new : information, : I told the users to go ahead click yes. But the message : keeps coming back no matter how many times they do that. : Does any one how to get rid of that? I could not find : anything in the Excel XP help or in the Knowledge Base : to : help me solve this problem. Excel XP does not have an : option to save file as Excel 97 type. It only has : a "Excel 97-2000 & 5.0/95 workbook" which doubles the : size : of the file. Thanks. : : : . : |
| 1827 | no problem open the workbook and a new one rightclick on a sheetab and choose select all sheets select move or copy and choose in "to book" the new workbook. select copy!! also then OK now all sheets ar in the new workbook hit alt f11 in the VBA editor you can drag and drop the modules and userforms to the new workbook. only the code that is under thisworkbook you must copy in the thisworkbook of the new file. Regards Ron "Tng Hau" <thau@gwbakeries.com schreef in bericht news:1491901c22784$c6a69f10$2ae2c90a@hosting.microsoft.com... Thanks. I thought about that. But I have a lot of macros and forms and range names that could become a litte messy. -----Original Message----- the only way I heve solved it to copy the sheets and modules to a new workbook in Excel 97 and save that one. Regards Ron "Tng Hau" <thau@gwbakeries.com schreef in bericht news:1456701c22776$9ab87800$37ef2ecf@TKMSFTNGXA13... I am the only one in my office so far to have the XP version of Excel. My problem is that any file I saved in XP will be upgraded to the XP. When other users use my files and try to save them, they would get a message saying the file was created using a later version of Excel, if you click yes you will lose the new information, I told the users to go ahead click yes. But the message keeps coming back no matter how many times they do that. Does any one how to get rid of that? I could not find anything in the Excel XP help or in the Knowledge Base to help me solve this problem. Excel XP does not have an option to save file as Excel 97 type. It only has a "Excel 97-2000 & 5.0/95 workbook" which doubles the size of the file. Thanks. . |
| 1831 | Hi Peo, Demonic possession of Mouse button. Try to select something to look at and it sends out a reply. Try to drag spam to delete file and it opens the spam emailings. There were a lot more than two empty replies that tried to go out, but I don't let replies get sent immediately so caught most of them by immediately going off line and then deleting or reworking into a reply. Can't even simulate it happening offline. The mark of a true demon possession by making sure it is not predictable. Since I saw the empty reply was going to reply that the formats were same from Excel 97 and up. But then noticed that was already acknowledged in his advice to users to ignore the message, so tried to kill the reply. That reply I did manage to eliminate without it actually going out. What's really been troubling today is difficulty selecting the exact words for copying. Picking up extra characters like single quote to left, or words both left and right of what I wanted to select. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Peo Sjoblom" <terre08@mvps.org wrote in message news:eqaeHw3JCHA.2160@tkmsftngp10... LOL. That was the second [empty] one today David |
| 1838 | Hi Terry, I think the ability to cut or copy and paste from one office application to another has done away with the ability to export or save as Rich Text Format. But, I think, you can copy to the clipboard and paste to WordPad Start, Run WordPad and then change the extension to .rtf if not already with .rtf Let me know if that worked for your application as you've made me curious. I noticed it did not include the interior coloring of the cells when I just tested. The only reason I ever worked with .rtf was to create an RTF file from Word to be run through a converter to HTML before anything was included in Excel. Depending on your system I think they were named RTF2HTML or RTFTOHTML. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Terry Freedman" <terry@terryfreedman.com wrote in message news:3d2b42d6.4460293@auth.news.easynet.co.uk... Hi Can someone tell me how to get Excel to export a worksheet to RTF format please? Thanks Terry == Terry Freedman Technology Tips for Writers mailto:wkh-subscribe@topica.com |
| 1841 | Hi Lisa, Look at webdings. When in CharMap leave the font selected (navy blue) and use the arrow keys to go up or down quickly through the different fonts. Instructions to install CharMap in your Excel menu or toolbars in /dmcritchie/excel/toolbars.htm HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Lisa Kelly" <lisa.b.kelly@baesystems.com wrote in message news:1721701c2278d$ead27bd0$39ef2ecf@TKMSFTNGXA08... Can anyone tell me how to put the check mark symbol in a cell? I don't see it on a regular character map... THANKS!! Lisa |
| 1842 | Thanks, David I was thiking more in terms of doing it thru VBA. Any suggestions? Terry On Tue, 9 Jul 2002 17:21:10 -0400, "" <dmcritchie@msn.com wrote: Hi Terry, I think the ability to cut or copy and paste from one office application to another has done away with the ability to export or save as Rich Text Format. But, I think, you can copy to the clipboard and paste to WordPad Start, Run WordPad and then change the extension to .rtf if not already with .rtf Let me know if that worked for your application as you've made me curious. I noticed it did not include the interior coloring of the cells when I just tested. The only reason I ever worked with .rtf was to create an RTF file from Word to be run through a converter to HTML before anything was included in Excel. Depending on your system I think they were named RTF2HTML or RTFTOHTML. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Terry Freedman" <terry@terryfreedman.com wrote in message news:3d2b42d6.4460293@auth.news.easynet.co.uk... Hi Can someone tell me how to get Excel to export a worksheet to RTF format please? Thanks Terry == Terry Freedman Technology Tips for Writers mailto:wkh-subscribe@topica.com == Terry Freedman Technology Tips for Writers mailto:wkh-subscribe@topica.com |
| 1850 | Hi Joe, Please do not post the same question into multiple threads. I answered your question (actually also a second time by accident) and others answered as well. My previous reply can be found in /dmcritchie/excel/validation.htm#creditcard HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Gord Dibben" <gdibben@shaw.ca wrote in message news:a1rjiussf181tf1hs9o4lcbbtpi3a0gn1b@4ax.com... Joe Have a look at ToolsOptionsEdit and see if you have "Fixed Decimals" checked at -1. Uncheck the "Fixed Decimals". HTH Gord Dibben Excel MVP - XL97 SR2 On Mon, 8 Jul 2002 10:28:24 -0700, "Joe Newbie" <junkoffers1020@hotmail.com wrote: I am working with an excel spreadsheet. One of the fields is set to currency and when any information is entered, the last digit is always changed to a "0". Event if the number is 654321.00 the "1" is automatically changed into a "0". Any help/advice would be much appreciated. |
| 1854 | Hi Tng, Excel 97 through XP should be compatible, or should they, well maybe not, here is a little item (if 13MB is little) that I found looking for an XP viewer. Glad it's not my problem. Office XP Converter Pack (13 MB download) /downloads/2002/ConvPack.aspx The Office XP Converter Pack can be useful to users or organizations that use Microsoft Office XP in a mixed environment with previous versions of Office, including Office for the Macintosh or other Office-related productivity applications. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Tng Hau" <thau@gwbakeries.com wrote in message news:1456701c22776$9ab87800$37ef2ecf@TKMSFTNGXA13... I am the only one in my office so far to have the XP version of Excel. My problem is that any file I saved in XP will be upgraded to the XP. When other users use my files and try to save them, they would get a message saying the file was created using a later version of Excel, if you click yes you will lose the new information, I told the users to go ahead click yes. But the message keeps coming back no matter how many times they do that. Does any one how to get rid of that? I could not find anything in the Excel XP help or in the Knowledge Base to help me solve this problem. Excel XP does not have an option to save file as Excel 97 type. It only has a "Excel 97-2000 & 5.0/95 workbook" which doubles the size of the file. Thanks. |
| 1869 | Thanks everyone. This is what I ended up doing. I had to tweak the VBA codes because many of the form objects (combo boxes, list boxes etc. were created with different names). Everyting is fine now. But I think this is a bug (in Excel 97). As soon as the file is re-saved to 97 format, that annoying message should completely disappear. Also, I think Excel XP should have Excel 97 as one of the formats (not the 97-2000,5.0/95 combo format) listed in the save as type. -----Original Message----- no problem open the workbook and a new one rightclick on a sheetab and choose select all sheets select move or copy and choose in "to book" the new workbook. select copy!! also then OK now all sheets ar in the new workbook hit alt f11 in the VBA editor you can drag and drop the modules and userforms to the new workbook. only the code that is under thisworkbook you must copy in the thisworkbook of the new file. Regards Ron "Tng Hau" <thau@gwbakeries.com schreef in bericht news:1491901c22784$c6a69f10 $2ae2c90a@hosting.microsoft.com... Thanks. I thought about that. But I have a lot of macros and forms and range names that could become a litte messy. -----Original Message----- the only way I heve solved it to copy the sheets and modules to a new workbook in Excel 97 and save that one. Regards Ron "Tng Hau" <thau@gwbakeries.com schreef in bericht news:1456701c22776$9ab87800$37ef2ecf@TKMSFTNGXA13... I am the only one in my office so far to have the XP version of Excel. My problem is that any file I saved in XP will be upgraded to the XP. When other users use my files and try to save them, they would get a message saying the file was created using a later version of Excel, if you click yes you will lose the new information, I told the users to go ahead click yes. But the message keeps coming back no matter how many times they do that. Does any one how to get rid of that? I could not find anything in the Excel XP help or in the Knowledge Base to help me solve this problem. Excel XP does not have an option to save file as Excel 97 type. It only has a "Excel 97-2000 & 5.0/95 workbook" which doubles the size of the file. Thanks. . . |
| 1872 | Dana DeLouis <ng_only@hotmail.com wrote... ... (Side note: I didn't think Microsoft offers an Excel XP viewer yet...) ... Why should they if (1) the viewer can't run macros or recalc anything and (2) the XLS file format hasn't changed (supposedly) since 97? I doubt SmartTags would be live even if visible. |
| 1903 | "Kevin" <khunter2@ford.com wrote in message news:aggn6s$18f5@eccws12.dearborn.ford.com... I keep getting an error message " no more new fonts may be applied to this workbook " Q1) Can the number of fonts allowed be increased ? Q2) How many am I allowed ? Q3) Will Excel XP allow me more ? Any help would be appreciated. Kevin, I experienced similar problems, when I had approx 150 charts in one workbook and tried to modify their fonts. Not sure if your case is similar, but here's a post from Trevor Shuttleworth that helped me out. I vaguely recall that the workaround in my case was to disable the font size autoscale thingy. HTH Jouni "Trevor Shuttleworth" <Trevor@shucks.demon.co.uk wrote in message news:uQOKo40BCHA.864@tkmsftngp04... Jouni From TechNet ... PSS ID Number: Q215573 Article last modified on 10-26-2000 WINDOWS:2000 ====================================================================== -------------------------------------------------------------------------- -- --- The information in this article applies to: - Microsoft Excel 2000 -------------------------------------------------------------------------- -- --- SYMPTOMS ======== When you copy a worksheet with embedded chart objects several times, you may receive the following error message: No more new fonts may be applied in this workbook. When you click OK to clear the error message, you may receive the following error message: Cell table Integrity failure. CAUSE ===== This problem may occur if all of the following are true: - You created a chart object in the worksheet. -and- - The chart object contains a title. -and- - You pasted a chart object on the same worksheet ten or more times. -and- - You copied the worksheet several times in the same workbook. WORKAROUND ========== To workaround this behavior, use either of the following methods. Method 1: Disable Autoscaling in the Chart ------------------------------------------ 1. Select the chart. 2. On the Format menu, click Selected Chart Area. 3. Click the Font tab. 4. Click to clear the Autoscale check box. 5. Click OK. Method 2: Split the Number of Charts into Multiple Workbooks ------------------------------------------------------------ STATUS ====== Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. Additional query words: XL2000 ====================================================================== Keywords : kbdta Version : WINDOWS:2000 Issue type : kbbug Solution Type : kbpending ============================================================================ = Copyright Microsoft Corporation 2000. Regards Trevor "Jouni" <asd.asd@asd.asd wrote in message news:ot9J8.5703$ws6.121035@news2.nokia.com... Hi, my problem seems unambiguous and maybe there's no cure for it. Anyway, I've got five workbooks in total: four having charts on several worksheets, and one that is used to collate the charts. That is, I copy each of the charts one by one (using macros, of course) and paste them into the fourth workbook. Because the charts tend to change a bit when pasted, I have to format axes (font sizes & types, and date-format). The problem is that I end up having appr 150 charts on one sheet, and that's apparently too much for Excel2000 because I get a message "Unable to set the Size property of the Font class". If I try to change any fonts manually, I get a message saying: "No more new fonts may be applied in this workbook." Any ideas how to overcome this? I've tried to format the charts before pasting them as _pictures_, but it didn't help because the fonts are still messed up and then, having the charts as pictures, the fonts are definitely untouchable. It's a little irritating that I have to send out a report that has fonts of size 3.75... Any help appreciated. Cheers, Jouni |
| 1907 | Hope someone can help!! I have loads of spreadsheets, which when opened prompt the macro warning, where their are no macros present, I have followed the advice of users withing the group and opened up the code and deleted any empty modules within. This helped in a few cases but not in the majority. This happens not only with spreadsheets, I have created but with many other excel users withinh the company. Some spreadsheets affected are quite complex, but some are very basic. We as a company have bang up to date antivurus software installed on all machines, so I dont beleive their is any viruses hiding within excel spreadsheets. Any ideas anyone?? Thanks in advance Paul |
| 1929 | There are a lot of right click menus in excel. But I bet you mean the one you get when you right click on a cell inside a worksheet. If yes, then you could do something like this: Option Explicit Sub AddToCellMenu() Dim iCtr As Long Dim mymacros As Variant Dim myCaptions As Variant Dim cb As CommandBar Set cb = Application.CommandBars("Cell") mymacros = Array("mac1", "mac2", "mac3") myCaptions = Array("Cap 1", "Cap 2", "Cap 3") With cb.Controls For iCtr = LBound(mymacros) To UBound(mymacros) With .Add(Type:=msoControlButton, temporary:=True) '.BeginGroup = True .Caption = myCaptions(iCtr) .OnAction = ThisWorkbook.Name & "!" & mymacros(iCtr) '.FaceId = 103 End With Next iCtr End With End Sub in your workbook_open/auto_open code, you can just have a call addtocellmenu You'll have to create the macros that you want to run. If you put this workbook in you XLStart folder (location varies with versions of windows and excel), then this workbook will open each time you start excel. You'll have access to these rightclick options. Teresa wrote: I would like to be able to customize the short cut menu (Right Click Menu)in Excell like you can in Word. Is there a way to do this? -- Dave Peterson ec35720@msn.com |
| 1957 | I'm not sure how you're displaying the macro names now, but you could add a list of button captions on the worksheet. Then, add code to the spin button to extract a caption from the list. For example, if the list starts in K4: Private Sub SpinButton1_Change() CommandButton1.Caption = _ Range("K3").Offset(SpinButton1.Value, 0) End Sub In the command button code, use a select case to run the matching macro, e.g.: Private Sub CommandButton1_Click() Dim i As Integer i = SpinButton1.Value Select Case i Case 1 MymacroRow1 Case 2 MymacroRow2 End Select End Sub Matt wrote: Hello- In my workbook I have 5 modules and worksheets, and a total of 38 macros. The macros are cycled through with a spin button and the macro name apears on a command button. My problem is, I want to name the macros something thats more specific to their function like "Rows 1 - 24", "rows 1 - 58", ect.. but those are invalid names. Is there anyway to make the spin button display a different name then the macro name? TIA, Matt -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 2005 | Hi James, What Dave provided Denis with is a macro that shows several fields that have related information that is available on Windows NT. Denis would have to chose the field he wants and place it into his own macro for printing. I will repeat that macro here because your copy would have wrapped at end of lines. Running the macro will simply display the fields. Since this is for Windows NT and the later Windows 2000 and Windows XP, I don't know what you might see on Windows 9x. Sub PCInformation() Dim msg msg = "UserName" & vbTab & Environ$("username") _ & vbNewLine & "UserProfile" & vbTab _ & Environ("UserProfile") & vbNewLine _ & "Computer #" & vbTab _ & Environ$("ComputerName") & vbNewLine _ & "Logon Server" & vbTab _ & Environ$("Logonserver") & vbNewLine _ & "UserDomain " & vbTab & Environ$("UserDomain") MsgBox msg, , "Envrionment Variables" End Sub As a macro the user will have to run their own macro themselves or have it installed so that it runs automatically . Suggest taking a look at /dmcritchie/excel/getstarted.htm to get a feel for macros. Then take a look at /dmcritchie/excel/pathname.htm to see how you can use change a footer with coding. If users are on Windows NT systems then in the following would include both what they used in their Tools, Options, General, User Name which they can change, and their Windows NT username assigned by the administrator. Private Sub Workbook_BeforePrint(Cancel As Boolean) 'Install in ThisWorkbook in the Project Library of your workbook 'Alt+F11 (switch to VBE), Ctrl+R (object explorer), 'find/install in "ThisWorkbook" under "Microsoft Excel Objects" With ActiveSheet.PageSetup .LeftFooter = "&8" & LCase(ActiveWorkbook.FullName) _ & ActiveSheet.Name & Chr(10) & _ Application.UserName & " / " & Environ$("username") .CenterFooter = "Page &P of &N" .RightFooter = "&8 " & Format(Now(), "yyyy-mm-dd") & " &T" End With End Sub Until you are more familar with macros, suggest you install this in a new workbook and test with Print Preview. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "James Tigert" <james31k@excite.com wrote in message news:fa1c54b4.0207101638.2abb6f76@posting.google.com... Where do I put that code? I tried putting that into the footer directly, but it apparently doesn't go there? Trying to learn the in's and outs of VB. |
| 2009 | Hi ..., Tools, Options, Edit, Move cursor after entry If the box is not checked the cursor will not move, the normal direction is down. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "azrael" <azrael103@hotmail.com wrote ... I have a problem on my excel 2000 premium, the enter key does not function in the said program. When I press the enter key, it wont go down but in the other program, the enter key works very well. |
| 2011 | Hi Jan, Hope I'm not reducing your chances of getting a better answer by an administrator but the problem is probably due to Excel needing to create a file in the same directory that has filename of 8 random characters without an extension. This looks like a security violation to both administrative rules and to antivirus programs. You may have to turn off your AV program in order to isolate the problem. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Jan De Vylder" <jan.devylder@webit.be wrote ... We have a strict security policy. With a user who's rights are limited, we try to open a spreadsheet that contains a label or button out of the Control Toolbox, then excel hangs. An administrative user can open it without problems. It is possible that the problem has to do with limited user rights on disk as well as in the registry. Possibly some extra reference to DLLs or registry keys are needed to open this excel. However, we don't know which ones, and auditing doesn't help, since the system hangs before writing anything to the event log. Can anybody help? Thanks. Jan |
| 2020 | Simon, modify your pivot table so that it just has one field. Then sort it. Then add back the other fields. Bob Flanagan macro Systems -ins.com Productivity add-ins and downloadable books on VB macros for Excel "simon" <simon.wanderer@tnsofres.com wrote in message news:1553601c228d1$1b0259d0$a4e62ecf@tkmsftngxa06... I'm trying to sort data in a pivot table in order of one of the fields (date). I'm sellecting ascending in the autosort options section of the pivot table field advanced options box. But the data is staying as it was and not getting in order. |
| 2042 | No. Controls from the Forms toolbar have very few formatting options. John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Mike" <dmcsoco@mongol.net wrote in message news:1537c01c228ee$99ac0430$36ef2ecf@tkmsftngxa12... I'm using Excel 2000. When I insert a control button from the Forms Toolbar to run a macro in a spreadsheet is there any way that I can change the background color of the control button? Many thanks for help and suggestions, Mike. |
| 2057 | Are your cells formulas or text? If formulas, then never mind. You can't format pieces of them. If they're text, you can use this macro: /groups?threadm=slkdgufpjev105%40corp.supernews.com written by Pat Finegan. It's pretty neat. It actually formats each character. The smaller ones are 85% of the larger ones. If you're new to macros, you can read more about them here: /dmcritchie/excel/getstarted.htm Mary Wei wrote: Wouldn't changing the text size of Upper Case just give me smaller Upper Case? How do I convert it to Small Caps? Thanks! On Wed, 10 Jul 2002 18:01:46 -0400, "EarlK" <earlk@livenet.net wrote: Mary, Here are some possibilities. You can put =UPPER(A2) in a cell. This will give you the upper-case result in this other cell. For the small caps part, you could change the text size. You could hide the original row or column (unless folks need to be able to edit the data). Or you could copy the cell containing the formula, and paste-special--Values right over the original cell. Now you can get rid of the formula. Or a simple macro could change it to upper case either at a time of your choosing, or automatically as soon as the data has been entered. Regards from Virginia Beach, EarlK ------------------------------------------------------------- "Mary Wei" <marywei@pacbell.net wrote in message news:ci2piucg3ji5mhpl31kvr6r11vs0hf2om7@4ax.com... Is there a way to format text as Small Caps in Excel '97. It can be done in word '97, but Excel '97 seems to have its limitations. Thanks For Your Help! -- Dave Peterson ec35720@msn.com |
| 2073 | When you delete a macro, you must also delete the "Module" (even if it is empty/blank). Go to Tools macros Visual Basic Editor and look at the VBE's Project Explorer window to see if there are anymodules in the workbook. You can remove a module by right-clicking on it and choosing "Remove Module" from the shortcut menu. HTH Laura "Helene Lovenheim" <heleneglov@mindspring.com wrote in message news:B953BE13.E32B%heleneglov@mindspring.com... Hi, If I have a spreadsheet which has a macro which is no longer useful, is there a way to get rid of the macro permenantly (so that the macro warning no longer appears when you open the file). I tried to delete the macro and delete anything that looked related to it in the VB Editor, and while I can no longer run the macro, the warning still appears. Short of copying all the still useful data and formulas into a clean file, is there a way to do this? I have this problem in Excel 2000 if that makes any difference. Helene |
| 2093 | On Fri, 12 Jul 2002 08:54:17 +0100, "Paul Young" <PYoung@Chilton-Scotland.co.uk wrote: Does anyone know if their is a way to format a cell which uses the =now() calculation so that the spreedsheet does not ask a user, who has not made any changes whether they want to save changes? I have a shared spreadsheet viewed by many others who have read only access, however many are not very experienced with excel and at the moment they are ending up saving copies of the original spreadsheet all over the place! Any suggestions? You could add this to the code module of the workbook: Private Sub Workbook_BeforeClose(Cancel As Boolean) ThisWorkbook.Saved = True End Sub This "tricks" Excel into thinking that the workbook's "changes" have already been saved. The down side is that if your users have virus protection on they'll get the "Enable macros" warning every time they open it. (And you'll have to hope that they don't disable the macros.) You'll also need to be real careful when you're working on it yourself because you won't get any prompt to save changes before you close. Another option may be to set the users' recalc mode to manual BUT a lot of users (understandably) don't like workbook applications frigging with their environment settings. (And it won't work if the user presses [F9] while they have the book open anyway.) --------------------------------------------------------- Hank Scorpio apolloXVIII@ozemail.com.au Change XVIII to 18 for real address. |
| 2108 | Dave, You are, or should I say Myrna is right. But how did you remember yet alone relate that thread to my posting, not immediately obvious. Thanks for picking it up. Assigning anything but Shift to my keyboard shortcuts allows all add-ins to load normally. Also as I hinted before, the problem is indeed related to the order in which add-ins had previously been de & reselected. I can now recreate and solve the problem and allow Shift in shortcuts. For anyone else troubleshooting the "orders" are not in reverse, instead somewhat illogical in my case. I had recently installed ASAP-utilities (www.asap- utilities.nl) which has many Ctrl+Shift shortcuts to macros, duplicating all my *.xls file shortcuts. De & reselecting caused both this and XL original Analysis ToolPak to fail. Odd, as the latter contains no shortcuts. Thanks again. Regards, Sandy -----Original Message----- A couple of years ago, there was a discussion about why a macro would not fire when it was started by using a shortcut key (assigned to a macro in excel). This is part of what Myrna Larson wrote then. I'm not sure if it fits your case, but it sounds pretty close: ===== From Myrna: It has to do with the fact that the shortcut key uses SHIFT. Excel "remembers" that the shift key was used to start the macro and behaves as though it's still depressed when the 2nd file is opened. Opening a file with the shift key down suppresses the running of a macro, and evidently also aborts one that is already running. In my book, this is a bug. In my case, the shortcut key was CTRL+SHIFT+U. The only workaround that I found was to use CTRL+U instead, i.e. assign a shortcut key that doesn't use the SHIFT key. ===== You can read the whole thread at: /groups? threadm=u7ufks038r9jjaaa3jrt2c3cpilu7rshja%404ax.com Sandy V wrote: Sometimes two add-ins fail to load when opening Excel via keyboard shortcuts to xls files with error - "Cannot open Microsoft Excel 97 Add-in for editing. Please edit the source document instead" Pressing enter twice and XL & the file open normally, except for the two add-ins (although they remain checked in the addin list). Opens normally via shortcut icons or explorer to xls files, also first opening XL then the keyboard shortcut. If I deselect the two failing add-ins, everything works normally next time I open from a shortcut. Whether or not add-ins fail to load on keyboard shortcut opening, seems (not totally sure) to be related to the order in which the add-ins were previously de & reselected. But bizarre as to why the problem only occurs with keyboard shortcut and not icon shortcut opening etc. Hardly the most serious XL problem I've ever had but slightly irritating, so any ideas appreciated. Sandy PS "Jean" reported a similar unresolved issue in this newsgroup and I've followed all the checks suggested by Nick & Jimmy; ref Subject: "Excel 97 : cannot open add- in for editing", Date: 25 Jan 2002 -- Dave Peterson ec35720@msn.com . |
| 2130 | Hi Oscar, You might try using filtering as an alternative, which would allow you to select an individual based on value in a column and see only those items. Select column A (for instance), data, filter, auto filter User drop down arrow to select person. Filtered lists can be copied/pasted, printed as if that is the only data in the worksheet. If you want the same effect as you had with Conditional Formatting you might want to use an Event macro. Look for topic Change Color of Cell depending on first letter (A to G) within Worksheet Events and Workbook Events /dmcritchie/excel/event.htm You may have trouble distinguishing text colors on laptops, probably want to use interior color instead. Example above uses interior color. More information on color and selecting a colorindex number. /dmcritchie/excel/colors.htm HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "UFA" <cpjustica.ufa@mail.telapc.pt wrote in message news:agm3q8$2lh$1@venus.telepac.pt... Hi there, Maybe someone can give me a hand here... I am using the conditional formatting in excel but I would like to have more than 3 condition options. Lets say that I have 10 persons and when I insert the name of one of those persons in the sheet, the color of the text changes depending of the name. I can make it... but only with 3 options... Is there another way? Thank you so much Oscar |
| 2139 | After finally upgrading from Office 97 to 2000, the macros that were being used in an Excel spreadsheet to control cursor movement no longer would work. I am not experienced with writing macros but have followed the directions available to write a new macro and I am having no luck. Why wouldn't the macros work after the upgrade and how do I write a macro for cursor movement? |
| 2142 | Hi Greg, Check your security level you need Medium to run your macros. Tools, macro, security Followed the directions from what. Is your macro installed in a module such as module1 What does your macro look like. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Greg" <nelsonga@health.missouri.edu wrote in message news:17f1f01c229a8$80aed060$9be62ecf@tkmsftngxa03... After finally upgrading from Office 97 to 2000, the macros that were being used in an Excel spreadsheet to control cursor movement no longer would work. I am not experienced with writing macros but have followed the directions available to write a new macro and I am having no luck. Why wouldn't the macros work after the upgrade and how do I write a macro for cursor movement? |
| 2146 | Question does not make sense to me. By text file your probably meant HTML file as text does not have frames. If it is an HTML file can you point to it's location. What version of Excel are you using. Help, about If it is HTML what happens if you copy it while viewing and then paste into HTML. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm <msindt@treasurer.state.md/us wrote in message news:17d2401c229a5$58d14e90$19ef2ecf@tkmsftngxa01... we have a text file with tons of frames and titles in it. we want to make a clean excel file with only 3 col. of data. i have seem lots of cases when people want to convert one type file to another but no one seems to have a way to do it. i remeber in the 80s there were lots of menus to convert word and excel and wordperfect and lotus ect... but with so many file types now have programers given up on helping us convert files. delimiting and sorting seems so primative. |
| 2147 | If you have XL97 or later suggest dumping DATEDIF when working with geneology and use John Walkenbach's XDATEDIF Extended Date Functions Add-In, eliminating problems with negative dates involving subtraction in MS date system and incorrect leap years in older MS 1900 date system. (also dates prior to 1900). -walk.com/ss/excel/files/xdate.htm HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm |
| 2152 | Q120596 -- XL: Importing Text Files Larger Than 16384 Rows Breaks files longer than sheet limit into more sheets. (Also covers Microsoft Excel 97 and up which have a limit of 65,536 rows) /support/kb/articles/q120/5/96.asp HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm <maictab@aol.com wrote in message news:15aac01c229ad$6fc5cbf0$36ef2ecf@tkmsftngxa12... How do I export records from an Access Query that exceeds 65000 records to Excel if the limit on one sheet in Excel is 65000? Is there a way to put a check in place that staes if records 64000 move to next sheet? Or is there a parameter I can put on the query that says send Field "A" to sheet1 and Field "B" to sheet2? I attached a Access DB with a test query that exceeds 65000 records. |
| 2160 | Has anyone had any experience with performance problems moving from Excel 79 to 2002? My specific problem appears to be related to very large spreadsheets that contain macros. In Excel 97 the module runs in 3 minutes. In Excel 2002 the module takes 24 minutes. Others have complained that performance is poor even in spreadsheets not containing macros. Any help? |
| 2163 | What are the system specs how was in installed (Clean build?) Most importantly how much memory is in your machines? -----Original Message----- Has anyone had any experience with performance problems moving from Excel 79 to 2002? My specific problem appears to be related to very large spreadsheets that contain macros. In Excel 97 the module runs in 3 minutes. In Excel 2002 the module takes 24 minutes. Others have complained that performance is poor even in spreadsheets not containing macros. Any help? . |
| 2166 | The SetSourceData method applies to a Chart object, not a ChartObject object (which is the parent of a Chart object). Try this modification: Worksheets("Graphs").ChartObjects(1).Chart.SetSourceData _ Source:=Sheets("Graphs").Range("S7:AB7"), _ PlotBy:=xlRows John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "John Ortt" <john.ortt@NOSPAMPLEEZbaesystems.com wrote in message news:3d2efc4c$1@pull.gecm.com... I have written the following code...... Sub Area01_Click() Sheets("Graphs").Select ActiveSheet.Range("R1").Select ActiveCell.Formula = "=ImportDwellData!$B$1" Sheets("Graphs").Select Worksheets("Graphs").ChartObjects(1).SetSourceData Source:=Sheets("Graphs").Range("S7:AB7"), _ PlotBy:=xlRows End Sub The SetSourceData line is causing it to go wrong, if annyone can see my error I wouald greatly appreciate it. J. Ortt -- -------------------------------------------------------------------------- -- john.ortt@baesystems.com -------------------------------------------------------------------------- -- "There is no reason why anyone would want a computer in their home" Ken Olson, DEC, 1977 -------------------------------------------------------------------------- -- |
| 2173 | I understood the OP as wanting to add a button directly to the worksheet--not to a toolbar. I think both of you are correct but are answering different questions! So the question is now: Did the OP mean adding a forms button to a worksheet or to a commandbar? RagDyer wrote: I'm probably totally misunderstanding the question, but I *always* apply different colors to buttons I add to toolbars that fire macros. I add these buttons by right clicking in the toolbar and <Customize <Commands tab Scroll down in the "Categories" window and click on "Forms" Then, in the "Commands" window, click and drag the "button" icon to the toolbar. I then click on "Modify" and assign my macro, and then click on "EditButtonImage" and paint it, or the background, almost any color I wish. On some occasions, I change the button image itself. Is this not what the OP requested??? Regards, RD "John Walkenbach" <john@j-walk.com wrote in message news:O9kyY$OKCHA.2500@tkmsftngp10... No. Controls from the Forms toolbar have very few formatting options. John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Mike" <dmcsoco@mongol.net wrote in message news:1537c01c228ee$99ac0430$36ef2ecf@tkmsftngxa12... I'm using Excel 2000. When I insert a control button from the Forms Toolbar to run a macro in a spreadsheet is there any way that I can change the background color of the control button? Many thanks for help and suggestions, Mike. -- Dave Peterson ec35720@msn.com |
| 2184 | I think Chip Pearson describes his keyboard as looking like a hockey player's mouth (missing teeth). I guess he hates the CapsLock key a lot. That's right. None of my keyboards (except the laptop) has Caps Lock, F1, Num Lock, or Scroll Lock keys. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "Dave Peterson" <ec35720@msn.com wrote in message news:3D2F67CD.E7C85CD8@msn.com... I think Chip Pearson describes his keyboard as looking like a hockey player's mouth (missing teeth). I guess he hates the CapsLock key a lot. You may want to remove the key itself or take out the springy part underneath. (I just swear when I hit it by accident.) But to piggyback on John's answer: Private Sub Workbook_Open() Application.OnKey "{F1}", "useF2asF1" End Sub Sub useF2asF1() Application.SendKeys "{f2}" End Sub Sub resetF1() Application.OnKey "{F1}" End Sub If you hit enough by mistake, just make it another F2. John Walkenbach wrote: Yep, accidentally pressing F1 is high on my list of the most annoying things about Excel. You can use a macro to disable the F1 key. If you have a workbook that you open all the time, add the procedure to the code module for ThisWorkbook: Private Sub Workbook_Open() Application.OnKey "{F1}", "" End Sub Or, for a low tech solution, just jam a piece of cardboard in the F1 key slot to keep it from moving. John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Greg Boettcher" <gwboettcher@yahoo.com wrote in message news:15d4901c229f3$6613ca80$2ae2c90a@hosting.microsoft.com... This is frustrating. I can't figure out how to stop F1 from loading up Help. I thought that I could do this with Excel's customization features. This is important because I press the F2 key all the time and I hate it when I accidentally miss by one key and my circa-1996 computer has to strain to load up a totally unwanted help box. Help! Greg -- Dave Peterson ec35720@msn.com |
| 2196 | Following applies only if you need to fix existing entries and do not want to format as 000000 Before changing the format to Text to take care of new entries you might need to fix your existing entries to six digits. The resulting values will be text and left justified by default. After running the macro the selection (probably a column) will be formatted as text and future entries will be okay. Sub ConvertToText_6() Dim cell As Range Selection.NumberFormat = "@" For Each cell In Intersect(Selection, _ Selection.SpecialCells(xlConstants, xlNumbers)) cell.Value = Right("000000" & cell.Value, 6) Next cell End Sub Select your column, and run the macro Note use of specialcells in itself restricts selection to the used cell range so you can safely select columns without spending a long time processing a mostly empty column. Since this is not the programming group you may need more information on installing and using a macro /dmcritchie/excel/getstarted.htm HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Dave Peterson" <ec35720@msn.com wrote in message news:3D3029AF.E3E3ED25@msn.com... You can do you data entry with a leading single quote: '012345 But this makes excel treat the cell as text--no arithmetic using that cell later on. You can pre-format the cell as Text (Format|Cells|Number tab|Text) and type: 012345 Same thing with with the no arithmetic, though. If you want to use the number as text (for comparisons with other cells), then use one of the above. If you really want numbers, just type in 12345 and the format the cell (whole column???) by: Format|Cells|Number Tab|Custom and type: 000000 in the Type box. (the value is still a real number and arithmetic is ok.) "Tony P." wrote: I need to enter a number into a cell with a leading zero such as 012345. But the cell drops the leading zero and I end up with 12345 instead. Is there a way I can format the cell to keep the zero? Thanks, Tony -- For replies, please remove * from my email address. -- Dave Peterson ec35720@msn.com |
| 2197 | Hi Gav and Sandy, Thought Sandy's solution had interesting possibilities as a simple user controlled alternative to Highlight Changes Highlight, Change Highlighting /dmcritchie/excel/highlite.htm which I'm not fond of, probably mainly from inexperience, but I don't see others referring to Change Highlighting very often either. Sub auto_close() If ThisWorkbook.Saved = True Then Exit Sub Dim currentsheet As String, currentaddress As String currentsheet = ActiveSheet.Name currentaddress = ActiveCell.Address(1, 1) Application.EnableEvents = False Err.Clear On Error Resume Next Worksheets("SaveHistory").Activate If Err.Number < 0 Then With ActiveWorkbook .Worksheets.Add(after:= _ .Sheets(.Sheets.Count)).Name = "SaveHistory" [a1] = "LastSaved" [b1] = "Active when saved" [c1] = "active Cell" [d1] = "UserName" [A2].Activate End With Else Range("A1").End(xlDown).Offset(1, 0).Activate End If On Error GoTo 0 ActiveCell.Offset(0, 0) = Now ActiveCell.Offset(0, 1) = currentsheet ActiveCell.Offset(0, 2) = currentaddress ActiveCell.Offset(0, 3) = Application.UserName If ActiveCell.Row < 3 Then Cells.EntireColumn.AutoFit Sheets(currentsheet).Select Application.EnableEvents = True End Sub HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Sandy V" <savituk@yahoo.co.uk wrote in message news:1872b01c22a71$d7d13bc0$3bef2ecf@TKMSFTNGXA10... Following maintains a history of file saves in the current file. Use of the If... prevents the update and the save prompt if there have been no changes since opening, also does not update if saved without changes. Sub Auto_Close() If ThisWorkbook.Saved = False Then Sheets("SaveHistory").Select ' If A1 & A2 might be empty a bit more is needed here Range("A1").End(xlDown).Offset(1, 0).Range("A1") = Now ' Or, last save time only without history ' Range("C1") = Now End If End Sub I imagine it's possible to adapt this to your needs and save the "history" to another file, or even directly into your "main". Not sure exactly how as my VB is minimal, so feel free anyone else to comment. (I don't like the "Select", would prefer to save the previous selection). Sandy -----Original Message----- Hi I have a excel macro which opens other workbooks and copies and pastes a sheet from each into the main workbook ( ie the one with the macro ). Is there a way from within a macro to ask Windows or Excel the date on which the workbooks being opened were last saved, so that I can then display those dates in a worksheet ?, i.e. show on worksheet how up to date the sheets being copied from are. Thanks in advance for any help. Gav . |
| 2200 | Every time I open D:\My Documents\Budget\Budget 2002.xls I'm told it contains macros and I'm asked whether I want to enable or disable them. I understand this is the default behavior since I have security set to medium. Here's the problem: there aren't any macros in this document (or anywhere else) that I know of. When I click on Tools, macro, macros, the macro box shows there are no macros in this workbook, all open workbooks or in Budget 2002.xls. I've tried setting security to everything but low, which I do not want to do for obvious reasons, and I'm always forced to either choose to enable or disable macros or to click OK & acknowledge that there are unsigned macros present in this workbook. Is there anyway to figure out what's causing Excel to think there are macros in this document? And yes, I've scanned my entire system with the latest version of Norton Antivirus with the latest virus definitions. Nothing... Anthony W. Burner |
| 2201 | Never mind. I read a post about deleting modules left over from a previous macro just now. I'm not aware that I had any previous macros but there was one unnamed module there. I deleted it & now my problem is gone. Thanks anyway! :) "Anthony W. Burner" <tonyburner@charter.net wrote in message news:uj0skmjoak4183@corp.supernews.com... Every time I open D:\My Documents\Budget\Budget 2002.xls I'm told it contains macros and I'm asked whether I want to enable or disable them. I understand this is the default behavior since I have security set to medium. Here's the problem: there aren't any macros in this document (or anywhere else) that I know of. When I click on Tools, macro, macros, the macro box shows there are no macros in this workbook, all open workbooks or in Budget 2002.xls. I've tried setting security to everything but low, which I do not want to do for obvious reasons, and I'm always forced to either choose to enable or disable macros or to click OK & acknowledge that there are unsigned macros present in this workbook. Is there anyway to figure out what's causing Excel to think there are macros in this document? And yes, I've scanned my entire system with the latest version of Norton Antivirus with the latest virus definitions. Nothing... Anthony W. Burner |
| 2202 | Anthony Perhaps a macro was created at one time then deleted, leaving you with an empty Module. Excel treats an empty module as if it has macros even though you deleted any macros. Any lines of code in a sheet, no matter what they, are will trigger the warning. Go to VB Editor(ALT + F11) and look for Modules in the workbook project. Remove by right-click on moduleRemove moduleanswer NO to "do you want to export". Also look for any code in Sheets and ThisWorkBook. Select and clear any you find. HTH Gord Dibben Excel MVP - XL97 SR2 On Sat, 13 Jul 2002 13:33:57 -0500, "Anthony W. Burner" <tonyburner@charter.net wrote: Every time I open D:\My Documents\Budget\Budget 2002.xls I'm told it contains macros and I'm asked whether I want to enable or disable them. I understand this is the default behavior since I have security set to medium. Here's the problem: there aren't any macros in this document (or anywhere else) that I know of. When I click on Tools, macro, macros, the macro box shows there are no macros in this workbook, all open workbooks or in Budget 2002.xls. I've tried setting security to everything but low, which I do not want to do for obvious reasons, and I'm always forced to either choose to enable or disable macros or to click OK & acknowledge that there are unsigned macros present in this workbook. Is there anyway to figure out what's causing Excel to think there are macros in this document? And yes, I've scanned my entire system with the latest version of Norton Antivirus with the latest virus definitions. Nothing... Anthony W. Burner |
| 2206 | Thanks Cracked it with all your help Earliest Birthday =XDATE(XDATEYEAR(F2)-G2-1,XDATEMONTH(F2),XDATEDAY(F2)+1) Latest Birthday =XDATE(XDATEYEAR(F2)-G2,XDATEMONTH(F2),XDATEDAY(F2)) Cell F2 is the Date Cell G2 is the Age This can be performed for all known data and then select the Latest "Earliest" and the Earliest "Latest" Birthday for the range. "" <dmcritchie@msn.com wrote in message news:eHFvO#aKCHA.2984@tkmsftngp13... | | If you have XL97 or later suggest dumping DATEDIF | when working with geneology and use John Walkenbach's | XDATEDIF Extended Date Functions Add-In, eliminating | problems with negative dates involving subtraction in MS | date system and incorrect leap years in older MS 1900 | date system. (also dates prior to 1900). | -walk.com/ss/excel/files/xdate.htm | | HTH, | , Microsoft MVP - Excel [site changed Nov. 2001] | My Excel macros: /dmcritchie/excel/excel.htm | Search Page: /dmcritchie/excel/search.htm | | | |
| 2217 | Hi Gord: Any lines of code in a sheet, no matter what they, are will trigger the warning. Except for Option keywords in a worksheet, which don't trigger the macro warning. Also, I noticed that a UserForm with code doesn't trigger the warning either. Strange. (Not that a userform would pose a threat since it doesn't seem to be able to launch a macro on workbook_open). "Gord Dibben" <gdibben@shaw.ca wrote in message news:rat0juc9ui8j67db99373h1esl8dfdkbgq@4ax.com... Anthony Perhaps a macro was created at one time then deleted, leaving you with an empty Module. Excel treats an empty module as if it has macros even though you deleted any macros. Any lines of code in a sheet, no matter what they, are will trigger the warning. Go to VB Editor(ALT + F11) and look for Modules in the workbook project. Remove by right-click on moduleRemove moduleanswer NO to "do you want to export". Also look for any code in Sheets and ThisWorkBook. Select and clear any you find. HTH Gord Dibben Excel MVP - XL97 SR2 On Sat, 13 Jul 2002 13:33:57 -0500, "Anthony W. Burner" <tonyburner@charter.net wrote: Every time I open D:\My Documents\Budget\Budget 2002.xls I'm told it contains macros and I'm asked whether I want to enable or disable them. I understand this is the default behavior since I have security set to medium. Here's the problem: there aren't any macros in this document (or anywhere else) that I know of. When I click on Tools, macro, macros, the macro box shows there are no macros in this workbook, all open workbooks or in Budget 2002.xls. I've tried setting security to everything but low, which I do not want to do for obvious reasons, and I'm always forced to either choose to enable or disable macros or to click OK & acknowledge that there are unsigned macros present in this workbook. Is there anyway to figure out what's causing Excel to think there are macros in this document? And yes, I've scanned my entire system with the latest version of Norton Antivirus with the latest virus definitions. Nothing... Anthony W. Burner |
| 2225 | Hi Andonny, In ThisWorkBook: Private Sub Workbook_BeforePrint(Cancel As Boolean) You can find the above from within ThisWorkBook where you will see two dropdowns the lefthand one has (General) or Workbook choose Workbook the righthand dropdown includes BeforePrint. --for others seeking information-- More information on Event macros in /dmcritchie/excel/event.htm More information on pathname in footers in /dmcritchie/excel/pathname.htm Excel XP users can place full pathname into the footer without a macro. (In VBA this is entered as &Z) &[Path]&[File] inserts the path and file name. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Andonny" <wje@multiline.com.au wrote in message news:#2xtS3xKCHA.388@tkmsftngp09... Hi, I would like to achieve that whenever I print an excel sheet it has the path in the footer. I know about the code placed into ThisWorkbook like Sub UpdateFooter() ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName End Sub Is it possible to have something in personal.xls or somewhere that I don't have to write the code into every workbook. Thanks for your help Andonny |
| 2226 | Hi David I was about to post a caveat to my suggestion before seeing your professional v. my amateur version. Taken me a while to understand (most of) what's going on, learnt more than normal in one day! You've also added some bells and whistles I'd never thought of. Small point for others – best start without worksheet "SaveHistory" and let the code take care of it. The caveat, because of the first If.. (also in yours), is if the file is manually saved before close, then history is not necessarily updated. ie misses any changes prior to the manual save if no changes occur subsequently before Close-Save. Is viable to record somehow if the file has been in an unsaved state, particularly prior to any manual save (and reset on close)? Then on close, updating and prompting a save. How would one copy the latest update to a cell in another file, say "main", which might be what Gav wants? If the update is also in a cell in his copy-sheet, comparison of the two cells would show him if he has the latest version of copy-sheet in "main". Sandy -----Original Message----- Hi Gav and Sandy, Thought Sandy's solution had interesting possibilities as a simple user controlled alternative to Highlight Changes Highlight, Change Highlighting /dmcritchie/excel/highlite.htm which I'm not fond of, probably mainly from inexperience, but I don't see others referring to Change Highlighting very often either. Sub auto_close() If ThisWorkbook.Saved = True Then Exit Sub Dim currentsheet As String, currentaddress As String currentsheet = ActiveSheet.Name currentaddress = ActiveCell.Address(1, 1) Application.EnableEvents = False Err.Clear On Error Resume Next Worksheets("SaveHistory").Activate If Err.Number < 0 Then With ActiveWorkbook .Worksheets.Add(after:= _ .Sheets(.Sheets.Count)).Name = "SaveHistory" [a1] = "LastSaved" [b1] = "Active when saved" [c1] = "active Cell" [d1] = "UserName" [A2].Activate End With Else Range("A1").End(xlDown).Offset(1, 0).Activate End If On Error GoTo 0 ActiveCell.Offset(0, 0) = Now ActiveCell.Offset(0, 1) = currentsheet ActiveCell.Offset(0, 2) = currentaddress ActiveCell.Offset(0, 3) = Application.UserName If ActiveCell.Row < 3 Then Cells.EntireColumn.AutoFit Sheets(currentsheet).Select Application.EnableEvents = True End Sub HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Sandy V" <savituk@yahoo.co.uk wrote in message news:1872b01c22a71$d7d13bc0$3bef2ecf@TKMSFTNGXA10... Following maintains a history of file saves in the current file. Use of the If... prevents the update and the save prompt if there have been no changes since opening, also does not update if saved without changes. Sub Auto_Close() If ThisWorkbook.Saved = False Then Sheets("SaveHistory").Select ' If A1 & A2 might be empty a bit more is needed here Range("A1").End(xlDown).Offset(1, 0).Range("A1") = Now ' Or, last save time only without history ' Range("C1") = Now End If End Sub I imagine it's possible to adapt this to your needs and save the "history" to another file, or even directly into your "main". Not sure exactly how as my VB is minimal, so feel free anyone else to comment. (I don't like the "Select", would prefer to save the previous selection). Sandy -----Original Message----- Hi I have a excel macro which opens other workbooks and copies and pastes a sheet from each into the main workbook ( ie the one with the macro ). Is there a way from within a macro to ask Windows or Excel the date on which the workbooks being opened were last saved, so that I can then display those dates in a worksheet ?, i.e. show on worksheet how up to date the sheets being copied from are. Thanks in advance for any help. Gav . . |
| 2229 | Place code into ThisWorkBook and use Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Information in macro only relates to the workbook if you want to record cell changes within the workbook you have to use Highlight, Change Highlighting /dmcritchie/excel/highlite.htm As far as what you do within another file you would have to do similar for that file, can't see that you are going to pick up changes to other files otherwise. -- --- HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Sandy V" <savituk@yahoo.co.uk wrote in message news:161d001c22b2a$cea8b960$36ef2ecf@tkmsftngxa12... Hi David I was about to post a caveat to my suggestion before seeing your professional v. my amateur version. Taken me a while to understand (most of) what's going on, learnt more than normal in one day! You've also added some bells and whistles I'd never thought of. Small point for others – best start without worksheet "SaveHistory" and let the code take care of it. The caveat, because of the first If.. (also in yours), is if the file is manually saved before close, then history is not necessarily updated. ie misses any changes prior to the manual save if no changes occur subsequently before Close-Save. Is viable to record somehow if the file has been in an unsaved state, particularly prior to any manual save (and reset on close)? Then on close, updating and prompting a save. How would one copy the latest update to a cell in another file, say "main", which might be what Gav wants? If the update is also in a cell in his copy-sheet, comparison of the two cells would show him if he has the latest version of copy-sheet in "main". Sandy -----Original Message----- Hi Gav and Sandy, Thought Sandy's solution had interesting possibilities as a simple user controlled alternative to Highlight Changes Highlight, Change Highlighting /dmcritchie/excel/highlite.htm which I'm not fond of, probably mainly from inexperience, but I don't see others referring to Change Highlighting very often either. Sub auto_close() If ThisWorkbook.Saved = True Then Exit Sub Dim currentsheet As String, currentaddress As String currentsheet = ActiveSheet.Name currentaddress = ActiveCell.Address(1, 1) Application.EnableEvents = False Err.Clear On Error Resume Next Worksheets("SaveHistory").Activate If Err.Number < 0 Then With ActiveWorkbook .Worksheets.Add(after:= _ .Sheets(.Sheets.Count)).Name = "SaveHistory" [a1] = "LastSaved" [b1] = "Active when saved" [c1] = "active Cell" [d1] = "UserName" [A2].Activate End With Else Range("A1").End(xlDown).Offset(1, 0).Activate End If On Error GoTo 0 ActiveCell.Offset(0, 0) = Now ActiveCell.Offset(0, 1) = currentsheet ActiveCell.Offset(0, 2) = currentaddress ActiveCell.Offset(0, 3) = Application.UserName If ActiveCell.Row < 3 Then Cells.EntireColumn.AutoFit Sheets(currentsheet).Select Application.EnableEvents = True End Sub HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Sandy V" <savituk@yahoo.co.uk wrote in message news:1872b01c22a71$d7d13bc0$3bef2ecf@TKMSFTNGXA10... Following maintains a history of file saves in the current file. Use of the If... prevents the update and the save prompt if there have been no changes since opening, also does not update if saved without changes. Sub Auto_Close() If ThisWorkbook.Saved = False Then Sheets("SaveHistory").Select ' If A1 & A2 might be empty a bit more is needed here Range("A1").End(xlDown).Offset(1, 0).Range("A1") = Now ' Or, last save time only without history ' Range("C1") = Now End If End Sub I imagine it's possible to adapt this to your needs and save the "history" to another file, or even directly into your "main". Not sure exactly how as my VB is minimal, so feel free anyone else to comment. (I don't like the "Select", would prefer to save the previous selection). Sandy -----Original Message----- Hi I have a excel macro which opens other workbooks and copies and pastes a sheet from each into the main workbook ( ie the one with the macro ). Is there a way from within a macro to ask Windows or Excel the date on which the workbooks being opened were last saved, so that I can then display those dates in a worksheet ?, i.e. show on worksheet how up to date the sheets being copied from are. Thanks in advance for any help. Gav . . |
| 2231 | Got it, Nice. Sandy -----Original Message----- Place code into ThisWorkBook and use Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Information in macro only relates to the workbook if you want to record cell changes within the workbook you have to use Highlight, Change Highlighting /dmcritchie/excel/highlite.htm As far as what you do within another file you would have to do similar for that file, can't see that you are going to pick up changes to other files otherwise. -- --- HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm |
| 2234 | Hi Andonny, See these articles in the Microsoft Knowledge Base: Q153090 How To Pass a Visual Basic Array to an Excel Worksheet /default.aspx?scid=kb;en-us;Q153090 Q177991 XL: Limitations of Passing Arrays to Excel Using Automation /default.aspx?scid=kb;en-us;Q177991 Q146406 XL: How to Retrieve a Table from Access into Excel Using DAO /default.aspx?scid=kb;en-us;Q146406 Q247412 INFO: Methods for Transferring Data to Excel from Visual Basic /default.aspx?scid=kb;en-us;Q247412 Q75376 XL: Maximum Array Size in Microsoft Excel /default.aspx?scid=kb;en-us;Q75376 Q166342 XL97: Maximum Array Size in Microsoft Excel 97 /default.aspx?scid=kb;en-us;Q166342 Q153307 HOWTO: Call Microsoft Excel macros that Take Parameters /default.aspx?scid=kb;en-us;Q153307 Q163435 VBA: Programming Resources /default.aspx?scid=kb;en-us;Q75376 HTH --- Orlando Magalhães Filho (So that you get best and rapid solution and all may benefit from the discussion, please reply within the newsgroup, not in email) "Andonny" <wje@multiline.com.au escreveu na mensagem news:eYJ0IAwKCHA.1988@tkmsftngp08... Hi, I am looking for a site where I could learn about arrays when used in functions and also used in visual balsic routines. Specially when setting ranges. I am aware of the pitfalls when using arrays but I still would like to learn more about them. Thanks for your help Andonny |
| 2243 | Tim Except for Option keywords in a worksheet, which don't trigger the macro warning. Thanks for the clarification. I have always cleared the option lines as a matter of habit so assumed(there's that word again) they would trigger the macros warning if left in. Gord On Sat, 13 Jul 2002 23:38:35 -0700, "Tim Zych" <tzych@earthlink.net wrote: Hi Gord: Any lines of code in a sheet, no matter what they, are will trigger the warning. Except for Option keywords in a worksheet, which don't trigger the macro warning. Also, I noticed that a UserForm with code doesn't trigger the warning either. Strange. (Not that a userform would pose a threat since it doesn't seem to be able to launch a macro on workbook_open). "Gord Dibben" <gdibben@shaw.ca wrote in message news:rat0juc9ui8j67db99373h1esl8dfdkbgq@4ax.com... Anthony Perhaps a macro was created at one time then deleted, leaving you with an empty Module. Excel treats an empty module as if it has macros even though you deleted any macros. Any lines of code in a sheet, no matter what they, are will trigger the warning. Go to VB Editor(ALT + F11) and look for Modules in the workbook project. Remove by right-click on moduleRemove moduleanswer NO to "do you want to export". Also look for any code in Sheets and ThisWorkBook. Select and clear any you find. HTH Gord Dibben Excel MVP - XL97 SR2 On Sat, 13 Jul 2002 13:33:57 -0500, "Anthony W. Burner" <tonyburner@charter.net wrote: Every time I open D:\My Documents\Budget\Budget 2002.xls I'm told it contains macros and I'm asked whether I want to enable or disable them. I understand this is the default behavior since I have security set to medium. Here's the problem: there aren't any macros in this document (or anywhere else) that I know of. When I click on Tools, macro, macros, the macro box shows there are no macros in this workbook, all open workbooks or in Budget 2002.xls. I've tried setting security to everything but low, which I do not want to do for obvious reasons, and I'm always forced to either choose to enable or disable macros or to click OK & acknowledge that there are unsigned macros present in this workbook. Is there anyway to figure out what's causing Excel to think there are macros in this document? And yes, I've scanned my entire system with the latest version of Norton Antivirus with the latest virus definitions. Nothing... Anthony W. Burner |
| 2286 | Hi Smiler, You can use worksheet events to run macros. For example, the code below will run Mymacro when cell A1 is select and/or cell B2 < "". - Open or create a new workbook - Press Alt+F11 - Double click ThisWorkbook object - Insert the code below Private Sub Workbook_SheetCalculate(ByVal Sh As Object) If Range("B1").Value < "" Then Mymacro End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Target.Address = "$A$1" And Range("B1").Value < "" Then Mymacro End Sub Sub Mymacro() MsgBox "Hello" End Sub HTH --- Orlando Magalhães Filho (So that you get best and rapid solution and all may benefit from the discussion, please reply within the newsgroup, not in email) "Smiler" <stephen.a.evans@virgin.net escreveu na mensagem news:iSmY8.3561$7g5.154618@newsfep1-win.server.ntli.net... I have 2 queries: Firstly can I (and if so how) trigger a macro to run from a particular cell Secondly, only allow that macro to run if a particular cell <"" Your help would be greatly appreciated Steve |