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
| 197 | Just a question and guesses: Does excel blow up (with the same error) with any other workbook? If yes, then it's probably not this workbook. If no, then maybe the workbook is corrupted (like Dave suggested). I've had luck using xl2002 opening files that both xl97 and xl2k couldn't open. Sometimes formatting was lost, but the data/formulas/macros were there. Other people have reported that Sun's OpenOffice/StarOffice can sometimes open corrupted excel files. (It used to be between 80-110 meg download. I looked and decided that it's too big for my puny modem!) Not-for-free StarOffice. /software/download/desktop_app.html Free version /dev_docs/source/1.0.0/index.html IrelandA wrote: Dear All I have got a 3MB worksheet which I cannot open. When I try to open it, I get the following dialogue box: =================================== Application Error The instruction at "0x30099749f" referenced memory at "0x00000002". The memory could not be "read". Click on OK to terminate the application. Click on Cancel to debug the application. =================================== Even if I click on "Cancel", it doesn't open Excel, so I can't view the spreadsheet at all. Any help/advice gratefully received... Regards Alistair -- Dave Peterson ec35720@msn.com |
| 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 |
| 211 | This might work for you: Tools|macros Type in the name of your function Hit the options button You have limited space to type in a short description. click ok (to go back one dialog) click Cancel (yep, cancel) With nice parameter names, it might be ok. Miguel Enriquez wrote: first: hi all Second: if i go to menu Insert-Function and Choose the Function Sum at the bottom of the box appear (say me) a little help..... and too for each parameter, right? Third: i make a function named NtoW (for converter Number to Words) i write my own function or macro, i save these macro (Save As.... Complement) and i can use always when i open Excel, but my question is: How can i too write a little Description of my Function and too for each parameter? (the heading of my function is: Public Function NtoW(ByVal NumeroaConvertir As String, Optional MAY_min_Amb As String = "M", Optional Sexo As String = "M", Optional Unidades As String = "PESOS") As String) thanks -- Dave Peterson ec35720@msn.com |
| 216 | Checkout the Duplicate Finder on my site. Bob Flanagan macro Systems -ins.com Productivity add-ins and downloadable books on VB macros for Excel "jvoortman" <jvoortman@canada.com wrote in message news:1010501c21712$c9ba86d0$39ef2ecf@TKMSFTNGXA08... when I have a "potential" customer list in excel, with companies name in column1, and street address in column 2, and city in column 3, and province in column 4, and postal code in column 5,and phone number in column 6, and fax number in column 7, and watts number in column 8, email, in 9, and web url in column 10. Now I would like to merge two different lists with the same layouts, but i am afraid that there will be some duplication (because some companies are listed under different categories in the yellow pages), and I want to know if there is a short cut to "pointing out", "eliminating" the doubles? |
| 217 | Very nice function for most people's email address. But there are some knuckleheads that have numbers in their addresses: You might want to change this line (but it occurs twice): If Mid(s, i, 1) Like "[A-Za-z.-]" Then to something like: If Mid(s, i, 1) Like "[A-Za-z0-9.-]" Then In fact, if you see email addresses that aren't alpha numeric/with dashes, you might want to add those characters, too. (Underscore comes to mind.) From ec35720@msn.com <vbg John Walkenbach wrote: You can use a custom VBA function. Copy the code below and paste it to a VBA module. Then you can use it in a formula, like this: =ExtractEmailAddress(A1) If the string has more than one email address, it returns the first one only. Function ExtractEmailAddress(s As String) As String Dim AtSignLocation As Long Dim i As Long Dim TempStr As String 'Get location of the @ AtSignLocation = VBA.InStr(s, "@") If AtSignLocation = 0 Then ExtractEmailAddress = "" 'not found Else TempStr = "" 'Get 1st half of email address For i = AtSignLocation - 1 To 1 Step -1 If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = Mid(s, i, 1) & TempStr Else Exit For End If Next i 'get 2nd half TempStr = TempStr & "@" For i = AtSignLocation + 1 To Len(s) If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = TempStr & Mid(s, i, 1) Else Exit For End If Next i End If 'Remove trailing period if it exists If Right(TempStr, 1) = "." Then TempStr = Left(TempStr, Len(TempStr) - 1) ExtractEmailAddress = TempStr End Function John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Tom Kosensky" <mrycleptREMOVE@prodigy.net wrote in message news:L%JP8.3019$Hj3.136240240@newssvr15.news.prodigy.com... Let's say I have cells that look like this: "Hi, I'm Pete, and my e-mail address is pete@email.com" "To get help on this feature, e-mail John at john@email.com. Remember, John only returns e-mails between 8 am and 2 pm" Is there anyway in Excel to extract the e-mail address from the string and put it into another cell? (IE, in the example above, I'd be left with pete@email.com and john@email.com in their own separate cells.) Any help would be appreciated - Tom -- Dave Peterson ec35720@msn.com |
| 220 | i open my book with my code, i go to Tools-macros-macros but i not see any macro, for i can enter to my code i need go to: Tools-macros-Visual Basic Editor then i cant enter to OPTIONS... thanks :( regards. |
| 222 | How long would you like to be annoying your readers. Get really boring and trashy after say 3 seconds. There is no flashing font built into Excel, you would have to start a timer and flash the color (change the color) say every second -- stealing from the performance of your Excel. Advise change the color so you won't be confused with bad code, but either way it would be difficult to tell the difference. You might want to look at cell validation instead. Data Validation, Debra Dalgleish at contextures.com /xlDataVal01.html Debra's is better than mine but mine has some formula examples: /dmcritchie/excel/validation.htm since we don't know what you are really going after as an error. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "LK" <lazark@precisemailing.com wrote in message news:102de01c21716$77665630$3bef2ecf@TKMSFTNGXA10... is it possible to blink or flash a cell to get the readers attention? |
| 223 | See my page on Events /dmcritchie/excel/event.htm Unlike regular macros which are installed in regular modules, Workbook Events are installed in ThisWorkBook the following manner: F11 (Visual Basic Editor), Ctrl+R (VBA Project), under the name of your workbook you see Microsoft Excel Objects, then before Modules you see ThisWorkBook, doubleclick and paste the code into the code window (F7). HTH, , Microsoft MVP - Excel My Excel macros: /dmcritchie/excel/excel.htm "LK" <lazark@precisemailing.com wrote in message news:e4f701c21718$438b45d0$2ae2c90a@hosting.microsoft.com... Well as a novice, this makes no sense to me at all. Can you repost this making it easier to understand. Thanks -----Original Message----- One way for the novice users in thisworkbook Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) ' will clear the clipboard when the sheet is deactivated, will fire each 'time the user switches to a new sheet to paste the data. Application.CutCopyMode = False End Sub Dave -----Original Message----- Howdy, Does anyone know how to prevent someone from using the copy function on an excel file. I want to be able to distribute a file to my office and only allow people to view it. I know how to block them from edit the file itself by protecting the workbook, but people can still copy and past the info into a new sheet. Is there a way to stop this? Thanks, Eddie LeBreton . . |
| 227 | Good point, Dave. I should have pointed out that it was not tested thoroughly. I whipped it off in about five minutes. Here's Version 2.0. I used a Const declaration to simplify changes to that list of characters. Function ExtractEmailAddress(s As String) As String Dim AtSignLocation As Long Dim i As Long Dim TempStr As String Const CharList As String = "[A-Za-z0-9.-_]" 'Get location of the @ AtSignLocation = VBA.InStr(s, "@") If AtSignLocation = 0 Then ExtractEmailAddress = "" 'not found Else TempStr = "" 'Get 1st half of email address For i = AtSignLocation - 1 To 1 Step -1 If Mid(s, i, 1) Like CharList Then TempStr = Mid(s, i, 1) & TempStr Else Exit For End If Next i 'get 2nd half TempStr = TempStr & "@" For i = AtSignLocation + 1 To Len(s) If Mid(s, i, 1) Like CharList Then TempStr = TempStr & Mid(s, i, 1) Else Exit For End If Next i End If 'Remove trailing period if it exists If Right(TempStr, 1) = "." Then TempStr = Left(TempStr, Len(TempStr) - 1) ExtractEmailAddress = TempStr End Function -John "Dave Peterson" <ec35720@msn.com wrote in message news:3D0FB29E.3151E5EE@msn.com... Very nice function for most people's email address. But there are some knuckleheads that have numbers in their addresses: You might want to change this line (but it occurs twice): If Mid(s, i, 1) Like "[A-Za-z.-]" Then to something like: If Mid(s, i, 1) Like "[A-Za-z0-9.-]" Then In fact, if you see email addresses that aren't alpha numeric/with dashes, you might want to add those characters, too. (Underscore comes to mind.) From ec35720@msn.com <vbg John Walkenbach wrote: You can use a custom VBA function. Copy the code below and paste it to a VBA module. Then you can use it in a formula, like this: =ExtractEmailAddress(A1) If the string has more than one email address, it returns the first one only. Function ExtractEmailAddress(s As String) As String Dim AtSignLocation As Long Dim i As Long Dim TempStr As String 'Get location of the @ AtSignLocation = VBA.InStr(s, "@") If AtSignLocation = 0 Then ExtractEmailAddress = "" 'not found Else TempStr = "" 'Get 1st half of email address For i = AtSignLocation - 1 To 1 Step -1 If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = Mid(s, i, 1) & TempStr Else Exit For End If Next i 'get 2nd half TempStr = TempStr & "@" For i = AtSignLocation + 1 To Len(s) If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = TempStr & Mid(s, i, 1) Else Exit For End If Next i End If 'Remove trailing period if it exists If Right(TempStr, 1) = "." Then TempStr = Left(TempStr, Len(TempStr) - 1) ExtractEmailAddress = TempStr End Function John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Tom Kosensky" <mrycleptREMOVE@prodigy.net wrote in message news:L%JP8.3019$Hj3.136240240@newssvr15.news.prodigy.com... Let's say I have cells that look like this: "Hi, I'm Pete, and my e-mail address is pete@email.com" "To get help on this feature, e-mail John at john@email.com. Remember, John only returns e-mails between 8 am and 2 pm" Is there anyway in Excel to extract the e-mail address from the string and put it into another cell? (IE, in the example above, I'd be left with pete@email.com and john@email.com in their own separate cells.) Any help would be appreciated - Tom -- Dave Peterson ec35720@msn.com |
| 228 | Oops, the hyphen must be the last character in the list! Change it to this: Const CharList As String = "[A-Za-z0-9._-]" -John "John Walkenbach" <john@j-walk.com wrote in message news:O4TZOCyFCHA.2076@tkmsftngp04... Good point, Dave. I should have pointed out that it was not tested thoroughly. I whipped it off in about five minutes. Here's Version 2.0. I used a Const declaration to simplify changes to that list of characters. Function ExtractEmailAddress(s As String) As String Dim AtSignLocation As Long Dim i As Long Dim TempStr As String Const CharList As String = "[A-Za-z0-9.-_]" 'Get location of the @ AtSignLocation = VBA.InStr(s, "@") If AtSignLocation = 0 Then ExtractEmailAddress = "" 'not found Else TempStr = "" 'Get 1st half of email address For i = AtSignLocation - 1 To 1 Step -1 If Mid(s, i, 1) Like CharList Then TempStr = Mid(s, i, 1) & TempStr Else Exit For End If Next i 'get 2nd half TempStr = TempStr & "@" For i = AtSignLocation + 1 To Len(s) If Mid(s, i, 1) Like CharList Then TempStr = TempStr & Mid(s, i, 1) Else Exit For End If Next i End If 'Remove trailing period if it exists If Right(TempStr, 1) = "." Then TempStr = Left(TempStr, Len(TempStr) - 1) ExtractEmailAddress = TempStr End Function -John "Dave Peterson" <ec35720@msn.com wrote in message news:3D0FB29E.3151E5EE@msn.com... Very nice function for most people's email address. But there are some knuckleheads that have numbers in their addresses: You might want to change this line (but it occurs twice): If Mid(s, i, 1) Like "[A-Za-z.-]" Then to something like: If Mid(s, i, 1) Like "[A-Za-z0-9.-]" Then In fact, if you see email addresses that aren't alpha numeric/with dashes, you might want to add those characters, too. (Underscore comes to mind.) From ec35720@msn.com <vbg John Walkenbach wrote: You can use a custom VBA function. Copy the code below and paste it to a VBA module. Then you can use it in a formula, like this: =ExtractEmailAddress(A1) If the string has more than one email address, it returns the first one only. Function ExtractEmailAddress(s As String) As String Dim AtSignLocation As Long Dim i As Long Dim TempStr As String 'Get location of the @ AtSignLocation = VBA.InStr(s, "@") If AtSignLocation = 0 Then ExtractEmailAddress = "" 'not found Else TempStr = "" 'Get 1st half of email address For i = AtSignLocation - 1 To 1 Step -1 If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = Mid(s, i, 1) & TempStr Else Exit For End If Next i 'get 2nd half TempStr = TempStr & "@" For i = AtSignLocation + 1 To Len(s) If Mid(s, i, 1) Like "[A-Za-z.-]" Then TempStr = TempStr & Mid(s, i, 1) Else Exit For End If Next i End If 'Remove trailing period if it exists If Right(TempStr, 1) = "." Then TempStr = Left(TempStr, Len(TempStr) - 1) ExtractEmailAddress = TempStr End Function John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Tom Kosensky" <mrycleptREMOVE@prodigy.net wrote in message news:L%JP8.3019$Hj3.136240240@newssvr15.news.prodigy.com... Let's say I have cells that look like this: "Hi, I'm Pete, and my e-mail address is pete@email.com" "To get help on this feature, e-mail John at john@email.com. Remember, John only returns e-mails between 8 am and 2 pm" Is there anyway in Excel to extract the e-mail address from the string and put it into another cell? (IE, in the example above, I'd be left with pete@email.com and john@email.com in their own separate cells.) Any help would be appreciated - Tom -- Dave Peterson ec35720@msn.com |
| 239 | You can't see your function, but you can still type it in. "Miguel Enriquez....." wrote: i open my book with my code, i go to Tools-macros-macros but i not see any macro, for i can enter to my code i need go to: Tools-macros-Visual Basic Editor then i cant enter to OPTIONS... thanks :( regards. -- Dave Peterson ec35720@msn.com |
| 243 | Hi George, See NETWORKDAYS Worksheet Function in HELP NETWORKDAYS(start_date,end_date,holidays) HTH, datetime.htm has date and time relate info on my site. , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm "George" <gjbeaupre@archchemicals.com wrote in message How can I calculate the number of days between two dates taking into account workdays only (i.e. Monday-Friday). |
| 265 | no man not see none, i can choose any macro, if i type the name of my macro not enable the button Options...... i only can see my code if i go to: Tools-macros-Visual Basic Editor ... thanks.... |
| 267 | Hi ("V x" <death@death.com), Don't know what you mean by other times, is the program supposed to figure this out or meaning he can set an option. The option for direction or to remain static is in Tools, Options, Edit (tab), [x] Move <direction after enter HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "V x" <death@death.com wrote in message news:Ok4ssO7FCHA.1600@tkmsftngp12... Hi guys, I started as the IT guy for this company that has a few stragglers still using Lotus 1-2-3. I converted one lady from 1-2-3 to Excel without incident. However, this other person has some pet peeves about the way Excel works. I will give you the first one now: - After entering a number into a cell, then hitting enter, he doesn't like the fact that Excel automatically moves to the cell below the current cell. The user, instead, would like to simply stay in the same cell after entering the number into the cell. Other times, he wants to move to the cell to the right. Is there any way to change the default action of hitting enter? Thanks in advance. More to come. |
| 275 | Hi Ralph, You will have to include ISERROR i.e. =IF(ISERROR(myformula),"error note", myformula) Might look something like this, since all you need is the error indicator: =IF(ISERROR(SEARCH("Inactive",B12,1)),IF(TRIM(B12)="","","active"),"inactive") HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Ralph K." <thermometer@excite.com wrote in message news:d987c0b7.0206190920.5d224e76@posting.google.com... I have two columns. Col A is blank. Col B will have something like: Alamance Community College Asheville-Buncombe Community College - Inactive Account Some cells will say Inactive. I want to flag those inactive entries by putting the word "Inactive' or "Active" in Col A. Here's my formula: =IF(SEARCH("Inactive",B12,1)<0,"inactive","active") It does put the word "inactive" in col A, but if the word "active" is not present, it gives me this: #VALUE! Any ideas? The FIND command does not work either. Thanks. |
| 278 | David, Thanks for the information and links to your excel pages. Ralph K. ~~~~~~~~~~~~~~~~~~~ -----Original Message----- Hi Ralph, You will have to include ISERROR i.e. =IF(ISERROR(myformula),"error note", myformula) Might look something like this, since all you need is the error indicator: =IF(ISERROR(SEARCH("Inactive",B12,1)),IF(TRIM(B12) ="","","active"),"inactive") HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Ralph K." <thermometer@excite.com wrote in message news:d987c0b7.0206190920.5d224e76@posting.google.com... I have two columns. Col A is blank. Col B will have something like: Alamance Community College Asheville-Buncombe Community College - Inactive Account Some cells will say Inactive. I want to flag those inactive entries by putting the word "Inactive' or "Active" in Col A. Here's my formula: =IF(SEARCH("Inactive",B12,1)<0,"inactive","active") It does put the word "inactive" in col A, but if the word "active" is not present, it gives me this: #VALUE! Any ideas? The FIND command does not work either. Thanks. . |
| 282 | One could also use isnumber and switch places with the "active" and "inactive" =IF(LEN(B12)=0,"",IF(ISNUMBER(SEARCH("Inactive",B12,1)),"inactive","active")) -- Regards, Peo Sjoblom "" <dmcritchie@msn.com wrote in message news:OM0ezo7FCHA.2388@tkmsftngp09... Hi Ralph, You will have to include ISERROR i.e. =IF(ISERROR(myformula),"error note", myformula) Might look something like this, since all you need is the error indicator: =IF(ISERROR(SEARCH("Inactive",B12,1)),IF(TRIM(B12)="","","active"),"inactive") HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Ralph K." <thermometer@excite.com wrote in message news:d987c0b7.0206190920.5d224e76@posting.google.com... I have two columns. Col A is blank. Col B will have something like: Alamance Community College Asheville-Buncombe Community College - Inactive Account Some cells will say Inactive. I want to flag those inactive entries by putting the word "Inactive' or "Active" in Col A. Here's my formula: =IF(SEARCH("Inactive",B12,1)<0,"inactive","active") It does put the word "inactive" in col A, but if the word "active" is not present, it gives me this: #VALUE! Any ideas? The FIND command does not work either. Thanks. |
| 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. |
| 292 | You are super. Thanks for all your wonderful code. "" <dmcritchie@msn.com wrote in message news:#iu1L46FCHA.2272@tkmsftngp09... Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column < 3 And Target.Column < 5 Then Exit Sub If UCase(Target.Value) = "XXX" Then _ Target.Value = "'" & Format(Date, "yyyy-mm-dd") End Sub Target is a variable name. See Help -- Index -- ByVal look at Sub Statement about half way down. That is about as well as I can answer what Target is you don't want to substitute activecell for anything there. The activecell for me would be target.offset(1,0) since the cursor goes down after entry (tools option) and if you want to change other cells you want to turn off EnableEvents Application.EnableEvents = False '--ooo coding ooo--- Application.EnableEvents = True Worksheet Events and Workbook Events /dmcritchie/excel/event.htm HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Splash" <splash@mosquitonet.com wrote in message news:uh19i93l24r367@corp.supernews.com... Now I'm trying something different. If every time I am in column 4 I want to check to see if the text is "XXX", and, if it is, to run a little code, do I replace "Text" with "Activecell"? [That is, in "ByVal Target As Excel.Range", have "ByVal activecell as Excel.range", and then throughout the code, where Target occurred put activecell??] If so, this opens up all possiblities. |
| 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 |
| 298 | It sounds as if something is running at startup. Any files in the user's XLSTART folder are opened when Excel starts and if they are Excel files with auto-running macros they will execute. This is also true for any files in the Alternate Startup File Location if one was designated under Tools, Options, General. I'd also check for add-ins loading automatically under Tools, Add-ins. -- Jim Rech Excel MVP |
| 308 | Hi Will, Next time you may not be as fortunate as to wipe out an Excel builtin menu, it could have just as easily have been one of your own making. So I would highly recommend that you include backing your *.xlb file when you backup your data files. /dmcritchie/excel/backup.htm also suggest printing out a print screen shot of the top of your Excel window and one of your desktop as well so you can comfortably be assured that they look the same as before. More information on toolbars /dmcritchie/excel/toolbar.htm HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm |
| 310 | Hi Paul, Topics in Excel, or topics in Math. It probably won't make much difference because you can probably find what you want in the links on tutorials on my /dmcritchie/excel/excel.htm#tutorial also note that Microsoft has materials K-12 kindergarten through grade 12 on their site, and the university tutorials are great for students who have to use Excel particularly for those who need to use Excel without taking a course in Excel. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Paul Reeves" <pwr@22balmoral.freeserve.co.uk wrote in message news:u83Tr48FCHA.2520@tkmsftngp13... I'm a maths teacher and would like to use excel as a resource. Does anyone know where I can get some ideas as to the topics I could cover, and how I could cover them? Paul |
| 311 | Excel is capable of displaying only 56 colors at a time (it's color palette). The default color palette contains 46 different colors - 10 of them are duplicated. If you use VBA to specify an RGB color that's not in the palette, Excel will use the closest match. In other words, if you write a macro to change a cell's color using RGB values, there is no guarantee that the actual color will be displayed. Here's a macro that will give you some info about the color palette in the active workboook. It adds a new worksheet and then write six columns of data. Sub DisplayColorPalette() Dim r As Long Worksheets.Add Range("A1") = "Index" Range("B1") = "Color" Range("C1") = "Value" Range("D1") = "Red" Range("E1") = "Green" Range("F1") = "Blue" Range("A1:F1").Font.Bold = True For r = 2 To 57 Cells(r, 1) = r - 1 Cells(r, 2).Interior.Color = ActiveWorkbook.Colors(r - 1) Cells(r, 3) = ActiveWorkbook.Colors(r - 1) Cells(r, 4) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 0 And 255 Cells(r, 5) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 1 And 255 Cells(r, 6) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 2 And 255 Next r End Sub John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Work Fast Party Horror" <news@busymonster.com wrote in message news:tQ5Q8.360$bK.67166@news.uswest.net... i'm trying to create a spreadsheet with 4 columns: red, green, blue, and color. the red, green, and blue cells are rgb values. i want the 4th cell to have its background color set to the rgb values in its row. help! this seems like it should be a simple thing to do in excel, but i can't find a way to make it happen. i've explored conditional formatting, script editing, and vb, but no luck. any suggestions on how to make this work? thanks! |
| 312 | Hi Justin, One possibility, if that other workbook is personal.xls it should be in your XLSTART library that you say you didn't change any locations. It should also be hidden Window, hide Perhaps you just need to open one of the workbooks before the other. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Justin" <arki3345@amtrak.com wrote in message news:1041701c217c9$04920920$19ef2ecf@tkmsftngxa01... Hey All, I have a workbook that has 100's of cells with formulas linking to values in another worksheet - although the locations of both of these worksheets has remained the same, somehow the worksheet with the links has 'lost' the worksheet with the source data. Every time I open it I now have to point excel to the source worksheet. is there any way to fix this short of rewriting the formulas? Thanks, Justin |
| 315 | Hi James, If that doesn't work try Stephen Bullen's page findcirc.zip /Excel/Default.htm might lead one to wonder with all the hype on the merits of circular links that just maybe Stephen once had a problem with them. (probably on someone else's workbooks) HTH, , Microsoft MVP - Excel My Excel macros: /dmcritchie/excel/excel.htm "Richard Price" <rjpriceuk@yahoo.co.uk wrote in message The status bar only indicates the cell reference when the sheet containing it is selected - at least in XL97 and 2000. So if you run through all 15 (quite quick using Ctrl+PgDn) you should see the reference pop up after "Circular:" on one of them. "James" <james_lumsden@baa.com wrote ... I am seeing Circular in the status bar but no cell reference. I have about 15 sheets and the error could be on any one of them! I was hoping there was some way to force Excel to search for the error. It could be that there are multiple errors and it does not know which one to point to. |
| 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^) |
| 323 | Thanks for the info. (50 meg might be worth it to find out if it can open files). Ron de Bruin wrote: 50 mb Dave I have download it for my brother two weeks ago.(10 minuts that is better than your puny modem) O yes I forgot somthing <vbg Regards Ron "Dave Peterson" <ec35720@msn.com schreef in bericht news:3D0FA860.66014496@msn.com... Just a question and guesses: Does excel blow up (with the same error) with any other workbook? If yes, then it's probably not this workbook. If no, then maybe the workbook is corrupted (like Dave suggested). I've had luck using xl2002 opening files that both xl97 and xl2k couldn't open. Sometimes formatting was lost, but the data/formulas/macros were there. Other people have reported that Sun's OpenOffice/StarOffice can sometimes open corrupted excel files. (It used to be between 80-110 meg download. I looked and decided that it's too big for my puny modem!) Not-for-free StarOffice. /software/download/desktop_app.html Free version /dev_docs/source/1.0.0/index.html IrelandA wrote: Dear All I have got a 3MB worksheet which I cannot open. When I try to open it, I get the following dialogue box: =================================== Application Error The instruction at "0x30099749f" referenced memory at "0x00000002". The memory could not be "read". Click on OK to terminate the application. Click on Cancel to debug the application. =================================== Even if I click on "Cancel", it doesn't open Excel, so I can't view the spreadsheet at all. Any help/advice gratefully received... Regards Alistair -- Dave Peterson ec35720@msn.com -- Dave Peterson ec35720@msn.com |
| 324 | This worked for me in xl2002 and it worked when I was running xl97. Tools|macros|macros... In the "macro Name" box: as soon as you type in the name correctly, then the "step into", "edit", "delete" and "options" button became enabled. Are you in the workbook that contains the function? Are you sure you typed in the name of the function correctly? Either of those would make it not work. Miguel Enriquez wrote: no man not see none, i can choose any macro, if i type the name of my macro not enable the button Options...... i only can see my code if i go to: Tools-macros-Visual Basic Editor ... thanks.... -- Dave Peterson ec35720@msn.com |
| 344 | Alternate Startup was the problem, thanks alot. - Michael -----Original Message----- It sounds as if something is running at startup. Any files in the user's XLSTART folder are opened when Excel starts and if they are Excel files with auto-running macros they will execute. This is also true for any files in the Alternate Startup File Location if one was designated under Tools, Options, General. I'd also check for add-ins loading automatically under Tools, Add-ins. -- Jim Rech Excel MVP . |
| 367 | -- --- HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Ted Heath" <tedheath@xtra.co.nz wrote in message news:Bj9Q8.584$K13.65138@news.xtra.co.nz... Thanks for replying but not sure what you mean Aladin when I go to format I have the following options in the drop down menu number, alignment, font, border, pattern, cell protection, style, auto format, row height, column height, justify. Right click the cell same contents. As I said I use version 4. Thanks again Ted "Aladin Akyurek" <akyurek@xs4all.nl wrote in message news:aeqv9g$otj$1@news1.xs4all.nl... Format the cell as text *before* you enter a cheque number. "Ted Heath" <tedheath@xtra.co.nz wrote in message news:Hf6Q8.524$K13.60733@news.xtra.co.nz... Hi, I have Excel version 4 for a list of cheques banked each month. It is the column which has the cheque number which is the problem. One banks cheques start with 00 but when I type these in and push enter the zeroes disappear. Can anyone help please? Cheers Ted Heath mailto:tedheath@xtra.co.nz |
| 375 | thanks, this is a great start! but, boy, the palette size is an annoying limitation. some more questions: 1. can i set up a new color palette by assigning my own specific rgb values to the 56 colors in the palette? 2. can i change color palettes when i change worksheets? that way i could give the "illusion" of having more colors by only having 56 per page...? thanks, mark "John Walkenbach" <john@j-walk.com wrote in message news:uQxYnG9FCHA.1424@tkmsftngp04... Excel is capable of displaying only 56 colors at a time (it's color palette). The default color palette contains 46 different colors - 10 of them are duplicated. If you use VBA to specify an RGB color that's not in the palette, Excel will use the closest match. In other words, if you write a macro to change a cell's color using RGB values, there is no guarantee that the actual color will be displayed. Here's a macro that will give you some info about the color palette in the active workboook. It adds a new worksheet and then write six columns of data. Sub DisplayColorPalette() Dim r As Long Worksheets.Add Range("A1") = "Index" Range("B1") = "Color" Range("C1") = "Value" Range("D1") = "Red" Range("E1") = "Green" Range("F1") = "Blue" Range("A1:F1").Font.Bold = True For r = 2 To 57 Cells(r, 1) = r - 1 Cells(r, 2).Interior.Color = ActiveWorkbook.Colors(r - 1) Cells(r, 3) = ActiveWorkbook.Colors(r - 1) Cells(r, 4) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 0 And 255 Cells(r, 5) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 1 And 255 Cells(r, 6) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 2 And 255 Next r End Sub John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Work Fast Party Horror" <news@busymonster.com wrote in message news:tQ5Q8.360$bK.67166@news.uswest.net... i'm trying to create a spreadsheet with 4 columns: red, green, blue, and color. the red, green, and blue cells are rgb values. i want the 4th cell to have its background color set to the rgb values in its row. help! this seems like it should be a simple thing to do in excel, but i can't find a way to make it happen. i've explored conditional formatting, script editing, and vb, but no luck. any suggestions on how to make this work? thanks! |
| 387 | Your basic problem is that you're using a VBA word in your function. Change Name to myName and this problem should go away. What's happening is that your code is under a worksheet. Excel thinks unqualified references belong to the thing (the worksheet that the code is in) owns that. Kind of like activesheet and Range("A1") in a general module. Since you're using Name (and I bet it wasn't declared), excel sees that as belonging to the sheet that owns the code. So this line Name = Range("P10").Value is equivalent to: me.Name = Range("P10").Value (in a general module terms--not really accurate here: activesheet.Name = Range("P10").Value) Fix your variable name and then you'll be off to the next fire! Bruce wrote: I have this macro which replaces a portion of a column with a value whish is entered by the user. For some unknown reason, when a number is entered it changes not only the specified cells in the column, but the Worksheet name too! The macro also converts the entered number to a predfeined string via an HLOOKUP. I have a lot of other macros which rely on this worksheet name and do not want it to change at all. the code is below. Please help! ElseIf Target.Address = "$P$10" Then OptionCheck = IsNumeric(Range("P10").Value) y = (Range("B15").Value) If OptionCheck Then MyVar = MsgBox("This will replace all the options below. Do you wish to continue?", 1, "Option Change") If MyVar = 1 Then For x = 16 To (15 + CInt(y)) Range("O" & x).Value = (Range("P10").Value) Next x Name = Range("P10").Value Range("P10").Value = "=HLOOKUP(" & Name & ",OptionNames,2)" End If End If -- Dave Peterson ec35720@msn.com |
| 388 | You can do it in xl2002, but you'd have to write a macro in earlier versions. You can protect a worksheet so that your code can make changes. But it has to be reset each time you open the workbook: something like: Worksheets("sheet1").Protect Password:="pass", UserInterFaceOnly:=True could go into workbook_open or auto_open. then give the users some buttons assigned to your macros: ActiveCell.EntireRow.Insert You could even prompt them and ask how many and where. Sue wrote: Is there any way to protect individual cells in a worksheet with disabling the ability to insert rows into the same worksheet? As soon as I turn on the protection, I loose the ability to insert. -- Dave Peterson ec35720@msn.com |
| 393 | I've never seen a missing link (huh?) cause this, but everyone should have a copy of Bill Manville's Findlink.xla. Download FINDLINK.ZIP from /mvp Links can hide in mysterious places and this'll find 'em. ============ But this sounds like there might be a macro hiding out. If you open this workbook with macros disabled, can you print without this error message? If yes, then we're on to something! Hit Alt-F11 to get to the VBE. Hit Ctrl-R to see the project explorer. Find your workbook's project. should look like: VBAProject (yourworkbooknamehere.xls) Expand any compressed items (like windows explorer with the box with +'s in them). Look for ThisWorkbook. Double click on that. Do you see any code in the code window (usually to the right)? Might start with: Private Sub Workbook_BeforePrint(Cancel As Boolean) If yes, then you've found the problem(?). The real question is what to do next. Should you delete it or modify it to work the way it was intended? Post back if any of this came true and you need help. Jeff wrote: Here's a new one - I can't print a document becuase I get an error message that states: "Income Statement1.xls" could not be found..blah, blah, blah. . . . The problem is that I've NEVER created a file with this name - I don't have a clue as to what it is! The worksheet I'm trying to print has NOTHING to do with Income anything! This file is not linked to anything (at least, I've never made any such links). UGH! Any ideas? Thanks! Jeff -- Dave Peterson ec35720@msn.com |
| 395 | Hi Dave, Just tried your suggestion and no luck. I rebooted and opened the file in Excel - didn't ask if there were any macros. I then followed your instructions to see if there were any macros - there are none. I'm going to download the program you just mentioned and try that. I'll post back here if that doesn't work. Jeff "Dave Peterson" <ec35720@msn.com wrote in message news:3D125E1D.FB0944ED@msn.com... I've never seen a missing link (huh?) cause this, but everyone should have a copy of Bill Manville's Findlink.xla. Download FINDLINK.ZIP from /mvp Links can hide in mysterious places and this'll find 'em. ============ But this sounds like there might be a macro hiding out. If you open this workbook with macros disabled, can you print without this error message? If yes, then we're on to something! Hit Alt-F11 to get to the VBE. Hit Ctrl-R to see the project explorer. Find your workbook's project. should look like: VBAProject (yourworkbooknamehere.xls) Expand any compressed items (like windows explorer with the box with +'s in them). Look for ThisWorkbook. Double click on that. Do you see any code in the code window (usually to the right)? Might start with: Private Sub Workbook_BeforePrint(Cancel As Boolean) If yes, then you've found the problem(?). The real question is what to do next. Should you delete it or modify it to work the way it was intended? Post back if any of this came true and you need help. Jeff wrote: Here's a new one - I can't print a document becuase I get an error message that states: "Income Statement1.xls" could not be found..blah, blah, blah. . . . The problem is that I've NEVER created a file with this name - I don't have a clue as to what it is! The worksheet I'm trying to print has NOTHING to do with Income anything! This file is not linked to anything (at least, I've never made any such links). UGH! Any ideas? Thanks! Jeff -- Dave Peterson ec35720@msn.com |
| 426 | Thnaks for your help! -----Original Message----- Your basic problem is that you're using a VBA word in your function. Change Name to myName and this problem should go away. What's happening is that your code is under a worksheet. Excel thinks unqualified references belong to the thing (the worksheet that the code is in) owns that. Kind of like activesheet and Range("A1") in a general module. Since you're using Name (and I bet it wasn't declared), excel sees that as belonging to the sheet that owns the code. So this line Name = Range("P10").Value is equivalent to: me.Name = Range("P10").Value (in a general module terms--not really accurate here: activesheet.Name = Range("P10").Value) Fix your variable name and then you'll be off to the next fire! Bruce wrote: I have this macro which replaces a portion of a column with a value whish is entered by the user. For some unknown reason, when a number is entered it changes not only the specified cells in the column, but the Worksheet name too! The macro also converts the entered number to a predfeined string via an HLOOKUP. I have a lot of other macros which rely on this worksheet name and do not want it to change at all. the code is below. Please help! ElseIf Target.Address = "$P$10" Then OptionCheck = IsNumeric(Range("P10").Value) y = (Range("B15").Value) If OptionCheck Then MyVar = MsgBox("This will replace all the options below. Do you wish to continue?", 1, "Option Change") If MyVar = 1 Then For x = 16 To (15 + CInt(y)) Range("O" & x).Value = (Range("P10").Value) Next x Name = Range("P10").Value Range("P10").Value = "=HLOOKUP(" & Name & ",OptionNames,2)" End If End If -- Dave Peterson ec35720@msn.com . |
| 481 | Using XL 97 on Win 95. I inherited a simple workbook (no macros to worry about). 1 page with raw data, another page with graphs linked to the raw data (for an "official" looking report). A few simple formulas, but nothing complex in the workbook. I haven't touched any of that- I want to make an easier data entry page, so I figured I'd build the interface, then copy the data over. The I can change the references from the "report" page to my new raw data page. So, I created a new sheet & renamed 'KRef' to hold some lists that I want in comboboxes related to data entry. Then I created a new sheet & renamed 'KEntry' and added 3 comboboxes. When I enter linked ranges, most of them work fine: KRef!A1:A4 KRef!D1:D12 _but_ if I press the '8' key above the alpha keys (inside the linked range property box), it crashes XL. I can replicate this either as part of correct syntax: KRef!A1:A8 <crash or incorrect syntax KRef!8<crash It does not wait for a further event- it crashes the moment I press '8' (e.g. it doesn't wait for me to leave the linked range property) The message I get is: [Excel] "This program has performed an illegal operation and will be shut down...if the problem persists, contact the program vendor" [Details] - EXCEL cause an invalid page fault in Module VBE.DLL as 0137:6500e1d0 (etc) My first thought was that I had a sticky alt or shift or control key, so I switched keyboards- but it still happens, even with a different keyboard. I tried using the keypad, and that 8 works ok (e.g. does not crash XL). I always thought that the same signal was sent to the CPU from the two "8" keys, but maybe not? Either way, it shouldn't be crashing- anyone have any ideas? Thanks in advance, Keith |
| 483 | You can actually accomplish that by creating a pivot table. 1. Add a dummy column label to the upper left cell. 2. Select Data * PivotTable Report 3. Select "Multiple consolidation range" option 4. Specify "I will create page fields" 5. Add the data range (A1:C3 in your example), click Next 6. Specify 0 page fields 7. Finish, to create the pivot table 8. Drag the "Column" field to the Row axis 9. Remove the totals 10. Copy the pivot table and paste special as Values 11. Fill in the gaps in the 1st column. John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "z" <diggitydown@diggitydown.com wrote in message news:113bf01c21929$eea06dc0$3bef2ecf@TKMSFTNGXA10... I would like take a set of horizontal data (like data presented in a pivot table) and make it vertical. For example, I want to take the following horizontal data.... ------------------------ | |1/02 |2/02 | ------------------------ |ABC | 100| 90| ------------------------ |DEF | 500| 600| ------------------------ and make it look like... ------------------------ |ABC |1/02 | 100| ------------------------ |ABC |2/02 | 90| ------------------------ |DEF |1/02 | 500| ------------------------ |DEF |2/02 | 600| ------------------------ Is there a function out there to do this...or, does anybody have a macro that does this. Thanks! |
| 484 | Hi Michael, Don't have an answer for you but would certainly check computer with Find for *.xlb files to see which is updated last. Is the person on a network, are any of the .xlb files read only (check file properties). Read only could occur from copying from read only media such as a CD and depending on how copied. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Michael Roth" <mroth@seibertpowder.com wrote in message news:111cb01c21924$27be3890$39ef2ecf@TKMSFTNGXA08... I have one user who is using Excel XP on Win98 who is having problems with his toolbar customizations. Every time he puts in a new toolbar, like Drawing, it disappears the next time he opens Excel. I can't find anything in the KB about this. Any Ideas? Thanks! |
| 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 |
| 509 | Hi Tony, Use this with the code you are already using: dname = "c:\mybackup\B" & Format(Now(), "yyyy_mmdd") HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "tony" <tony4fly@netscape.net wrote in message news:f6ba01c219ec$919bd380$37ef2ecf@TKMSFTNGXA13... I need help again. the macro saves a file with a given name. To the name I want to append today's date. What should be the command. Thanks a lot for the help. tony :-) |
| 511 | Dave, That's great, thanks. Is there a way I can ask for a password in order to run this macro - if not correct then it exits? Then I could leave this macro in the workbook and just protect the code. Rob. -----Original Message----- Hi Rob This code will toggle sheet protection on/off Sub ToggleProtectContent() Dim i As Integer For i = 3 To 50 If Sheets(i).ProtectContents = False Then Sheets(i).Protect Password:="rob1" Else Sheets(i).Unprotect Password:="rob1" End If Next i End Sub It only checks for Content protection. -- "Rob Pearce" <rob.pearce@consignia.com wrote in message news:f81c01c219bb$7dfb11a0$a4e62ecf@tkmsftngxa06... | I have a workbook with 50 worksheets. | | I protect sheets 3 to 50 [all being the same template for | data entry] with the same password. | | When I want to change some formula/formatting on these 48 | sheets I have to unprotect each sheet individually so that | I can eventually group them together to alter. | | Would it be possible to have some code that will loop from | shhet 3 to 50 and unprotect - the password is rob1 if | anyone could lend a hand - and conversely then to re | protect. | | I would obviously remove the macros from the workbook | afterwards! | | Thanks, | | Rob. . |
| 514 | Hi Ryan, One way would be to make it text by prefixing with a single quote. You could format the column as text. If the fraction will always be 26ths you could format as a custom format # ??/26 Text formats will not be numbers and will be left aligned by default. . HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Ryan" <oystreck@yahoo.ca wrote in message news:f6e601c219ff$777e8390$37ef2ecf@TKMSFTNGXA13... When entering a fraction into Excel, is there any way to prevent the fraction from going to lowest terms? For example, I am entering test scores, such as 13/26 and it breaks it down to 1/2. I need the data to read 13/26. Any help would be appreciated. |
| 519 | Hi Geoff, In addition to Earl's answer today to your reposted question, you can find the replies to your original question asked on the 13th (12th in my time zone) by searching Google Groups with your email address as author. Because they have same title -- Google sometimes keeps them all as if in the same thread, which can sometimes lead to interesting results across multiple years and newsgroups. /advanced_group_search Then look at the entire thread. /groups?selm=gxUN8.11148%24Hj3.36301%40newsfeeds.bigpond.com You should look in Google Groups if a reply does not show up in a day rather than automatically reposting. Some responses do take a few days, and Google is about 12 hours behind in slurping up postings. It was nice of you to allow enough time. I think most of the fault was on the servers (this time), most of us experienced a lot of difficulties, even those not using Outlook Express. I used catch up (in OE) and just look at the last few days which suits me on a lap top, and use Google Groups to look at archives. /dmcritchie/excel/xlnews.htm HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm |
| 527 | Here is one approach. This will not re-protect the sheets. You will have to use the code from the previous response for that. Sub Get_Password() On Error GoTo errPassword Dim strPassword As String Dim i As Integer '*** GET THE PASSWORD FROM USER strPassword = InputBox("Enter Password", "Unprotection Password") '*** TRY TO UNPROTECT THE FIRST SHEET Sheets(3).Unprotect Password:=strPassword '*** IF NO ERROR THEN UNPROTECT THE REST OF THE SHEETS For i = 4 To 50 Sheets(i).Unprotect Password:=strPassword Next i Exit Sub errPassword: '*** DISPLAY A MESSAGE IF PASSWORD INCORRECT AND EXIT MsgBox "You entered and incorrect password!" End Sub -----Original Message----- Dave, That's great, thanks. Is there a way I can ask for a password in order to run this macro - if not correct then it exits? Then I could leave this macro in the workbook and just protect the code. Rob. -----Original Message----- Hi Rob This code will toggle sheet protection on/off Sub ToggleProtectContent() Dim i As Integer For i = 3 To 50 If Sheets(i).ProtectContents = False Then Sheets(i).Protect Password:="rob1" Else Sheets(i).Unprotect Password:="rob1" End If Next i End Sub It only checks for Content protection. -- "Rob Pearce" <rob.pearce@consignia.com wrote in message news:f81c01c219bb$7dfb11a0$a4e62ecf@tkmsftngxa06... | I have a workbook with 50 worksheets. | | I protect sheets 3 to 50 [all being the same template for | data entry] with the same password. | | When I want to change some formula/formatting on these 48 | sheets I have to unprotect each sheet individually so that | I can eventually group them together to alter. | | Would it be possible to have some code that will loop from | shhet 3 to 50 and unprotect - the password is rob1 if | anyone could lend a hand - and conversely then to re | protect. | | I would obviously remove the macros from the workbook | afterwards! | | Thanks, | | Rob. . . |
| 539 | Hi Ken, In the unlikely event that you have the Full Acrobat you can use Copy Table on the Text drop down in the full acrobat. How to Export a Table From a PDF File into An Excel Spreadsheet /edrs/services/publications/howto/PDFtoXLS/PDFtoExcel.html This is doable though with the Free Acrobat Reader you have to select one column at a time from the table. Ctrl+v or look for boxed [T] for Text and use the drop down box to select Column Select Tool (Ctrl+v) Select the column from the table in acrobat, and paste into Excel. Carefully select the next column so everything remains lined up with the previous Column Select and Paste operation. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "KENJOHNSO" <KENJOHNSO@email.msn.com wrote in message news:Ok$VNuhGCHA.2532@tkmsftngp09... Looking for any ideas on how to import, cut & paste whatever... From PDF into a workable text /numbers format. I need to manipulate the numbers in Excel. Thanks Ken |
| 540 | Hi Ken, If you are talking about restoring from the CD that takes care of the most important part don't do anything to the existing file. If you have the file on the hard drive copy it to another file and work only from the copied file(s). Did you try opening the file without macros and without addins. (in Safe Mode) The most painless solution is obtain Java from Sun Microsystems www.sun.com and Open Office from www.openoffice.com The openoffice is the same as what Sun used to supply free in StarOffice but gave the source code free for open office and now charges for StarCalc. Believe it or not!, some companies prefer to pay, thinking they will get better service if it breaks -- I'm sure not is not your main concern. You would be downloading about 90-120MB of data from those two sites so you would definitely need to have broadband or have someone download them to a CD for you. Other painless solutions (only painless if they work) is to have someone try to read the file on their system preferable Excel 2002 which is supposed to have more recovery ability. But even if you can only find someone with Excel 97 or 2000 or a Mac comparable version they may be able to read your file. If they can they should make a copy and another copy without your most recent sheet worked on. BTW, anybody reading your file should be checking for viruses before attempting to read your file. The second half of my Backup and Recovery page is concerned with Recovery. /dmcritchie/excel/backup.htm Good Luck, these solutions are probably as good as commercial solutions. Please report back your results, including any information as to why you think the copy of the CD got messed up, or was it just copied after the file was destroyed. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Rusty" <rusty@theseams.com.au wrote in message news:3d14b62b$0$28007$afc38c87@news.optusnet.com.au... My daughter's PC crashed and she can't read the backup of her most important Excel file from a CD. Is there a freeware or shareware utility that I can use to recover the file? She can't afford to pay USD$90 for the commercial programs. Thanks for your suggestions. Ken Russell Sydney |
| 546 | Hi Tom, I don't know if a filtered list would work for your search requirement. A filtered list is treated like those are the only entries in the sheet for many purposes such as copy and paste, mail merge. To create labels with Excel as the database we generally use MS Word for the printing of labels since Excel does not have builtin capability for printing labels. See my page Using Mail Merge with data from Excel /dmcritchie/excel/mailmerg.htm I have included some summary label information to help you pick an actual label or that you can use to print same on plain paper. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Tom" <auctionid4me@yahoo.co.uk wrote in message news:aa4R8.20086$ZR3.1838683@news6-win.server.ntlworld.com... Could somebody tell me how to do the following please: 1. Create printable labels from the ITEM NUMBER (ROW NUMBER) COLUMN A COLUMN B COLUMN C 2. Incorporate a search option onto a worksheet Thanks Piers |
| 547 | Hi Pat, Format, Cells is grayed out with sheet protection. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Pat Kung" <Pat.Kung@sdsmt.edu wrote in message news:1107f01c21a24$25330280$9ae62ecf@tkmsftngxa02... I am trying to sort my spreadsheet, but it keeps telling me I have merged cells and can't do that. The problem is I can't find the merged cells. I tried to highlight the whole spreadsheet and uncheck merged cells, but it is grayed out when I highlight the whole spreadsheet. Anybody have a way to find out what cells are merged so I can unmerge them? Thanks in advance |
| 603 | Zillion tks, it worked ! -----Original Message----- Hi Tony, Use this with the code you are already using: dname = "c:\mybackup\B" & Format(Now(), "yyyy_mmdd") HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "tony" <tony4fly@netscape.net wrote in message news:f6ba01c219ec$919bd380$37ef2ecf@TKMSFTNGXA13... I need help again. the macro saves a file with a given name. To the name I want to append today's date. What should be the command. Thanks a lot for the help. tony :-) . |
| 614 | As Peo says, hide it. Once you've done this and close Excel, it'll ask if you want to save your changes to Personal.xls. Choosing 'Yes' will mean that the next time you open Excel, Personal.xls will be hidden from the outset. It doesn't say anything about Personal.xls in Excel Help, but I *believe* that the *only* use of Personal.xls (which is stored in your 'alternate startup file location' - via Tools | Options | General) is for storing macros that you want to have available for all spreadsheets. (Please can somebody correct me if this is wrong.) I know that it ain't the default template (cf. Normal.dot in Word) because that's Book.xlt, if you have it, which sits in your XLStart folder. "Supereal" <none@none.com wrote in message news:11e6901c21b91$9e6ebae0$3aef2ecf@TKMSFTNGXA09... Whenever I start up excel, rather than a new workbook, PERSONAL.XLS starts up in its place. Also, whenever I double click another excel file to open up, excel starts, open my file and also opens up PERSONAL.XLS. Does anyone know why this is happening and how to stop it? Thanks. SnR |
| 620 | The Personal.xls problem seems to be well covered. The second instance of Excel opening upon double-click is usually resolved by ToolsOptionsGeneral uncheck "Ignore other Applications". HTH Gord Dibben Excel MVP - XL97 SR2 On Mon, 24 Jun 2002 18:35:00 +0100, "ikr" <ripsher[nospam]@btinternet.com wrote: As Peo says, hide it. Once you've done this and close Excel, it'll ask if you want to save your changes to Personal.xls. Choosing 'Yes' will mean that the next time you open Excel, Personal.xls will be hidden from the outset. It doesn't say anything about Personal.xls in Excel Help, but I *believe* that the *only* use of Personal.xls (which is stored in your 'alternate startup file location' - via Tools | Options | General) is for storing macros that you want to have available for all spreadsheets. (Please can somebody correct me if this is wrong.) I know that it ain't the default template (cf. Normal.dot in Word) because that's Book.xlt, if you have it, which sits in your XLStart folder. "Supereal" <none@none.com wrote in message news:11e6901c21b91$9e6ebae0$3aef2ecf@TKMSFTNGXA09... Whenever I start up excel, rather than a new workbook, PERSONAL.XLS starts up in its place. Also, whenever I double click another excel file to open up, excel starts, open my file and also opens up PERSONAL.XLS. Does anyone know why this is happening and how to stop it? Thanks. SnR |
| 635 | When you say chart area, do you really mean plot area? Here's a simple example that creates a 400x300 chart, and then sizes the plot are to be 1/2 the size of the chart object, and centered within the chart object. Sub CreateChart() Dim ChtObj As ChartObject Dim Cht As Chart Set ChtObj = ActiveSheet.ChartObjects.Add(1, 1, 400, 300) Set Cht = ChtObj.Chart Cht.SetSourceData Source:=ActiveSheet.Range("A1:A4") Cht.Location Where:=xlLocationAsObject, Name:=ActiveSheet.Name With Cht.PlotArea .Width = ChtObj.Width * 0.5 .Height = ChtObj.Height * 0.5 .Left = ChtObj.Width * 0.25 .Top = ChtObj.Height * 0.25 End With End Sub John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Fernand Lendoye" <lendoye@hotmail.com wrote in message news:fe6001c21baf$08bbd730$37ef2ecf@TKMSFTNGXA13... I'm trying to build a chart with a specific size. I'm using the height and with properties to do that. Unfortunately I don't have any control of the size of the chartarea. What I really want is to be able to control the sizes of the chartarea and plotarea. Is there any way that a linear relationship could be build between the properties(width, height) of the chart and chart area? Thanks a lot for any insight. FL |
| 654 | I keep my personal.xl* in my XLStart folder (along with book.xlt and sheet.xlt). I don't think I've ever pointed to any folder for the "alternate startup file location" (renamed in xl2002 to: "at startup, open all files in:"). ikr wrote: As Peo says, hide it. Once you've done this and close Excel, it'll ask if you want to save your changes to Personal.xls. Choosing 'Yes' will mean that the next time you open Excel, Personal.xls will be hidden from the outset. It doesn't say anything about Personal.xls in Excel Help, but I *believe* that the *only* use of Personal.xls (which is stored in your 'alternate startup file location' - via Tools | Options | General) is for storing macros that you want to have available for all spreadsheets. (Please can somebody correct me if this is wrong.) I know that it ain't the default template (cf. Normal.dot in Word) because that's Book.xlt, if you have it, which sits in your XLStart folder. "Supereal" <none@none.com wrote in message news:11e6901c21b91$9e6ebae0$3aef2ecf@TKMSFTNGXA09... Whenever I start up excel, rather than a new workbook, PERSONAL.XLS starts up in its place. Also, whenever I double click another excel file to open up, excel starts, open my file and also opens up PERSONAL.XLS. Does anyone know why this is happening and how to stop it? Thanks. SnR -- Dave Peterson ec35720@msn.com |
| 665 | How about just using a macro assigned to some keys that can easily be pressed at once (e.g. Ctrl+Shift+z)? Try this: ToolsmacroRecord Press Shift+z to name the shortcut key. Click OK. Then stop the macro. do this: ToolsmacromacrosEdit delete anything in the macro that was started and copy and paste the macro shown here. Sub macro1() ActiveCell = ActiveCell + 1 End Sub Edit the macro if you ever want to change the increment value or else add another macro to do another value. --Brian Taylor Refinate (C)2001, 20 free trials. Click to: Rearrange Cells or Cell Content, Find Duplicates, Count Items, Import, much more. www.adetaylor.com "V x" <death@death.com wrote in message news:evt4YP7GCHA.404@tkmsftngp13... One of my sales managers wants to be able to use a cell as a "tally" space, of sorts. In other words, she wants to be able to hit the number "1" key to just add one to whatever number or sum already exists in the cell. Is this possible? Normally, of course, hitting the number "1" key in a cell containing a number of formula would simply overwrite the existing contents with simply "1." This tally method, apparently, she prefers over having to edit the formula, like =1+1+1+1 Thanks in advance, Vinod |
| 679 | Dear MS Support Team, I am having problem with one of the excel s/sheet I used until Friday = evening. I am using MS-Excel 97 SR-1 and this s/sheet worked perfectly = until Friday evening. It is a very simple data-only s/sheet..no macros/ = scripts/ program at all. I saved this successfully on my 'C' drive on = Friday night and tried to open it this morning but got a message box = (attached in the file) with the following error - "Error Message : = Address: excel.exe - Application Error" It doesn't even allow me to open the s/sheet. I have checked this file = for virus and there are none. Would appreciate if you can help me in this matter as I spent = considerable amount of time preparing this s/sheet. Regards, Ash |
| 685 | This is primarily peer to peer support. If you want to talk to microsoft, try using and see what your options are. You probably have a corrupt file problem. You probably need to go to your back up copy. If you have macros in the sheet, try holding down the shift key while you open the file Ashu <ashuintokyo@hotmail.com wrote in message news:1225001c21bef$d031bf10$39ef2ecf@TKMSFTNGXA08... Dear MS Support Team, I am having problem with one of the excel s/sheet I used until Friday evening. I am using MS-Excel 97 SR-1 and this s/sheet worked perfectly until Friday evening. It is a very simple data-only s/sheet..no macros/ scripts/ program at all. I saved this successfully on my 'C' drive on Friday night and tried to open it this morning but got a message box (attached in the file) with the following error - "Error Message : Address: excel.exe - Application Error" It doesn't even allow me to open the s/sheet. I have checked this file for virus and there are none. Would appreciate if you can help me in this matter as I spent considerable amount of time preparing this s/sheet. Regards, Ash |
| 698 | Hi, Some things to do: Options to try and open a corrupt file - Set calculation to manual - open the file, but disable macros (assuming you've set macro security to medium: Tools, macro, security) - As soon as you've clicked the disable macros button, press control-pageup or control-pagedown, thus changing sheets. If that does not work, try creating a link to the file: ='c:\my documents\[MyFileName.xls]Sheet1'!A1 and copy right and down. This at least gets you the worksheets values. Sometimes the Excel viewer (or Word) enables you to open the file and copy information out of it. Also, Excel XP can sometimes repair XL files with trouble. Lastly: Download the office suite from www dot sun dot com slash staroffice (awkward spelling to hopefully avoid another autodeletion of posting...) it's a killer app for file recovery. Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- Dear MS Support Team, I am having problem with one of the excel s/sheet I used until Friday evening. I am using MS-Excel 97 SR-1 and this s/sheet worked perfectly until Friday evening. It is a very simple data-only s/sheet..no macros/ scripts/ program at all. I saved this successfully on my 'C' drive on Friday night and tried to open it this morning but got a message box (attached in the file) with the following error - "Error Message : Address: excel.exe - Application Error" It doesn't even allow me to open the s/sheet. I have checked this file for virus and there are none. Would appreciate if you can help me in this matter as I spent considerable amount of time preparing this s/sheet. Regards, Ash . |
| 721 | Hi, Itried creating an add-in tohave some macros and functions of my own available on any workbook, but Ican't seem to be able access the macros and funs on the add-in I create after I install it with the add-in manager. I can access the code with the VB Editor, but can't find neither macros' name on the "run macro" menu nor functions' name in the insert "function menu" Guess there's something wrong with the code itself, but I can't figure out what exactly. Please mail me an answer directly, since I got problem accessing newsgroups. In case you do, please remove "NOSPAM" from address. Thanks a lot |
| 724 | Hi, I am able to pass parameters to a VBA macro from the 'Assign macro' dialog by enclosing in single quotes. e.g. Book1!'Msg "Hello"' to execute the macro in the open workbook 'Book1': Public Sub Msg(m As String) MsgBox m End Sub However I am unable to do the same for a macro in an open Add-in. If I try 'MyAddin.xla'!'Foo "param1"' I am getting an error saying The macro 'Book1'!'Foo "param1"' cannot be found. I can invoke macros with no parameters in the Add-in. Anybody know why this is not working, and if there is a way to make it work? Thanks in advance. |
| 756 | Thanks Dave, However my problem is not calling it from within a VBA macro but I want an Excel developer who may know nothing about VBA to pass parameters to a VBA macro contained in a loaded Add-in from a button he has added to the spreadsheet. The syntax I used ('MyAddin.xla'!'Foo "param1"') from within the Assign macro dialog of the excel button control seemed to be ignoring the 'MyAddin.xla' prefix and assuming it was in the workbook containing the button (Book1.xls). Steve What I was hoping for was "Dave Peterson" <ec35720@msn.com wrote in message news:3D18E8DF.4D4DEB54@msn.com... When I tried to pass a string to your Msg sub, I could do it like this: Application.Run "'book1.xla'!msg", "hi there!" I could also do it like this, if I did a Tools|references and set a reference to that other project. msg "hi there2" But I'm not sure if this was the answer to your question.... Steve Legg wrote: Hi, I am able to pass parameters to a VBA macro from the 'Assign macro' dialog by enclosing in single quotes. e.g. Book1!'Msg "Hello"' to execute the macro in the open workbook 'Book1': Public Sub Msg(m As String) MsgBox m End Sub However I am unable to do the same for a macro in an open Add-in. If I try 'MyAddin.xla'!'Foo "param1"' I am getting an error saying The macro 'Book1'!'Foo "param1"' cannot be found. I can invoke macros with no parameters in the Add-in. Anybody know why this is not working, and if there is a way to make it work? Thanks in advance. -- Dave Peterson ec35720@msn.com |