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
| Article | Body |
| 12 | Put this code in the workbook where the lists will be created. It will add Sheets as it needs them. Select multiple files in the file open dialog It will prompt for the number of addresses per sheet. Sub GetData() Dim flist As Variant Dim i As Long, j As Long Dim rw As Long, shCnt As Long Dim FileNo As Long Dim sh As Worksheet Dim sName As String Dim sLine As String Dim NumAddresses As Variant Dim MailList() as String ChDrive "C" ChDir "C:\Data" flist = Application.GetOpenFilename( _ FileFilter:="Text Files (*.txt),*.txt", _ MultiSelect:=True) If TypeName(flist) = "Boolean" Then Exit Sub Do NumAddresses = InputBox("Enter the number of Addresses" _ & " per sheet") Loop Until IsNumeric(NumAddresses) And Len(Trim(NumAddresses)) 0 ReDim MailList(1 To NumAddresses, 1 To 1) Set sh = ThisWorkbook.Worksheets.Add( _ After:=ThisWorkbook.Worksheets( _ ThisWorkbook.Worksheets.Count)) sName = "Data" & 1 On Error Resume Next Application.DisplayAlerts = False Worksheets(sName).Delete Application.DisplayAlerts = True On Error GoTo 0 sh.Name = sName shCnt = 1 rw = 1 For i = LBound(flist) To UBound(flist) FileNo = FreeFile() Open flist(i) For Input As #FileNo j = 0 Do While Not EOF(FileNo) Line Input #FileNo, sLine j = j + 1 MailList(rw, 1) = Trim(sLine) rw = rw + 1 If rw NumAddresses Then sh.Cells(1, 1).Resize(NumAddresses, 1).Value _ = MailList ReDim MailList(1 To NumAddresses, 1 To 1) rw = 1 Set sh = ThisWorkbook.Worksheets.Add( _ After:=ThisWorkbook.Worksheets( _ ThisWorkbook.Worksheets.Count)) sName = "Data" & shCnt + 1 On Error Resume Next Application.DisplayAlerts = False Worksheets(sName).Delete Application.DisplayAlerts = True On Error GoTo 0 sh.Name = sName shCnt = shCnt + 1 End If Loop Debug.Print flist(i), j Next sh.Cells(1, 1).Resize(NumAddresses, 1).Value _ = MailList ReDim MailList(1 To NumAddresses, 1 To 1) End Sub Richard <richard@clarknet.com wrote in message news:d65101c214a2$d20278b0$37ef2ecf@TKMSFTNGXA13... Here's the deal: I have successfully created an Excel 2000 macro that imports a large amount of text into a worksheet. The file being imported is so large that I have to import the remainder into a second sheet. The content is email addresses only so only one column is used. I have many such files and need to import a lot of them. I would like help or the code to accomplish a simple task: 1. Create an automated means for importing the large text file specifying increments of 2500, 5000, 10000, and 20000 at a time with a new sheet being created based on the chosen number to extract. I want it to run until the end of the first file and automatically go to the next file. I'm not a VB programmer so I'm looking for either the scripting already done (a few modifications are ok) or very detailed and specific instructions on how to do it. Thanks. |
| 15 | I have an attendance form and a mileage travel form in excel 2000 format. Each requires a signature in the appropriate place. I've scanned my signature as a bitmap and when I paste it in the appropriate line I can't get rid of the background in that picture. This signature works fine in WORD but I can't find anything on excel. I've used the format picture command etc and can't loose the background to make it transparent so the signature line (actual line) will show through the background.. Any ideas or help would be appreciated. THanks |
| 35 | Hi Martin, Hope I have read you correctly. Going by your config described, try this set-up: a. Enter a formula in col AG to pick out the min % value for each row of your reference table first. For eg: in cell AG33, enter =MIN(AH33:AO33). Copy the formula down to AG57. b. Enter =VLOOKUP(M3,$AG$33:$AP$57,10,FALSE), where M3 contains the formula =MIN(AH33:AO57). The vlookup will now return the value of 18. Hth Max -----Original Message----- excel 2000 I am trying to find the corresponding value of a cell. I am using =MIN(AH33:AO57) to find the minimum %. Having found that value I want to then find the value in column AP. This value is a whole number. I have tried =VLOOKUP(M3, $AH$33:$AP$57, 9,0) but all I am getting is #N/A An example is:- -3% 15% -9% -14% 10% 2% -7% 5% 18 So if in the range AH33:AO57 the above row has the minimum % of -14% then vlookup would return the value of 18 Any help would be greatly appreciated. Ta, Martin . |
| 53 | I am new to VBA & VB. I wish to establish if it is possible to launch a .wav (and/or) on opening an Excel 2000 worksheet...(Note: not workbook,) I have perused newsgroups archives and web with Google and have been unable to locate any previous discussion on this. I would appreciate any direction to any tutorial or tips page that is relevant and could assist me. Many thanks Chris Hawkins |
| 63 | John Walkenbach has some example code for playing a .wav file: -walk.com/ss/excel/tips/tip59.htm -walk.com/ss/excel/tips/tip87.htm Should get you going into the right direction... HTH Jason Atlanta, GA -----Original Message----- I am new to VBA & VB. I wish to establish if it is possible to launch a .wav (and/or) on opening an Excel 2000 worksheet...(Note: not workbook,) I have perused newsgroups archives and web with Google and have been unable to locate any previous discussion on this. I would appreciate any direction to any tutorial or tips page that is relevant and could assist me. Many thanks Chris Hawkins . |
| 71 | Can anyone tell me how to disable or remove the easter egg in Excel 2000? I work in a local secondary school and it is being accessed by pupils which is disruptive to lessons!! Regards Shirl |
| 74 | Shirl, The easter egg is built into Excel's program code, isn't documented, and can't be disabled, AFAIK. If you are good with events, you could prevent the needed cells (X2000:L2000) from being selected, but that wouldn't stop a talented student from figuring out a way around it. The only sure way to prevent its use is to actually discipline the offender: set a policy (with penalties) and then stick to it, no matter what. Empty threats are worse than doing nothing. A reduced or failing grade on a report card (with documentation backing it up) would send a pretty firm message. HTH, Bernie shirl wrote: Can anyone tell me how to disable or remove the easter egg in Excel 2000? I work in a local secondary school and it is being accessed by pupils which is disruptive to lessons!! Regards Shirl |
| 88 | Note: If you have Excel 2000 or Excel 2002, EUR is one of the available currency symbols, so steps 5 and 6 are not required. Debra Dalgleish wrote: 1. Select the cells 2. Choose FormatCells 3. On the Number tab, choose Currency or Accounting 4. Select the number of decimal places, and negative number formatting that you prefer; select $ as the Symbol. 5. Choose the Custom Category 6. In the Type box, replace each $ with: "EUR " 7. Click OK Andy Walawender wrote: Does anyone known how to / whether it is possible to insert text into a column of already filled cells. ie column of cells with numeric values to which I want to add the string "EUR " for euro in front of the the monetary values without deleting or clearing the cells. (There's hundreds of them so doing each one manually is pretty tedious !!) Thanks for yr help Andy -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 131 | How can I attach a picture in to the header in excel 2000? |
| 138 | Whenever I try to open Excel, my pc freezes and I have to reboot. If I do a Ctrl/Alt/Delete during the freeze I get a white dialog box that tells me my "System is dangerously low in resources". I deleted the normal.dot file - not sure if that's only for Word but I knew it wouldn't hurt to try. That didn't work. I'm running Excel 2000 on Win 98 with 512 MB RAM. Any suggestions would be welcome. Thanks. Keith Knowles Full Circle, LLC -solutions.com keith@fullcircle-solutions.com phone: 860.206.2183 fax: 800.508.7581 |
| 141 | Thanks so much for posting. What would you do with the same problem. Can the tech reverse my data from his 2000 Excel back to the original V5. Is there a valid upgrade..... Excel 2000 / 2001? I need to be able to use existing files. Again, thanks for taking the time to help. Janet "Bay" -----Original Message----- Excel 5 ==== Excel 2002 is not a valid upgrade.. -- Regards, Peo Sjoblom "Bay" <jebn60@msn.com wrote in message news:dc8f01c21636 $2790eed0$a4e62ecf@tkmsftngxa06... I'm using Excel Version 5.0 on a new Dell Windows XP system. Original data files were recovered from an old PC, which has harddrive problems by a tech using Excel 2000 and now I can't retrieve/open them with V5.0. It was suggested that I upgrade, which I have tried to load Excel 2002 Upgrade and I received error 1608 which means, could not find any previously installed compliant products on the machine for installing this product. I let Windows automatically locate Excel V5 (in C:\Documents & Settings\Janet\Desktop - even in this location I could double click the icon on my desktop and it would open up and I could open a file. I even moved (dragged within Windows Explorer) the Excel V5 folder to the Program Files directory and still I was unsuccessful in loading Excel 2002 Upgrade - received the same error code of 1608. I also installed Lotus SmartSuite V4, (1995) from CD and Excel V5 (Disk 1-9, 3.5")and they were both placed in the same directory automatically. C:\Document & Settings\Janet\Desktop and neither program appears under the Control Panel, Add/Remove Programs. But what does appear is all software that came preloaded and 1 other 2002 that I installed from a CD that is XP compatible. Software preloaded was only Windows XP that includes MS Works, etc. and Dell programs included in the January 2002 special. I was going to uninstall V5 and start over, but it doesn't show up in the Control panel Add/Remove Prog. Any and all help is deeply appreciated, at 58 this is about to get to me. Thanks, Bay . |
| 150 | hello everyone, I've got a problem with Excel 2000 (I found the same prob. in 2002, in any case). Having a dimension (say, product hierarchy) with a "void" member (a member with no record in fact table), Excel "hides" the member, making it unvisble. The member itself is present and alive in the dimension treeview (i see it, and i can select/deselect it), but in any case Excel don't show up the member (ok...only null values for it...but I want to see it!)in the pivottable. In the Analysis Services shell, I can browse data viewing my member... Is there a "legal" way, workaround or trick to make the hidden member appearing again ? Thank you very much in advance Mauro |
| 166 | When I attempt to open Excel 2000, it first tries to open all of my Word templates then opens each Excel data file stored in "My Documents". I have uninstalled and re- installed but no help. Can anyone suggest how to stop this? |
| 172 | I have an Excel 2000,SR1(9.0.4402) workbook that will NOT recalculate properly on cell references between worksheets. Recalc set to Automatic. I can open the file on some machines and it works perfectly; not on others. Can find no settings that are different between the various machines. Saved the file from a machine on which it worked; then it worked on the first machine for awhile, but eventually reverted to not working. Any ideas? Sheila |
| 176 | Go to Tools/Options/General and clear "My Documents" from the Alternate Startup File Location. Anything in the startup or alternate startup folder will be opened on application launch. In article <e37001c216f7$0aa8e810$2ae2c90a@hosting.microsoft.com, Sally Broff <sally@displaytech-us.com wrote: When I attempt to open Excel 2000, it first tries to open all of my Word templates then opens each Excel data file stored in "My Documents". I have uninstalled and re- installed but no help. Can anyone suggest how to stop this? |
| 184 | Hi Sheila, Excel takes the calculation setting from the first workbook opened, rather than from each workbook. So this can happen if you open other workbooks which are set to manual, or have a workbook in Xlstart which is set to manual, or ... Charles Williams ______________________ Decision Models The Excel Calculation Site www.DecisionModels.com "Sheila" <sleppert@chguernsey.com wrote in message news:10d5301c216fd$78c35780$35ef2ecf@TKMSFTNGXA11... I have an Excel 2000,SR1(9.0.4402) workbook that will NOT recalculate properly on cell references between worksheets. Recalc set to Automatic. I can open the file on some machines and it works perfectly; not on others. Can find no settings that are different between the various machines. Saved the file from a machine on which it worked; then it worked on the first machine for awhile, but eventually reverted to not working. Any ideas? Sheila |
| 199 | I use xl2002 and had the same difficulty as . I typed in www.abc.com (and excel saw it as a hyperlink) I dragged down a few cells Selected that range and try to replace www.abc.com with www.microsoft.com. The value in the cell showed www.microsoft.com, but the hyperlink tip showed www.abc.com. And when I clicked on the link, I didn't go to the microsoft.com site. wrote: Hi Norman, Did not work for me. I have Excel 2000 is your different. B3: ctrl+h replace abc.com with mvps.org/dmcritchie/excel/colors.htm B3: /dmcritchie/excel/colors.htm with old link of previous testing wiped out link entirely that was created via a macro. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Norman Jones" <norman_jones@btconnect.com wrote ... Dear David, Using Edit\Replace to amend links certainly works for me . If , for example, I amend your website link and then change it back (e.g. edit\replace it with twit; edit\replace twit with it) I can then click on the link and be transported(profitably!) to your web pages. -- Dave Peterson ec35720@msn.com |
| 202 | Option Explicit Sub testme() CommandBars("Toolbar List").Enabled = False CommandBars("Tools").Controls("Customize...").Enabled = False End Sub The top one stops the right clicking on an empty spot on the toolbar area and choosing "customize..." from the bottom. The second one stops the Tools|Customize... (But you can still double click on an empty spot on the toolbar area and get the customize dialog.) Matthew Jackson wrote: I'm having trouble trying to figure out how to disable the "Customize..." Dialog Box in Excel 2000. If someone could send me a VBA code snippet that would be great. Thanks, Matt -- Dave Peterson ec35720@msn.com |
| 257 | In article <ddb301c21653$64edfc10$36ef2ecf@tkmsftngxa12, "Bay" <jebn60@msn.com wrote: This 1997 or greater would need to be an upgrade version - correct? The tech that recovered the files did say he couldn't guarantee me that if he retrieved and resaved, it might not work - 2 out of 2. I better go shopping. He needed to "save as" the file as a previous version (XL 5 for instance). Even in the current XP based version of xl we have, there is an option to do that. I suggest the tech hasn't finished the recovery job you asked them to do :) Go back and ask them to do it properly ... there is no reason to upgrade xl 5 if you only want to use xl 5 files ... always assuming you can still run xl 5 :) Bruce -----Original Message----- If I understand correctly, you have files that xl97 (or greater) based. But all you have on your new pc is xl95. Do you know someone with xl97 (or greater)? Maybe you could share a copy of your files with them. They could open each one and save it back down to the xl95 version. (Be careful though. If you used anything that was added in xl97, xl2000, it might not be "downgradeable".) I _think_ that xl97 is a valid starting point to upgrade to xl2002. Maybe you can find a (cheap) version on Ebay. Then you can load that version and then upgrade. Bay wrote: Thanks so much for posting. What would you do with the same problem. Can the tech reverse my data from his 2000 Excel back to the original V5. Is there a valid upgrade..... Excel 2000 / 2001? I need to be able to use existing files. Again, thanks for taking the time to help. Janet "Bay" -----Original Message----- Excel 5 ==== Excel 2002 is not a valid upgrade.. -- Regards, Peo Sjoblom "Bay" <jebn60@msn.com wrote in message news:dc8f01c21636 $2790eed0$a4e62ecf@tkmsftngxa06... I'm using Excel Version 5.0 on a new Dell Windows XP system. Original data files were recovered from an old PC, which has harddrive problems by a tech using Excel 2000 and now I can't retrieve/open them with V5.0. It was suggested that I upgrade, which I have tried to load Excel 2002 Upgrade and I received error 1608 which means, could not find any previously installed compliant products on the machine for installing this product. I let Windows automatically locate Excel V5 (in C:\Documents & Settings\Janet\Desktop - even in this location I could double click the icon on my desktop and it would open up and I could open a file. I even moved (dragged within Windows Explorer) the Excel V5 folder to the Program Files directory and still I was unsuccessful in loading Excel 2002 Upgrade - received the same error code of 1608. I also installed Lotus SmartSuite V4, (1995) from CD and Excel V5 (Disk 1-9, 3.5")and they were both placed in the same directory automatically. C:\Document & Settings\Janet\Desktop and neither program appears under the Control Panel, Add/Remove Programs. But what does appear is all software that came preloaded and 1 other 2002 that I installed from a CD that is XP compatible. Software preloaded was only Windows XP that includes MS Works, etc. and Dell programs included in the January 2002 special. I was going to uninstall V5 and start over, but it doesn't show up in the Control panel Add/Remove Prog. Any and all help is deeply appreciated, at 58 this is about to get to me. Thanks, Bay . -- Dave Peterson ec35720@msn.com . -------------------------------------------------------------------- Oook ! NOTE remove the not_ from the address to reply. NO SPAM ! |
| 286 | Dear David, I was totally wrong - I apologise! Unfortunately, my 'verification' test (amend the hyperlink using edit/replace and then reverse the amendment using edit/replace) was insufficiently rigorous. It appeared to work only because the hyperlink remained unchanged despite its textual mutation. Again, apologies. Regards, Norman. "" <dmcritchie@msn.com wrote in message news:enmwO9tFCHA.2636@tkmsftngp13... Hi Norman, Did not work for me. I have Excel 2000 is your different. B3: ctrl+h replace abc.com with mvps.org/dmcritchie/excel/colors.htm B3: /dmcritchie/excel/colors.htm with old link of previous testing wiped out link entirely that was created via a macro. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Norman Jones" <norman_jones@btconnect.com wrote ... Dear David, Using Edit\Replace to amend links certainly works for me . If , for example, I amend your website link and then change it back (e.g. edit\replace it with twit; edit\replace twit with it) I can then click on the link and be transported(profitably!) to your web pages. |
| 296 | Hi Marguerite, Administrative privileges. Excel needs to create a copy of the file to protect data if copy of file gets messed up and is not to be saved. The filename will be random looking characters and no file extension. Something that does not go over to well with network administrators or with antivirus programs. Anyway that is the problem. I'm not on a network so didn't try to answer deeper than that the MS KB has four articles that should help. Normally I would not include things directly out of the MS KB, but for those who don't know about the knowledge base, it, and Google Groups are our primary references. /dmcritchie/excel/xlnews.htm Searching the Knowledge Base /default.aspx?ln=EN-US&pr=kbinfo or if you can't stand the "improvements" above /oldmskb.htm Product: Excel for windows Type: All words Search: antivirus administrator Search Results Your search for antivirus administrator found 4 matches. 1 through 4 are listed below. {if you do your own search, you will have links to the following articles} 1) XL2000: "Your Changes Could Not Be Saved" Error While Saving to Network Drive (Q214032) Excerpt from this page:In Microsoft Excel, when you save a workbook file on a network drive, if you do not have both Modify and Delete rights for the directory to which you are saving the file, one of the following occurs:(size 9976 bytes, updated 12/3/2001 8:05:00 PM GMT) 2) XL2002: "Your Changes Could Not Be Saved" Error While Saving to a Network Drive (Q291070) Excerpt from this page:In Microsoft Excel, when you save a workbook file on a network drive, if you do not have both Modify and Delete rights for the directory to which you are saving the file, one of the following occurs:(size 10086 bytes, updated 12/3/2001 8:04:00 PM GMT) 3) XL: "Your Changes Could Not Be Saved" Error While Saving to Network Drive (Q113600) Excerpt from this page:In Microsoft Excel, when you save a workbook file on a network drive, if you do not have both Modify and Delete rights for the directory to which you are saving the file, one of the following occurs:(size 13286 bytes, updated 7/21/2001 3:14:00 AM GMT) 4) OFF2000: Error Message: "Internal Error 2351" or "Internal Error 2355" During Setup (Q236437) Excerpt from this page:When you run Setup for Microsoft Office 2000 or any Microsoft Office 2000 program, you may receive one or both of the following error messages:(size 25103 bytes, updated 1/9/2002 7:44:00 AM GMT) HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Marguerite Borck" <mborck@magliner.com wrote ... I have two users on a Windows 2000 Server LAN than cannot open Excel 2000 workbooks saved on a LAN drive in anything other than Read Only mode. As administrator or logged in as myself I can open and close the workbooks all day long. Anyone have any ideas for this mystery happening? thanks Margi |
| 321 | I needed to do the same thing and the best way (lowest overhead) is to use a simple txt file In my case QuoteNumber.txt. Simple open Notepad and save a blank file titled QuoteNumber in the c:\ root directory, each time Sub GetQuoteNumber is run the macro will do the rest. Have fun! . Sub GetQuoteNumber() Dim TedsNumber As Long Dim FileNum As Integer 'gets any available file number FileNum = FreeFile Open "c:\QuoteNumber.txt" For Random As #FileNum Len = Len(TedsNumber) 'gets the current value Get #FileNum, 1, TedsNumber 'increments by one TedsNumber = TedsNumber + 1 'places the new value to file Put #FileNum, 1, TedsNumber Close #FileNum 'places the new incremented number into a worksheet called Sheet2 in cell C8 Sheets("Sheet2").Range("C8").Value = TedsNumber End Sub "Wilson" <jwilson@wickes.com wrote in message news:eBrqY#5FCHA.1692@tkmsftngp05... Hi Ian, One way would be to store the invoice number in an unused cell with the font color the same as the background color of that cell, say N1 for example Then get the invoice number that is visible with =N1 In ThisWorkbook module, increment the number by one whenever the file is opened Private Sub Workbook_Open() Range("N1").Value = Range("N1").Value + 1 End Sub HTH "Ian Roberts" <Ian_Roberts@SPAMOFFBTinternet.com wrote in message news:aeq2su$dds$1@nntp-m01.news.aol.com... Hi I've made a custom invoice template using Excel 2000. It would be useful if there was a way to keep a record of the invoice number so that it could automatically be incremented each time a new invoice is created. I tried doing a search but couldn't find much. The few 3rd party invoice templates I've seen have the macros hidden so I don't know how to reproduce the function on my sheet. I would appreciate any assistance or links to relevant info. Many thanks. Ian I^) |
| 384 | I think that excel gets the format for dates from a Windows setting. It sounds like one pc is different than the other and not recognizing the date. I'm in the US and if I type 12/31/2001, I get a date (expected). But if I type 31/12/2001, I get the text 31/12/2001--not really a date. On the "bad" pc, if you format the cell as General (Format|Cells|Number Tab), do you get a number? (I get 37621 for 12/31/2001 (using the 1900 date system). If you don't get a number, then that cell really doesn't have a date in it (no matter what it looks like). And since the value isn't numeric (it's a string), the =text() has no affect on it. If this looks like the case, then close excel and try: Start|Settings|Control Panel|Regional Settings Applet|Date Tab. Check (change to) the format. Start Excel. I'm not sure if it'll react to the change or if you'll have to change something for it to notice. Hit F2 and then enter on one of the cells. Did it fix your problem? If yes, maybe you can convert a whole column of dates at once. Select your column and do a Data|Text to columns. Follow the wizard and make sure you choose the correct (mdy?) format on step 3 of the wizard. Good luck, James Goodchild wrote: Hi all We have a Windows 2000 server running terminal services and Citrix. Because of a requirement from a custom built program, MS Office 2000 was installed to the local hard drive of the Citrix server as well as to the Citrix neighborhood. Everything loads fine and other than one formatting issue, everything seems to work. Here is the problem, in Excel 2000 in the local copy if you type the following date 12/31/01 in cell A1 and then enter the formula =TEXT(A1,"Mmm. Dd yyyy")in cell B2, the result displays as 12/31/01. It should display as Dec. 01 2001. If we try the same formula from Excel 2000 under the Citrix neighborhood install on the same server, it works fine. Unfortunately, the custom application is a reporting tool that makes calls to Excel to generate graphs and charts for the report and requires the local version. Anyone got any ideas on how to address the format issue? Thanks in advance. -- Dave Peterson ec35720@msn.com |
| 466 | Hi Dave I guess I wasn't as clear as I could have been. The issue is on the smae computer - there are two instances of Excel on it, one loaded directly on the computer and the other loaded under Citrix on the same computer. I did check the international settings, but wasn't sure how this would effect one and not the other instance of Excel. Anyone else have any ideas? -----Original Message----- I think that excel gets the format for dates from a Windows setting. It sounds like one pc is different than the other and not recognizing the date. I'm in the US and if I type 12/31/2001, I get a date (expected). But if I type 31/12/2001, I get the text 31/12/2001--not really a date. On the "bad" pc, if you format the cell as General (Format|Cells|Number Tab), do you get a number? (I get 37621 for 12/31/2001 (using the 1900 date system). If you don't get a number, then that cell really doesn't have a date in it (no matter what it looks like). And since the value isn't numeric (it's a string), the =text() has no affect on it. If this looks like the case, then close excel and try: Start|Settings|Control Panel|Regional Settings Applet|Date Tab. Check (change to) the format. Start Excel. I'm not sure if it'll react to the change or if you'll have to change something for it to notice. Hit F2 and then enter on one of the cells. Did it fix your problem? If yes, maybe you can convert a whole column of dates at once. Select your column and do a Data|Text to columns. Follow the wizard and make sure you choose the correct (mdy?) format on step 3 of the wizard. Good luck, James Goodchild wrote: Hi all We have a Windows 2000 server running terminal services and Citrix. Because of a requirement from a custom built program, MS Office 2000 was installed to the local hard drive of the Citrix server as well as to the Citrix neighborhood. Everything loads fine and other than one formatting issue, everything seems to work. Here is the problem, in Excel 2000 in the local copy if you type the following date 12/31/01 in cell A1 and then enter the formula =TEXT(A1,"Mmm. Dd yyyy")in cell B2, the result displays as 12/31/01. It should display as Dec. 01 2001. If we try the same formula from Excel 2000 under the Citrix neighborhood install on the same server, it works fine. Unfortunately, the custom application is a reporting tool that makes calls to Excel to generate graphs and charts for the report and requires the local version. Anyone got any ideas on how to address the format issue? Thanks in advance. -- Dave Peterson ec35720@msn.com . |
| 504 | One more option. You could type your number, then hit alt-enter, then type your letter. Then edit your cell. Highlight the number portion and then Format|Cells| and make the font size smaller. If you like this idea (try it first), you can play with this to make your data entry easier. I assumed that you'll have x number of different type boxes. empty Number only (up to 2 digits) letter only (only 1) number and letter (Up to 2 digits and exactly one letter) So type in some test data n a few cells. 1A 13B X 7 A Then select that range and try the following macro: Option Explicit Sub testme() Dim myCell As Range Dim lenLeadingNumbers As Long For Each myCell In Selection.Cells lenLeadingNumbers = stripNumbers(myCell.Value) If Len(myCell.Value) - lenLeadingNumbers 2 Then 'something is wrong MsgBox "something is wrong with cell: " & myCell.Address Exit Sub End If Select Case Len(myCell.Value) - lenLeadingNumbers Case Is = 0 'either nothing in cell, or just numbers If Len(myCell.Value) = 0 Then 'do nothing Else myCell.Value = myCell.Value & Chr(10) End If Case Is = 1 'either it's got an alt-enter already or needs one If InStr(1, myCell.Value, Chr(10)) = 0 Then 'needs alt-enter myCell.Value = Left(myCell.Value, lenLeadingNumbers) _ & Chr(10) & Right(myCell.Value, 1) Else 'do nothing, it's just numbers then alt-enter End If Case Is = 2 If InStr(1, myCell.Value, Chr(10)) = 0 Then '2 non-numbers and one isn't alt-enter--error! MsgBox "something is wrong with cell: " & myCell.Address Exit Sub End If End Select 'now all entries are similar (numbers, altenter, letter) myCell.Characters(1, lenLeadingNumbers).Font.Size = 10 myCell.Characters(lenLeadingNumbers + 1, 1).Font.Size = 3 myCell.Characters(lenLeadingNumbers + 2, 1).Font.Size = 15 Next myCell End Sub Function stripNumbers(myString As String) As Long Dim iCtr As Long Dim myTempString As String myTempString = "" For iCtr = 1 To Len(myString) If Mid(myString, iCtr, 1) Like "[0-9]" Then myTempString = myTempString & Mid(myString, iCtr, 1) End If Next iCtr stripNumbers = Len(myTempString) End Function If you're new to macros, then you can read more about them at 's web site: /dmcritchie/excel/getstarted.htm (my first overkill of the weekend!) ======= You may even want to try a crossword puzzle maker. I went to google and search for crossword puzzles and I got lots of hits. I searched for "crossword puzzles shareware" w/o the quotes. You could try , too. Michael Redbourn wrote: Hi, I just started putting together a crossword and am using Excel 2000 Wondered if there's a way to put a number in the top left third of a cell (a square of the crossword) whilst writing another larger letter in the bottom two thirds of the same cell. What I want to do is to have some of the answers already filled in. Any help would be appreciated, thanks, Mike "The only reason for time is so that everything doesn't happen at once." Albert Einstein -- Dave Peterson ec35720@msn.com |
| 545 | I'm having a problem and I'm curious if others have it, too. When using the Excel 2000 "Save as HTML" function so that I can view and work with an excel function sheet within a web page. It opens fine in IE version 5.5 sp1. But for our workstations using IE version 5.5 sp2 and IE version 6, the excel does not load at all. Any suggestions? Tony |
| 552 | Hi again. I'm trying to automatize my graphs so they read from a cell the range in which they are supposed to get points. Say, I have X data in column B and Y data in colmun D. My spreadsheet locates the appropriate range and displays it as text in four cells (upper and lower x range, upper and lower y range), say H4 says "$B$10" H5 says "$B$15", I4 has "$D$10" and I5 "$D$15" . I want the ranges in the graph to "read" the text strings in H4, H5 as the range in X and I4,I5 as the range for Y. Help very much appreciated. Typing indirect in the graph range is invalid (Excel 2000). Thanks, John |
| 560 | And btw, i am using excel 2000 not 2002. "Peo Sjoblom" <terre08@mvps.org wrote in message news:uP8lSCjGCHA.2668@tkmsftngp13... Have you installed the add-inn? If not you have to download it at Microsoft's download site /downloads/2002/acclnk.aspx -- Regards, Peo Sjoblom "Fran" <fkwlau@hotmail.com wrote in message news:f71c01c21a2d$2ca66cb0$a5e62ecf@tkmsftngxa07... Hi, I am trying to convert an Excel list to an Access db. I went to the help in Excel and it said that all I need to do is click on the Data menu - click Convert to MS Access. The problem is I don't find this feature, despite numerous attemps to install Excel and add-ins. I made a very simple list, Type Price Dairy 45 Food 23 and clicked in one of the cells before I looked for that feature, but it still wouldn't give me anything in the data menu. Any suggestion is appreciated. Fran |
| 574 | Hi John Try using a Named range to refer indirectly to the cells storing the addresses (InsertNameDefine), should work. -- **Possible solution has NOT been tested** FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm "John Vera" <emperorj@hotmail.com wrote in message news:1130001c21a30$e59a6b10$9be62ecf@tkmsftngxa03... | | | Hi again. I'm trying to automatize my graphs so they read | from a cell the range in which they are supposed to get | points. Say, I have X data in column B and Y data in | colmun D. My spreadsheet locates the appropriate range and | displays it as text in four cells (upper and lower x | range, upper and lower y range), say H4 says "$B$10" H5 | says "$B$15", I4 has "$D$10" and I5 "$D$15" . I want the | ranges in the graph to "read" the text strings in H4, H5 | as the range in X and I4,I5 as the range for Y. Help very | much appreciated. Typing indirect in the graph range is | invalid (Excel 2000). | | Thanks, | John |
| 577 | Could you use a Worksheet_Change event instead? If the cell reference in one of the four cells is changed, the chart is updated. For example: Private Sub Worksheet_Change(ByVal Target As Range) Dim str1 As String Dim str2 As String Select Case Target.Address Case "$H$4", "$H$5", "$I$4", "$I$5" str1 = "Sheet1!" & Range("H4") & ":" & Range("H5") str2 = "Sheet1!" & Range("I4") & ":" & Range("I5") Worksheets("Sheet1").ChartObjects(1).Chart _ .SeriesCollection(1).Formula _ = "=SERIES(," & str1 & "," & str2 & ",1)" End Select End Sub John Vera wrote: Hi again. I'm trying to automatize my graphs so they read from a cell the range in which they are supposed to get points. Say, I have X data in column B and Y data in colmun D. My spreadsheet locates the appropriate range and displays it as text in four cells (upper and lower x range, upper and lower y range), say H4 says "$B$10" H5 says "$B$15", I4 has "$D$10" and I5 "$D$15" . I want the ranges in the graph to "read" the text strings in H4, H5 as the range in X and I4,I5 as the range for Y. Help very much appreciated. Typing indirect in the graph range is invalid (Excel 2000). Thanks, John -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 582 | Hi Dave - I just responded to an earlier post by John. The problem with charting from a named range, is that while it seems as though it should work, and while the same named range works in a worksheet, Excel's charting engine chokes on named ranges that use INDIRECT in their RefersTo formula. A few other functions also cause problems. I suggested a slightly different approach using OFFSET, which works fine with charts. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <3O9R8.3$TG5.14797@vicpull1.telstra.net, said... Hi John Try using a Named range to refer indirectly to the cells storing the addresses (InsertNameDefine), should work. -- **Possible solution has NOT been tested** FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm "John Vera" <emperorj@hotmail.com wrote in message news:1130001c21a30$e59a6b10$9be62ecf@tkmsftngxa03... | | | Hi again. I'm trying to automatize my graphs so they read | from a cell the range in which they are supposed to get | points. Say, I have X data in column B and Y data in | colmun D. My spreadsheet locates the appropriate range and | displays it as text in four cells (upper and lower x | range, upper and lower y range), say H4 says "$B$10" H5 | says "$B$15", I4 has "$D$10" and I5 "$D$15" . I want the | ranges in the graph to "read" the text strings in H4, H5 | as the range in X and I4,I5 as the range for Y. Help very | much appreciated. Typing indirect in the graph range is | invalid (Excel 2000). | | Thanks, | John |
| 583 | Thanks Jon, didn't know that. I assumed that as a chart can read a dynamic range, it would accept other named ranges that uses the INDIRECT. -- "Jon Peltier" <jonpeltier@yahoo.com wrote in message news:#U$mtjmGCHA.2452@tkmsftngp08... | Hi Dave - | | I just responded to an earlier post by John. The problem with charting from | a named range, is that while it seems as though it should work, and while the | same named range works in a worksheet, Excel's charting engine chokes on | named ranges that use INDIRECT in their RefersTo formula. A few other | functions also cause problems. | | I suggested a slightly different approach using OFFSET, which works fine with | charts. | | - Jon | ------- | Jon Peltier, Microsoft Excel MVP | /jonpeltier/Excel/index.html | _______ | | In article <3O9R8.3$TG5.14797@vicpull1.telstra.net, said... | | Hi John | | | Try using a Named range to refer indirectly to the cells storing the | addresses (InsertNameDefine), should work. | | | -- | **Possible solution has NOT been tested** | | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | | "John Vera" <emperorj@hotmail.com wrote in message | news:1130001c21a30$e59a6b10$9be62ecf@tkmsftngxa03... | | | | | | Hi again. I'm trying to automatize my graphs so they read | | from a cell the range in which they are supposed to get | | points. Say, I have X data in column B and Y data in | | colmun D. My spreadsheet locates the appropriate range and | | displays it as text in four cells (upper and lower x | | range, upper and lower y range), say H4 says "$B$10" H5 | | says "$B$15", I4 has "$D$10" and I5 "$D$15" . I want the | | ranges in the graph to "read" the text strings in H4, H5 | | as the range in X and I4,I5 as the range for Y. Help very | | much appreciated. Typing indirect in the graph range is | | invalid (Excel 2000). | | | | Thanks, | | John | | | |
| 600 | Hi Dave Thanks for this. But this won't distinguish (as one example of several) the changes that have been made to parameters to the Worksheet.Protect command since 97. I can access the details from the Excel 2000 help files on the MS site. I'm trying to find out if the text of the XL 97 help files is also available somewhere. Anne "" <DavidH@OzGrid.com wrote in message news:pEhR8.18$kP5.5272@vicpull1.telstra.net... Hi Anne Type: What's new in Microsoft Excel? into the help -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Anne Lambert" <AnneTLambert@hotmail.com wrote in message news:ODTBn7pGCHA.2448@tkmsftngp10... | Thanks for this. But I don't want to *use* XL 97: I just want to read the help files. I use XL 2002, and I can't remember all that's | changed over the years. | | Anne | | | "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:O07bGzpGCHA.2992@tkmsftngp13... | You must install the help files Anne. | If you install Office custom the vba helpwill not be installed I believe.?? | | So run your setup again and install it | | Regards Ron | | "Anne Lambert" <AnneTLambert@hotmail.com schreef in bericht | news:uvAYyupGCHA.1772@tkmsftngp09... | Are the Excel 97 Help files available on the Microsoft site? I can find | the 2000 Help text, but in spite of a lot of searching, I | can't find the Excel 97 help. I particularly need the VBA help | information. | | If they're available, I'd be grateful for a URL. | | Thanks. | | Anne | | | | | | | | |
| 604 | AFAIK, the Excel 97 Help files aren't online, but the VBA Programmer's Guide may help: /library/default.asp?url=/library/en-us/office97/html/web/fulltoc.asp Anne Lambert wrote: Hi Dave Thanks for this. But this won't distinguish (as one example of several) the changes that have been made to parameters to the Worksheet.Protect command since 97. I can access the details from the Excel 2000 help files on the MS site. I'm trying to find out if the text of the XL 97 help files is also available somewhere. Anne "" <DavidH@OzGrid.com wrote in message news:pEhR8.18$kP5.5272@vicpull1.telstra.net... Hi Anne Type: What's new in Microsoft Excel? into the help -- "Anne Lambert" <AnneTLambert@hotmail.com wrote in message news:ODTBn7pGCHA.2448@tkmsftngp10... | Thanks for this. But I don't want to *use* XL 97: I just want to read the help files. I use XL 2002, and I can't remember all that's | changed over the years. | | Anne | | | "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:O07bGzpGCHA.2992@tkmsftngp13... | You must install the help files Anne. | If you install Office custom the vba helpwill not be installed I believe.?? | | So run your setup again and install it | | Regards Ron | | "Anne Lambert" <AnneTLambert@hotmail.com schreef in bericht | news:uvAYyupGCHA.1772@tkmsftngp09... | Are the Excel 97 Help files available on the Microsoft site? I can find | the 2000 Help text, but in spite of a lot of searching, I | can't find the Excel 97 help. I particularly need the VBA help | information. | | If they're available, I'd be grateful for a URL. | | Thanks. | | Anne | | | | | | | | -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 605 | Hi Debra Yes, indeed. Thanks for this. Anne "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D15B24B.2040004@contextures.com... AFAIK, the Excel 97 Help files aren't online, but the VBA Programmer's Guide may help: /library/default.asp?url=/library/en-us/office97/html/web/fulltoc.asp Anne Lambert wrote: Hi Dave Thanks for this. But this won't distinguish (as one example of several) the changes that have been made to parameters to the Worksheet.Protect command since 97. I can access the details from the Excel 2000 help files on the MS site. I'm trying to find out if the text of the XL 97 help files is also available somewhere. Anne "" <DavidH@OzGrid.com wrote in message news:pEhR8.18$kP5.5272@vicpull1.telstra.net... Hi Anne Type: What's new in Microsoft Excel? into the help -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Anne Lambert" <AnneTLambert@hotmail.com wrote in message news:ODTBn7pGCHA.2448@tkmsftngp10... | Thanks for this. But I don't want to *use* XL 97: I just want to read the help files. I use XL 2002, and I can't remember all that's | changed over the years. | | Anne | | | "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:O07bGzpGCHA.2992@tkmsftngp13... | You must install the help files Anne. | If you install Office custom the vba helpwill not be installed I believe.?? | | So run your setup again and install it | | Regards Ron | | "Anne Lambert" <AnneTLambert@hotmail.com schreef in bericht | news:uvAYyupGCHA.1772@tkmsftngp09... | Are the Excel 97 Help files available on the Microsoft site? I can find | the 2000 Help text, but in spite of a lot of searching, I | can't find the Excel 97 help. I particularly need the VBA help | information. | | If they're available, I'd be grateful for a URL. | | Thanks. | | Anne | | | | | | | | -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 607 | Thank you for your comprehensive reply. "Peo Sjoblom" <terre08@mvps.org wrote in message news:e$g#P8rGCHA.612@tkmsftngp08... There are some new things that relates to 2002 only but on the whole everything you need for 2000 is there as well, I think that it could be named "Microsoft Excel 2000/2002: Power Programming with VBA, there is a 2000 version named "Microsoft Excel 2000: Power Programming with VBA", don't know if it is still in print but a quick web search at half.com turned up several new copies.. Since 2002 is the latest version I'd recommend that book in case you have to work with someone who has 2002... -- Regards, Peo Sjoblom "ikr" <ripsher[nospam]@btinternet.com wrote in message news:Oi5MweqGCHA.2568@tkmsftngp10... Thanks for this recommendation. Does it matter that John's book covers XL2002 (and I have XL2000)? In other words, is the VBA the same 'version' for each? "Peo Sjoblom" <terre08@mvps.org wrote in message news:e8g6CxhGCHA.2580@tkmsftngp09... Excel 2002 Power Programming With VBA by John Walkenbach is one (I have that one), he also wrote a dummies book although I would never buy a dummies book albeit being one, it's too insulting <g -- Regards, Peo Sjoblom "ikr" <ripsher[nospam]@btinternet.com wrote in message news:O$uAHWhGCHA.2684@tkmsftngp10... Can anyone recommend a good book for learning Excel (2k) VBA for absolute beginners? |
| 610 | Hi. thanks for your advice. Still, since I'm not an advanced Excel user, would this work when I rename my worksheet? What happens is, I'm trying to make a template that will then become various files with different data, and each file consists of 8 sheets with almost the same configuration. But each file and each sheet in each file will be named differently, will I also need to rename the sheets in the code you gave me? thanks, John -----Original Message----- Could you use a Worksheet_Change event instead? If the cell reference in one of the four cells is changed, the chart is updated. For example: Private Sub Worksheet_Change(ByVal Target As Range) Dim str1 As String Dim str2 As String Select Case Target.Address Case "$H$4", "$H$5", "$I$4", "$I$5" str1 = "Sheet1!" & Range("H4") & ":" & Range("H5") str2 = "Sheet1!" & Range("I4") & ":" & Range("I5") Worksheets("Sheet1").ChartObjects(1).Chart _ .SeriesCollection(1).Formula _ = "=SERIES(," & str1 & "," & str2 & ",1)" End Select End Sub John Vera wrote: Hi again. I'm trying to automatize my graphs so they read from a cell the range in which they are supposed to get points. Say, I have X data in column B and Y data in colmun D. My spreadsheet locates the appropriate range and displays it as text in four cells (upper and lower x range, upper and lower y range), say H4 says "$B$10" H5 says "$B$15", I4 has "$D$10" and I5 "$D$15" . I want the ranges in the graph to "read" the text strings in H4, H5 as the range in X and I4,I5 as the range for Y. Help very much appreciated. Typing indirect in the graph range is invalid (Excel 2000). Thanks, John -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html . |
| 611 | Hi. thanks for your advice. Still, since I'm not an advanced Excel user, would this work when I rename my worksheet? What happens is, I'm trying to make a template that will then become various files with different data, and each file consists of 8 sheets with almost the same configuration. But each file and each sheet in each file will be named differently, will I also need to rename the sheets in the code you gave me? thanks, John -----Original Message----- Could you use a Worksheet_Change event instead? If the cell reference in one of the four cells is changed, the chart is updated. For example: Private Sub Worksheet_Change(ByVal Target As Range) Dim str1 As String Dim str2 As String Select Case Target.Address Case "$H$4", "$H$5", "$I$4", "$I$5" str1 = "Sheet1!" & Range("H4") & ":" & Range("H5") str2 = "Sheet1!" & Range("I4") & ":" & Range("I5") Worksheets("Sheet1").ChartObjects(1).Chart _ .SeriesCollection(1).Formula _ = "=SERIES(," & str1 & "," & str2 & ",1)" End Select End Sub John Vera wrote: Hi again. I'm trying to automatize my graphs so they read from a cell the range in which they are supposed to get points. Say, I have X data in column B and Y data in colmun D. My spreadsheet locates the appropriate range and displays it as text in four cells (upper and lower x range, upper and lower y range), say H4 says "$B$10" H5 says "$B$15", I4 has "$D$10" and I5 "$D$15" . I want the ranges in the graph to "read" the text strings in H4, H5 as the range in X and I4,I5 as the range for Y. Help very much appreciated. Typing indirect in the graph range is invalid (Excel 2000). Thanks, John -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html . |
| 681 | I have beat my head against that wall so many times. The ranges work fine in the worksheet, and either don't work at all in the chart, or only show one point, or other weird things happen. I stick to the OFFSETs now, because I know they will work. What can be done, of course, is define the named range with INDIRECT, then array enter it into a worksheet range, and then refer to this range in the chart, either directly through the address, or through another dynamic range, if the number of cells changes. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <J9cR8.7$TG5.21637@vicpull1.telstra.net, said... Thanks Jon, didn't know that. I assumed that as a chart can read a dynamic range, it would accept other named ranges that uses the INDIRECT. -- "Jon Peltier" <jonpeltier@yahoo.com wrote in message news:#U$mtjmGCHA.2452@tkmsftngp08... | Hi Dave - | | I just responded to an earlier post by John. The problem with charting from | a named range, is that while it seems as though it should work, and while the | same named range works in a worksheet, Excel's charting engine chokes on | named ranges that use INDIRECT in their RefersTo formula. A few other | functions also cause problems. | | I suggested a slightly different approach using OFFSET, which works fine with | charts. | | - Jon | ------- | Jon Peltier, Microsoft Excel MVP | /jonpeltier/Excel/index.html | _______ | | In article <3O9R8.3$TG5.14797@vicpull1.telstra.net, said... | | Hi John | | | Try using a Named range to refer indirectly to the cells storing the | addresses (InsertNameDefine), should work. | | | -- | **Possible solution has NOT been tested** | | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | | "John Vera" <emperorj@hotmail.com wrote in message | news:1130001c21a30$e59a6b10$9be62ecf@tkmsftngxa03... | | | | | | Hi again. I'm trying to automatize my graphs so they read | | from a cell the range in which they are supposed to get | | points. Say, I have X data in column B and Y data in | | colmun D. My spreadsheet locates the appropriate range and | | displays it as text in four cells (upper and lower x | | range, upper and lower y range), say H4 says "$B$10" H5 | | says "$B$15", I4 has "$D$10" and I5 "$D$15" . I want the | | ranges in the graph to "read" the text strings in H4, H5 | | as the range in X and I4,I5 as the range for Y. Help very | | much appreciated. Typing indirect in the graph range is | | invalid (Excel 2000). | | | | Thanks, | | John | | | |
| 722 | Hi All, I want to know the difference between Excel 2000 Premium & Excel 2000 Professional edition. Any help on this will be appreciated. Thanks, Rajesh |
| 723 | Hi Rajesh As far as I know nothing and no such thing. Standard, Professional, Premium, Developer are labels on the Office package, containing more and more stuff like Access, FrontPage, Photo Editor, Developer tools, ... HTH. Best wishes Harald "Rajesh Chandukutty" <rajesh.chandukutty@oracle.com skrev i melding news:3D185647.74A77D49@oracle.com... Hi All, I want to know the difference between Excel 2000 Premium & Excel 2000 Professional edition. Any help on this will be appreciated. Thanks, Rajesh |
| 754 | Hi there. Windows 2000, Service Pack 2 Excel 2000, Service Pack 1 When the user logs on to the domain, he launches his spreadsheet directly from the server folder, it takes several minutes to load. When I log on as Administrator on the same domain, the workbook opens straight away. This also happens when I log on locally as Administrator or local user on the server. Any idea why so slow? Thanks, Neal Blackie. |
| 777 | I am tring to get excel 2000 to open each excel file in a new taskbar button. I have gone into TOOL, OPTIONS, VIEW and checked the box WINDOWS IN TASKBAR but the option is not working. Do you know of other options that i may need checked to get this function to work. Thanks for the help |
| 827 | Hi All, One more problem that I would really appreciate your help with. I do a lot of importing into a particular Excel 2000 database from all kinds of sources.I extract names, addresses, email, you name it. Included with the imports in some cells are leading spaces. They completely ruin sorts because all data sorted in these columns alphabetically, sorts a-z without the leading spaces, then continues sorting from a-z witht the leading spaces!!! How do I get rid of these leading spaces!!!! I tried using indent, left align, replace, nothing works. Do I have to go through all the cells with these leading spaces and delete them manually????!!!!!!!!!!!!!!!!! Thanks for your quick and concise response from my first question. You guys are great!!!! |
| 840 | Bill, Two possibilities: 1) Create a "repeat" column of everything (you can then hide the original or something) containing the following (assuming B is repeat column, A is original): =IF(left(a1,1)=" ",right(a1,len(a1-1)),a1) 2) Create a Macro to sift through the data and look for leading spaces. You can either attach this to a button so that it runs whenever you click the button, or set it as an event procedure to run whenever the spreadsheet is updated. Something like: sub deletespaces() for each cell in worksheet data=cell.value if left(data,1)=" " then data=right(data,len(data-1)) cell.value=data end if next cell end sub Hope this helps, Steve -----Original Message----- Hi All, One more problem that I would really appreciate your help with. I do a lot of importing into a particular Excel 2000 database from all kinds of sources.I extract names, addresses, email, you name it. Included with the imports in some cells are leading spaces. They completely ruin sorts because all data sorted in these columns alphabetically, sorts a-z without the leading spaces, then continues sorting from a-z witht the leading spaces!!! How do I get rid of these leading spaces!!!! I tried using indent, left align, replace, nothing works. Do I have to go through all the cells with these leading spaces and delete them manually????!!!!!!!!!!!!!!!!! Thanks for your quick and concise response from my first question. You guys are great!!!! . |
| 843 | Try the "Trim" function. With text in A1 =TRIM(A1) And copy/drag down as needed. HTH RD "Bill" <bill@thetrainingassociates.com wrote in message news:12a0001c21d27$2af40380$19ef2ecf@tkmsftngxa01... Hi All, One more problem that I would really appreciate your help with. I do a lot of importing into a particular Excel 2000 database from all kinds of sources.I extract names, addresses, email, you name it. Included with the imports in some cells are leading spaces. They completely ruin sorts because all data sorted in these columns alphabetically, sorts a-z without the leading spaces, then continues sorting from a-z witht the leading spaces!!! How do I get rid of these leading spaces!!!! I tried using indent, left align, replace, nothing works. Do I have to go through all the cells with these leading spaces and delete them manually????!!!!!!!!!!!!!!!!! Thanks for your quick and concise response from my first question. You guys are great!!!! |
| 851 | Hi Roger, What does =TRIM(A1:A100) do ? Regards, RD "Roger Govier" <roger@technology4u.co.uk wrote in message news:kspjhu0vk04bfhj956knu0l9usq83on84q@4ax.com... Hi Bill Take look at the TRIM() function =TRIM(A1:A100) will remove the excess leading and trailing spaces for you. -- Roger Govier Technology 4 U W98SR2 XL2K On Wed, 26 Jun 2002 08:35:58 -0700, "Bill" <bill@thetrainingassociates.com wrote: Hi All, One more problem that I would really appreciate your help with. I do a lot of importing into a particular Excel 2000 database from all kinds of sources.I extract names, addresses, email, you name it. Included with the imports in some cells are leading spaces. They completely ruin sorts because all data sorted in these columns alphabetically, sorts a-z without the leading spaces, then continues sorting from a-z witht the leading spaces!!! How do I get rid of these leading spaces!!!! I tried using indent, left align, replace, nothing works. Do I have to go through all the cells with these leading spaces and delete them manually????!!!!!!!!!!!!!!!!! Thanks for your quick and concise response from my first question. You guys are great!!!! |
| 866 | The data/text function worked the best. Thank you, everyone for your help!!! This newsgroup is great and I will check it out more often, for tips, as well as lending a helping hand too if I can. All for now.... Bill -----Original Message----- Try the "Trim" function. With text in A1 =TRIM(A1) And copy/drag down as needed. HTH RD "Bill" <bill@thetrainingassociates.com wrote in message news:12a0001c21d27$2af40380$19ef2ecf@tkmsftngxa01... Hi All, One more problem that I would really appreciate your help with. I do a lot of importing into a particular Excel 2000 database from all kinds of sources.I extract names, addresses, email, you name it. Included with the imports in some cells are leading spaces. They completely ruin sorts because all data sorted in these columns alphabetically, sorts a-z without the leading spaces, then continues sorting from a-z witht the leading spaces!!! How do I get rid of these leading spaces!!!! I tried using indent, left align, replace, nothing works. Do I have to go through all the cells with these leading spaces and delete them manually????!!!!!!!!!!!!!!!!! Thanks for your quick and concise response from my first question. You guys are great!!!! . |
| 1920 | Hi, Your problem is not related to XP or 2000. Your errors are due to the File Type Association. The My.xls error is due to that your shortcut tries to open a file called My.xls because there is a space between C:\My Doc... The second error is due to the same in that your file is located under a directory structure that will also contain spaces. Therefore its the File Type Association that needs to be updated. You can test it by putting an Excel file under the C:\Temp directory and run the shortcut from there. Best regards KM -----Original Message----- hello have Excel 2000 on my machine, and installed Excel XP recently aswell - I use both as I have to train them. anyway, I prefer to use 2000 as my default, but when I set it to do so, and then open a workbook via a desktop shortcut, it gives me 2 error messages: 1) it can't find D:\My.xls But I don't have, nor ever created such a file on my machine 2) it can't find D:\my documents\........\Budget.xls which is for the workbook I'm trying to open, BUT then it still opens it? Any idea why this is happening? If I set XP to be the default app, no problems. TIA Fuddam --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (). Version: 6.0.373 / Virus Database: 208 - Release Date: 01/07/2002 . |
| 1922 | I am currently using Excel 2000. A coworker of mine that prefers the R1C1 reference style used my computer for a short amount of time yesterday and changed the reference style to their preferance while using it. Now whenever I open Excel I have to manually go in to Tools Options General and uncheck the R1C1 box. But if I close Excel and then start it back up, the box is checked again. Is there a way to change Excel's start-up properties or a solution that may help me? TIA -- Laura Though it can be tedious, I may take the long road, for it is the one that I know. But if someone has the map for the short road, I'd sure like to take a look! |
| 1935 | This is one of those settings that excel picks up from the first workbook that it opens. If that file had R1C1 style set, and if you open it first (like double clicking in windows explorer), then you'll get R1C1 style. If you change back to A1 and save that file, then you'll be set (until you work on a file you share with your coworker and he/she changed styles and you open that file first). You may be in a battle of a lifetime with your coworker--or you could just make sure you open (or create a new workbook) with A1 reference style before you open the "battle" workbook. That's what Jason's post was trying to do. I use personal.xla (hidden) and the reference style had no affect on my settings. (Maybe another option would be to make book.xlt in your xlstart and make sure that this workbook has A1 style set. Then open excel and use File|open to open that other pesky workbook.) FYI: Excel also gets it calculation mode setting this way (first workbook opened in a session). Here's hoping your coworker doesn't start messing with that! Laura Wilde wrote: I am currently using Excel 2000. A coworker of mine that prefers the R1C1 reference style used my computer for a short amount of time yesterday and changed the reference style to their preferance while using it. Now whenever I open Excel I have to manually go in to Tools Options General and uncheck the R1C1 box. But if I close Excel and then start it back up, the box is checked again. Is there a way to change Excel's start-up properties or a solution that may help me? TIA -- Laura Though it can be tedious, I may take the long road, for it is the one that I know. But if someone has the map for the short road, I'd sure like to take a look! -- Dave Peterson ec35720@msn.com |
| 1949 | Fuddam Try first: StartRun "Excel.exe /regserver"(no quotes)OK If no joy: go into File Types and place double quotes(") at each end of your path to Excel. e.g. "C:\program files\msoffice\office\excel.exe" HTH Gord Dibben Excel MVP - XL97 SR2 On Wed, 10 Jul 2002 19:50:09 +0100, "Fuddam" <fuddam@NOSPAMntlworld.com wrote: thanks for the info, but not sure how to do that - can open Tools/ Folder Options / File Types, but other than associating XLS with the Excel 2000 app, can't see anything else than can be changed. Has worked in the past, so why the problem now? am using Win XP Pro, btw thanks Fuddam "Kevin McCartney" <kevin@moonsys.de wrote in message news:14c2001c2280e$f863abd0$36ef2ecf@tkmsftngxa12... Hi, Your problem is not related to XP or 2000. Your errors are due to the File Type Association. The My.xls error is due to that your shortcut tries to open a file called My.xls because there is a space between C:\My Doc... The second error is due to the same in that your file is located under a directory structure that will also contain spaces. Therefore its the File Type Association that needs to be updated. You can test it by putting an Excel file under the C:\Temp directory and run the shortcut from there. Best regards KM --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (). Version: 6.0.373 / Virus Database: 208 - Release Date: 01/07/2002 |
| 2007 | 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. What will I do to function the enter key in excel again, is there any patches that I'm going to download or is there any setting that I will change to make my enter key work again in the excel. Thank you. |
| 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. |
| 2040 | 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. |
| 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. |
| 2043 | In Excel 97 I could use a custom format such as 000 to force a 1 to be 001. This is very important to a number of our codes. I can't use text for this purpose because numbers don't sort correctly as text, ie 021 wii before 003. In Excel 2000 this no longer works. Is there another way to do this?? Thanks Karren |
| 2061 | My toolbar file in Excel 97 and Windows 98SE is: C:\WINDOWS\UserName8.xlb My toolbar file in Excel 2000 and Windows 98SE is: C:\WINDOWS\Application Data\Microsoft\Excel\Excel.xlb This is UN-tested: Find the Excel.xlb file on the destination machine. Rename the Excel.xlb file on the destination machine to Excel.bak (or something else that isn't used) Copy the xxxx8.xlb file from the source machine. Paste the xxxx8.xlb file in the same location as the Excel.bak Rename the xxxx8.xlb to Excel.xlb "Tom S" <Sinskit@usa.redcross.org wrote in message news:173d301c2290b$47e4c7f0$b1e62ecf@tkmsftngxa04... How do I convert an Excel 97 file with vba code and custom menus to excel 2002. The MS Knowledge Base web site says custom toolbars will not be migrated. Then to open the *8.xlb file to view the toolbars in 2002. Problem is my computer with Windows 2000 on it does not have a *8.xlb file to open. |
| 2070 | 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 |
| 2072 | It might be just a little bit easier to format that *empty* cell as you wish, prior to copying. HTH RD "Karren" <kbeedonohoe@stny.rr.com wrote in message news:17a3501c2291b$b6e10050$19ef2ecf@tkmsftngxa01... Deborah, THANKS It worked!!! Karren -----Original Message----- To change the data into numbers: 1. Select an empty cell 2. Click the Copy button 3. Select the column of numbers 4. Choose EditPaste Special 5. Select Add, click OK Then apply the custom number format of 000 Karren wrote: Tim, When the data is brought into excel from an Oracle Database or Access, I lose the leading 0's. With the cells set to number and the formatting set to 000 or however many digits I needed, the 0's would re-appear in the cells. Now this no longer works. I don't know where to go to Complain or how maybe something else will do the same thing. As Text I can't get my leading 0's to come back. Karren -----Original Message----- It sounds like some of your data is numbers and some is text. A change in format won't affect text. (If they were all text with leading zeros, they would sort correctly, but there are other reasons for preferring numbers over text.) Tim C "Karren" <kbeedonohoe@stny.rr.com wrote in message news:1721b01c228f0$5973ab50$b1e62ecf@tkmsftngxa04... In Excel 97 I could use a custom format such as 000 to force a 1 to be 001. This is very important to a number of our codes. I can't use text for this purpose because numbers don't sort correctly as text, ie 021 wii before 003. In Excel 2000 this no longer works. Is there another way to do this?? Thanks Karren . -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html . |
| 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 |
| 2075 | Hey Dave, If I adjusted the rowheight manually (moving it even slightly), then the rowheight ***would autoadjust*** (until I autofit the row again).<<< This is a typo and you meant "would not", didn't you ? From what I've seen, *any* manual adjustment turns *off* autoadjust. Or, did you mean something else ? Regards, RD "Dave Peterson" <ec35720@msn.com wrote in message news:3D2E0DA5.4DC86A22@msn.com... I don't think it's the boldness of the cell. I tried this just now. I turned wraptext on for a cell, then hit the bold button. did a bunch of typing and when I hit enter, the rowheight was ok. Turned bold off and it still worked ok. But.... If I adjusted the rowheight manually (moving it even slightly), then the rowheight would autoadjust (until I autofit the row again). What'd'ya think? Ariel wrote: Hi all, first, let me thank everyone -- I can't tell you how many times i've found solutions to problems here! Here's my problem... excel 2000 running on a w2k box. I have a cell that has word wrap on and the row is set up for autofit. When I enter text, it seems to work properly most of the time, allowing me to put in gobs of text and wrapping properly. However, there are times where there's one word that doesn't wrap to a new line and then autofit won't grow the row to a new height that makes that one word appear. So, I noticed that I'd designed the cells that are having this problem to be BOLD. Low and behold, when I turn the format to normal (turning bold off), the size of the cell and row are perfect. When I turn bold back on, the one word that should go on the new line because of the extra size of all the letters dissappears. If i turn word wrap off and autofit off and then turn them back on - it all goes back to the size that works for NORMAL text - the bolding just doesn't seem to work right. If I add another word to the text, then it wraps and autofits correctly, so I'm pretty sure it is just the BOLD format that is throwing things off. I've searched high and low on the newsgroups and can't find an existing solution. I also can't just run out to the MS site and download any patches that might fix this as this spreadsheet will be used by hundreds of folks and I can't be patching excel for all those folks. Anyone have any ideas? (besides the obvious solution to turn the cell to NORMAL instead of BOLD - it would just not look right with all the other cells.) Thanks! Ariel -- Dave Peterson ec35720@msn.com |
| 2084 | When I try to open an Excel 2000 premium file from a floppy disk, I get the following message: file.xls cannot be accessed. The file may be read-only or you may be trying to access a read-only location. Or, the server the document is stored on may not be responding. I have other xls files on the same disc and I can open them. This file is not tagged read-only. Can anyone help??? Thanks |
| 2097 | Mike, If you need to use a Forms control button you can create the coloured effect by grouping a textbox over the button. You could also use an autoshaped Bevel to create a psuedo button. I have worked examples of these, if you are interested email me and I will send you. Cheers Andy "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. |
| 2117 | I have a student who is on an Excel 2000 course, but only has Works 2002 installed on his PC at home. This causes a problem in that he cannot open any files he creates at home as they are saved as wks files that Excel 2000 cannot read. I know there is a converter program to open Works 2002 WPS wordprocessor files, but cannot find any similar program for the Works spreadsheets. Can any one help???? Thanks Trevor |
| 2119 | In Works, he can save the files as Excel 2000 files. 1. Choose FileSave As 2. From the Save As Type drodown, choose Excel 97-2000 (*.xls) 3. Click Save Trevor wrote: I have a student who is on an Excel 2000 course, but only has Works 2002 installed on his PC at home. This causes a problem in that he cannot open any files he creates at home as they are saved as wks files that Excel 2000 cannot read. I know there is a converter program to open Works 2002 WPS wordprocessor files, but cannot find any similar program for the Works spreadsheets. Can any one help???? Thanks Trevor -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |