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 |
| 27 | I believe I am just making the built in scrollbar visible. How do you add code to make the contents of a form "scroll?" -----Original Message----- Set the width and height properties to increase or decrease the size. Are you just making the builtin scrollbar visible? Have you added code to the scroll event of the userform to manage the userform? Martin <MartinCregg@ncodeinc.com wrote in message news:d44101c2141c$7ca0fbc0$36ef2ecf@tkmsftngxa12... I am adding controls to a form directly using Excel VBA. Depending on certain previously selected options the number of controls I add wiil increase thus they appear "off the form." I was hoping to add a scrollbar and scroll down to reveal the added forms. However, the scroll bar does nothing. On a related note, is there a way of changing the size of a form once it is active. . |
| 32 | Hi, Tom. I want to "set" the custom format for Textbox2. I am getting the value for Textbox2 from Textbox1. As I exit Textbox1, I give Textbox2 the value of (1-Textbox1.value). For example, I don't want to see "0.5" in Textbox2, but ".5". On the Excel spreadsheet, I have a custom NumberFormat ".0". I unsuccessfully tried Format for both Value and Text: Private Sub Textbox2_Change() Format (tbxTextbox2.Value), ".0" ' Format (tbxTextbox2.Text), ".0" End Sub and, also Private Sub Textbox1_Exit() Format (tbxTextbox2.value), ".0" End Sub Any ideas, Tom? Thanks for your help. "Tom Ogilvy" <twogilvy@msn.com wrote in message news:e##sjhVFCHA.1716@tkmsftngp07... for the most part, it should work. You have to use the Format command. If you are filling the textbox from the cell with code, it might be easier to do Textbox1.Text = Range("A1").Text You might want to post the specifics. Regards, Tom Ogilvy Splash <splash@mosquitonet.com wrote in message news:ugpfmqgvnqmec7@corp.supernews.com... I have defined a custom "NumberFormat" for my Excel cells. Is there a way to make that same custom format apply to a textbox in a userform that runs from an Excel macro? (I've asked the VBA group, with no responses). Thanks, guys. |
| 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 . |
| 111 | I am looking for a VBA statement that will coun the # of worksheets in an excel workbook. Can you help me with this? |
| 114 | Dan, ActiveWorkbook.Worksheets.Count Workbooks("My Workbook.xls").Worksheets.Count HTH, Bernie Dan Flannery wrote: I am looking for a VBA statement that will coun the # of worksheets in an excel workbook. Can you help me with this? |
| 139 | Because you are creating multiple inter-related sets of data (relational tables) for inventory, invoicing, payables, and jobs, MS Access is the optimum application to use. Access has the ability to create data entry and display forms, as well as reports that provide the specific management information structure desired. However, the flat tables in MS Excel, if constructed properly, can be related using MS Query, and reports can be generated with VBA Code and Userforms. -----Original Message----- Hi all I work for a small building business in the uk. We need a program that will moniter all our stock as well as the the jobs in progress ie: Moniter stock Moniter payments in(interim etc) Moniter payments out Moniter several ongoing jobs at once We've have had no luck as yet in our search (Iv'e been on google all day) I was wondering if it might be better to MS access or excel Any input most welcome Cheers Jill . |
| 151 | I have a workbook that has some 200 sheets. I would like to copy the three same cells from all worksheets and put it on a worksheet called "Foremen" within the same workbook. I tried modifying some code that I already had been using for another process but can't seem to get it quite right seeing as how I am far from a VBA master. With it the way it is now I get an error saying the subscript is out of range. Can anyone help me out with this? Below is the code I have been trying to tweak. "Foremen" is the leftmost worksheet, the first one if you will and I do not want to extract data from it. This is where all the data should be deposited. The "GBG001" worksheet in the range is just to right of "Foremen", the second worksheet in the workbook and the first one I want to extract data from. The "RWW2W0208" worksheet is the far right worksheet, the last one in the workbook. "012-204-2" is the name of the excel file. Sub CreateList2() Dim w As Range For Each w In Workbooks("012-204-2").Range("GBG001:RWW2W0208") With Worksheets("Foremen") .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value _ = Worksheets(w)Cells(4, 2).Value .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value _ = Worksheets(w).Cells(5, 2).Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value _ = Worksheets(w).Cells(7, 1).Value End With Next End Sub Any ideas are aprreciated. Thanks in advance! Bill |
| 162 | Sub CreateList3() For Each ws In Sheets If ws.Name < "Foremen" Then w = ws.Name With Sheets("Foremen") .Cells(Rows.Count, "A").End(xlUp).Offset(1) = Sheets(w).Cells(4, 2) .Cells(Rows.Count, "B").End(xlUp).Offset(1) = Sheets(w).Cells(5, 2) .Cells(Rows.Count, "C").End(xlUp).Offset(1) = Sheets(w).Cells(7, 1) End With End If Next End Sub -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Bill Kelly" <bkelly@pilchuck-usa.com wrote in message news:OJAXLPuFCHA.1916@tkmsftngp13... I have a workbook that has some 200 sheets. I would like to copy the three same cells from all worksheets and put it on a worksheet called "Foremen" within the same workbook. I tried modifying some code that I already had been using for another process but can't seem to get it quite right seeing as how I am far from a VBA master. With it the way it is now I get an error saying the subscript is out of range. Can anyone help me out with this? Below is the code I have been trying to tweak. "Foremen" is the leftmost worksheet, the first one if you will and I do not want to extract data from it. This is where all the data should be deposited. The "GBG001" worksheet in the range is just to right of "Foremen", the second worksheet in the workbook and the first one I want to extract data from. The "RWW2W0208" worksheet is the far right worksheet, the last one in the workbook. "012-204-2" is the name of the excel file. Sub CreateList2() Dim w As Range For Each w In Workbooks("012-204-2").Range("GBG001:RWW2W0208") With Worksheets("Foremen") .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value _ = Worksheets(w)Cells(4, 2).Value .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value _ = Worksheets(w).Cells(5, 2).Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value _ = Worksheets(w).Cells(7, 1).Value End With Next End Sub Any ideas are aprreciated. Thanks in advance! Bill |
| 164 | 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 |
| 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 |
| 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 |
| 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 |
| 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! |
| 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! |
| 380 | I have a problem that appears to be fairly widespread. I haven't, however, found a solution to my exact problem on this site... Here's the trouble. I have a system of files that I need to be able to transfer over to a different drive. I've read that as long as everything is in the same directory and the sub-directory structure and names of the sub-directories are left intact that Excel will pretend the references are relative (aka, I can move the files and they will work). Alas, it doesn't appear to be working for me. Here's the magnitude of data that I'm worried about: 26 directories, 25 of which are designed to collect data from different users (the whole reason this all isn't just one file anymore), and 1 directory to compile this information (and also serve as a source of a single piece of information for every file in the other directories). In each of the 25 directories, I will have 9 or 36 (undecided) files that I need to reference. The collection folder contains two files - fetcher (which gets the data for me) and sesdata (which the input files need to reference). The collection folder is named "master", the other inputs are all numbered. All these directories are in the directory "qpa." I try to move the directory containing both master and the input folders (qpa), and all my links which, if relative, would read "../master/sesdata" break. I would like to be able to design this system on my pc, record it to a CD, then copy it onto different computers. Due to the number of links, the edit links option simply won't work. I am willing to move the directory structure (to some extent), try VBA (throw code at me, I don't do Basic), or whatever. Or if I need to try to use Quattro Pro, tell me. |
| 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 |
| 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 |
| 400 | It sure sounds like a macro to me (but I've been wrong lots of times). Maybe there's an addin that's looking for print events?? Try running excel in safe mode. close excel Windows Start button|Run Excel /S It won't look the same, but it's a vanilla version of excel running. Try loading your workbook and printing then. If that works, then I (still) think it might be a macro. When you were in the VBE, did you notice the names of the workbooks that were open in the project explorer? Mine look like: atpvbaen.xls (atpvbaen.xla) funcres (funcres.xla) personal (personal.xla) vbaproject (book1) The first two are related to Excel's analysis toolpak (worksheet and VBA versions). The personal one is mine. The last one is the current workbook. Do you have more/different? (If you do and they look suspicious, you may want to experiment more. These addins usually reside in the XLStart folder (varies with versions of xl and versions of windows). The other spot is under Tools|Addins. Move all the files out of your xlstart, uncheck all the addins in Tools|Options and restart excel. Try your workbook. If it works then, you're on the way to finding the culprit. Start adding things back one at a time. Restart excel (each time) and try it out. If it breaks, you found it. If not, continue reloading stuff. === But I'm pretty much out of ideas after this. Good luck. ======= FWIW, I didn't think FindLink would work in this case, but don't throw it away. It'll come in handy someday! Jeff wrote: Hi Dave, Just tried the FINDLINK.ZIP program. Was able to install it just fine but it didn't find my "mystery" document. Any other ideas? Thanks, Jeff -- Dave Peterson ec35720@msn.com |
| 425 | Hi Dave, I think you've hit on something! I just tried Excel in Safe Mode and it will print! It will not, however, when I'm in regular mode. When I enter VBE, the following are listed in the Project - Project VBA window - Start (OR Start.xla) TbRun97Project (TRRUN9.XLS) VBAPROJECT (FERPA.xls) The are sub headings under the last one, which is also the current workbook. The first two items are mysteries to me and, when I click on them, ask for a password. (Why? I haven't a clue.) I also just tried unchecking all of the addins in Tools/Options (there was only one checked) and tried to find the XLStart folder (I couldn't find it using Excel but believe it may be under Windows, Application Data, Microsoft, Excel. I removed the one file found there and rebooted Excel. Still can't print. This is VERY strange - apparently, you did find something as I can print the file in safe mode. Jeff "Dave Peterson" <ec35720@msn.com wrote in message news:3D127B06.5AB8B95D@msn.com... It sure sounds like a macro to me (but I've been wrong lots of times). Maybe there's an addin that's looking for print events?? Try running excel in safe mode. close excel Windows Start button|Run Excel /S It won't look the same, but it's a vanilla version of excel running. Try loading your workbook and printing then. If that works, then I (still) think it might be a macro. When you were in the VBE, did you notice the names of the workbooks that were open in the project explorer? Mine look like: atpvbaen.xls (atpvbaen.xla) funcres (funcres.xla) personal (personal.xla) vbaproject (book1) The first two are related to Excel's analysis toolpak (worksheet and VBA versions). The personal one is mine. The last one is the current workbook. Do you have more/different? (If you do and they look suspicious, you may want to experiment more. These addins usually reside in the XLStart folder (varies with versions of xl and versions of windows). The other spot is under Tools|Addins. Move all the files out of your xlstart, uncheck all the addins in Tools|Options and restart excel. Try your workbook. If it works then, you're on the way to finding the culprit. Start adding things back one at a time. Restart excel (each time) and try it out. If it breaks, you found it. If not, continue reloading stuff. === But I'm pretty much out of ideas after this. Good luck. ======= FWIW, I didn't think FindLink would work in this case, but don't throw it away. It'll come in handy someday! Jeff wrote: Hi Dave, Just tried the FINDLINK.ZIP program. Was able to install it just fine but it didn't find my "mystery" document. Any other 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 . |
| 455 | Hello, I have a number of data files generated by a Vbasic program. The files are of identical format though with a varying number of records. I need to open all these data files into a single Excel worksheet and do some simple sums. I'm sure I'm missing something simple, but I can't see how to merge several excel files into a single worksheet, other than using copy / paste which seems a bit clumsy. With thanks KK |
| 500 | Hello, Thanks for your reply. I did look into Excel help, and found the same advice you gave me, but it was not helpful. I think you misunderstand my question I don't have copies of a shared workbook, I have several data files of identical format but with different data and with different numbers of records. I am not trying to merge changes to a shared workbook, I am trying to merge/insert/join/combine these several files into a single worksheet so that I can do some sums on all the records therein. My apologies for the confusion KK "Brian Roberts" <meorelse@hotmail.com wrote in message news:e3FLlFYGCHA.2212@tkmsftngp12... Hi, From Excel help: Merge workbooks 1.. Make sure the copies of the shared workbook that you want to merge are all stored in the same folder. To do this, you must give each copy a different name. 2.. Open the copy into which you want to merge changes. 3.. On the Tools menu, click Compare and Merge Workbooks. 4.. If prompted, save the workbook. 5.. In the Select Files to Merge into Current Workbook dialog box, click a copy that has changes to be merged, and then click OK. To merge more than one copy at the same time, hold down CTRL or SHIFT and click the file names, and then click OK. "KK" <trebor@yeleek.nospam.freeserve.co.uk wrote in message news:aev43e$r$1@newsg3.svr.pol.co.uk... Hello, I have a number of data files generated by a Vbasic program. The files are of identical format though with a varying number of records. I need to open all these data files into a single Excel worksheet and do some simple sums. I'm sure I'm missing something simple, but I can't see how to merge several excel files into a single worksheet, other than using copy / paste which seems a bit clumsy. With thanks KK |
| 506 | You could have a macro open each text(?) file individually and then have your macro do the copy and paste. But if you recall your old DOS commands, there was a way to merge text files into one. Let's say they are named MyFile0001.txt through myFile0999.txt. And they're in the same folder. Just shell to DOS (Windows Start button|Programs|MS DOS Prompt (for win98)) May be "command prompt" in NT (IIRC). Then traverse to that folder. when you're there, just type: copy myfile*.txt allfiles.txt All the files that start with myfile and have an extension of .txt will be copied into allfiles.txt. Then Exit from the dos prompt and open that one in excel. ======== There are a few warnings about the DOS copy command. You couldn't do this without getting into trouble. copy *.txt allfiles.txt It would try to add allfiles.txt to allfiles.txt. You could even do something like: copy c:\mydir\*.* c:\allfiles.txt by putting the "to" file into a separate folder, we can combine all the files using a wildcard. ======== If you still want a macro to open up each text file and do the copy and paste, post back with some more details. Are the files all in one folder? Are they the only files in that folder? If not is there a unique identifier for the group? (It's kind of the same answers you need to do the DOS copy solution, too.) good luck, KK wrote: Hello, Thanks for your reply. I did look into Excel help, and found the same advice you gave me, but it was not helpful. I think you misunderstand my question I don't have copies of a shared workbook, I have several data files of identical format but with different data and with different numbers of records. I am not trying to merge changes to a shared workbook, I am trying to merge/insert/join/combine these several files into a single worksheet so that I can do some sums on all the records therein. My apologies for the confusion KK "Brian Roberts" <meorelse@hotmail.com wrote in message news:e3FLlFYGCHA.2212@tkmsftngp12... Hi, From Excel help: Merge workbooks 1.. Make sure the copies of the shared workbook that you want to merge are all stored in the same folder. To do this, you must give each copy a different name. 2.. Open the copy into which you want to merge changes. 3.. On the Tools menu, click Compare and Merge Workbooks. 4.. If prompted, save the workbook. 5.. In the Select Files to Merge into Current Workbook dialog box, click a copy that has changes to be merged, and then click OK. To merge more than one copy at the same time, hold down CTRL or SHIFT and click the file names, and then click OK. "KK" <trebor@yeleek.nospam.freeserve.co.uk wrote in message news:aev43e$r$1@newsg3.svr.pol.co.uk... Hello, I have a number of data files generated by a Vbasic program. The files are of identical format though with a varying number of records. I need to open all these data files into a single Excel worksheet and do some simple sums. I'm sure I'm missing something simple, but I can't see how to merge several excel files into a single worksheet, other than using copy / paste which seems a bit clumsy. With thanks KK -- Dave Peterson ec35720@msn.com |
| 533 | Can anyone recommend a good book for learning Excel (2k) VBA for absolute beginners? |
| 538 | 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? |
| 553 | Phil - 1/ You could look up Data Type Summary in VBA help, which leads to a description of the various data types. 2/ You could set up a moving average in your worksheet. Say you have values in B2:B100, and you want a moving average of six consecutive numbers. In C7, enter this formula =average(B2:B7) And drag this down to C100. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <1075a01c21858$c6327760$9be62ecf@tkmsftngxa03, Phil said... Hi, I'm using excel to plot some experimentally determined data, however the resulting chart has glitches due to rounding errors. To what accuracy does excel store numbers? And, is there a smoothing type of function for plotted data, whereby say, 6 consecutive numbers, for example, are averaged to give a smoother curve? Thanks Phil |
| 593 | 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 |
| 594 | 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 |
| 596 | 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 |
| 598 | 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 | | | | | | | | |
| 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 | | | | | | | | |
| 602 | 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? |
| 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? |
| 609 | "Max Foo" <maxfoo@punkass.com wrote in message news:i9qbhu01v2lckrtn77r14ng8tqf4j73b04@4ax.com... On Sun, 23 Jun 2002 14:50:32 GMT, "Mike" <mike@nospam.com wrote: "Max Foo" <maxfoo@punkass.com wrote in message news:j5ibhuc7fqdidrd9j1ifvu8eau5ag3b3ps@4ax.com... What exactly was the probably you were having with Excel. Every one of the requirements you listed can be done in Excel. Not really. Engineering units, for example, in which the powers of 10 used are multiples of 3, aren't possible in Excel. It would be even nicer if yes, this can be done easily.. right click select 'format cell', go to custom or run this macro with the cell you want formatted changed. Selection.NumberFormat = "##0.0E+0" Hey, imagine that. It actually works... but there's certainly nothing obvious about it, is there? There's nothing in the help under "Engineering Notation," or under Number, or under Format... In fact, here's the complete text of the scientific number format help from the number format codes help page: ------------------- Scientific notation To display numbers in scientific format, use "E-," "E+," "e-," or "e+" exponent codes in a section. If a format contains a 0 (zero) or # (number sign) to the right of an exponent code, Excel displays the number in scientific format and inserts an "E" or "e". The number of 0's or #'s to the right of a code determines the number of digits in the exponent. E- or e- places a minus sign by negative exponents. E+ or e+ places a minus sign by negative exponents and a plus sign by positive exponents. ------------------- I know: I'm looking in the wrong place. I should have looked under number formats on the left side of the decimal point, and inferred what happens when I combine them with scientific notation. It sure would be easier if there was something like an "engineering notation" option, wouldn't it? Excel understood engineering suffixes the way SPICE does, and could use them in plots. As an engineer, 10u is much easier to read and understand than this too can be done.. define a function using VBA. The last thing anyone should have to do is learn a programming language to make a plot. 1E-5. Excel requires that log axes begin and end on powers of 10, so if your data extends from 1 to 5, the axis still extends from 1 to 10. That's just stupid, and it can't be changed. Excel also strangely wants linear axes to again it seems your too lazy to setup the graph manually, or automate it will VBA. Lazy my ass, buddy. I did try it manually, and verified that it works the way I stated, before I sent the first message. Maybe there is some macro that will do it, but it's certainly not obvious, and it certainly doesn't work by using the manual settings. Choose a maximum value of 5 on a log scaled axis, and Excel conveniently changes it to 10 when you click OK. Maybe you could write a macro to do this, but I can't find any documentation on it. Why not? If it's easy to do, shouldn't it be easy to find? ... Mike, you need to learn Excel VBA than anything is possible... Where do you get off with this crud? It'll do what you want, as long as you write the progam to do what you want? -- Mike -- |
| 621 | Unless you are talking about using VBA code to do this, just using worksheet functions - then the formula that displays the results would have to be in the cell where the results would be displayed. In this case, the formula would have to be in column C. Column A would have not role. in C1 =If(len(trim(D1))=0,"",D1) would be an example. John Vera <emperorj@hotmail.com wrote in message news:12cc101c21ba9$faeaf3c0$35ef2ecf@TKMSFTNGXA11... Hi again. Is there any way to make a cell change the contents of another cell or cells? for example, I'd like to have a column A verify whether column D is blank or has a value, and then make column C be blank or have a value (depending only on the D values). Thanks in advance, John |
| 637 | Jan's method works with foreground colors. Dave's works with background colors. Both are great, and I can make use of this myself. The major problem with both methods is that when you change a color, the sum or the count does not change. For example, if I change a green color to a red, neither the green totals nor the red totals changes. Can either of you offer a solution to this? Many thanks, Paul "" <DavidH@OzGrid.com wrote in message news:<jiDR8.19$926.7591@vicpull1.telstra.net... Hi Debbie I have a UDF that does this here: /VBA/Sum.htm It looks remarkably similar to Jans. -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Jan Karel Pieterse" <pieterse_home@compuserve.com wrote in message news:1186301c21b6d$0194cf30$9be62ecf@tkmsftngxa03... | Hi, | | I assumed the text has the color, not the interior. | Try this user defined function: | | Option Explicit | | Function CountColour(rRange As Range, iColor As Integer) | Dim rCell As Range | Dim lTotal As Long | Application.Volatile | For Each rCell In rRange | If rCell.Font.ColorIndex = iColor Then | lTotal = lTotal + 1 | End If | Next | CountColour = lTotal | End Function | | Now to count red cells in the range A1:A5, use this | function: | | =CountColour(A1:A5,3) | | How to enter a macro (=VBA code): | | 1. Open the Excel file you want to add the code to. | | 2. Choose Tools, Macro, Visual Basic Editor to open the | Visual Basic Editor (or press Alt+F11). This is the | design environment that stores the VBA code. If this | is | the first time you have opened the editor, you will | probably see three windows: the Project window, the | Properties window, and the Code window. | | 3. In the Project window, select the name of the workbook | you want to add the sub to (one project is listed for | each open file). | | 4. After selecting the project icon, choose Insert, | Module. | This inserts a VBA code module into the project and | places the insertion point in the Code window of that | module. | | 5. You can either type the snippet code exactly as | written, | or you can copy the code directly from this message | and | paste it into the Code window. | | Regards, | | Jan Karel Pieterse | Excel TA/MVP | | -----Original Message----- | I am wanting to count dates in a range, but by colour. | Ie, all red dates to give a total number, all blue dates | to give a total number. | | Can anyone help?? | | Debbie. | . | |
| 653 | Hello All, I've been working with charts that generate into userforms and I'm having alot of fun with it (can you tell that I'm in finance...). Anyway, I've been using John Walkenbach's fabulous code from the "Power Programming with VBA". It's working really well right now, but what I'd like to do is have more than one dataobject when I generate the graph. I'll show you what I have so far... Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) UserRow = ActiveCell.Row If UserRow < 2 Or IsEmpty(Cells(UserRow, 1)) Then MsgBox "Move the cell cursor to a row that contains data." Exit Sub End If CreateChart (UserRow) UserForm1.Show End Sub Sub CreateChart(r) Dim Charttitle1 As String Dim ChartTitle2 As String Dim Charttitle3 As String Dim Tempchar As Chart Application.ScreenUpdating = False Set CatTitles = ActiveSheet.Range("E2:AZ2") Set ScrRange = ActiveSheet.Range(Cells(r, 5), Cells(r, 52)) Charttitle1 = Cells(r, 2) ChartTitle2 = Cells(r, 3) Charttitle3 = Cells(r, 1) Set SourceData = Union(CatTitles, ScrRange) Set tempchart = Charts.Add With tempchart .ChartType = xlLineMarkers .SetSourceData Source:=SourceData, PlotBy:=xlRows .HasLegend = False .ApplyDataLabels Type:=xlDataLabelsShowNone, LegendKey:=False .HasTitle = True .Charttitle.Text = "Transit: " & Charttitle3 & " " & Charttitle1 & "-" & ChartTitle2 .Charttitle.Font.Bold = True .Charttitle.Font.Size = 12 '.Axes(xlValue).MaximumScale = 30000 .Axes(xlCategory).TickLabels.Font.Size = 6 .Axes(xlCategory).TickLabels.Orientation = xlHorizontal .Location where:=xlLocationAsObject, Name:="Volumes" End With With ActiveSheet.ChartObjects(1) .Width = 600 .Height = 300 .Visible = False End With Private Sub UserForm_Initialize() Set currentchart = ActiveSheet.ChartObjects(1).Chart fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif" currentchart.Export Filename:=fname, filtername:="GIF" Sheets("volumes").ChartObjects(1).Delete Image1.Picture = LoadPicture(fname) UserForm1.Caption = "Charts 'n Stuff" Application.ScreenUpdating = True End Sub Is there any way to modify this code so that the user can select more than one row and be able to compare and contrast the data? Any help would be greatly appreciated, and a special thanks to John for the great resource book he's written, Thanks in advance, Mike |
| 667 | I have a problem that appears to be fairly widespread. I haven't, however, found a solution to my exact problem on this site... Here's the trouble. I have a system of files that I need to be able to transfer over to a different drive. I've read that as long as everything is in the same directory and the sub-directory structure and names of the sub-directories are left intact that Excel will pretend the references are relative (aka, I can move the files and they will work). Alas, it doesn't appear to be working for me. Here's the magnitude of data that I'm worried about: 26 directories, 25 of which are designed to collect data from different users (the whole reason this all isn't just one file anymore), and 1 directory to compile this information (and also serve as a source of a single piece of information for every file in the other directories). In each of the 25 directories, I will have 9 or 36 (undecided) files that I need to reference. The collection folder contains two files - fetcher (which gets the data for me) and sesdata (which the input files need to reference). The collection folder is named "master", the other inputs are all numbered. All these directories are in the directory "qpa." I try to move the directory containing both master and the input folders (qpa), and all my links which, if relative, would read "../master/sesdata" break. I would like to be able to design this system on my pc, record it to a CD, then copy it onto different computers. Due to the number of links, the edit links option simply won't work. I am willing to move the directory structure (to some extent), try VBA (throw code at me, I don't do Basic), or whatever. Or if I need to try to use Quattro Pro, tell me. Any help is greatly appreciated. |
| 683 | Tom - Did you use the Save As HTML command to make your GIFs? Make sure you have the latest version of the Internet Assistant? I think I remember reading that the earlier version that shipped with Office 97 didn't work as well as later versions. According to MSKB article Q158079 at /default.aspx?scid=kb;en-us;Q158079 "To obtain the latest version of the Internet Assistant Wizard, go to the following Microsoft Web site: /downloads/9798/html.aspx" Did you try the chart.export method in VBA? Did this give better results? - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <ewtdjZ#GCHA.2056@tkmsftngp12, Tom Moers said... Running XL97 in WinXP, charts saved as .gif file is truncated (right portion is missing). These charts were created in XL7.0 in Win95 and saved as .gif files work perfectly in that envorinment. I have just completed running sr2 to update XL97 and that didn't fix the problem. Any thoughts would be appreciated. Tom |
| 700 | I have a problem that appears to be fairly widespread. I haven't, however, found a solution to my exact problem on this site... Here's the trouble. I have a system of files that I need to be able to transfer over to a different drive. I've read that as long as everything is in the same directory and the sub-directory structure and names of the sub-directories are left intact that Excel will pretend the references are relative (aka, I can move the files and they will work). Alas, it doesn't appear to be working for me. Here's the magnitude of data that I'm worried about: 26 directories, 25 of which are designed to collect data from different users (the whole reason this all isn't just one file anymore), and 1 directory to compile this information (and also serve as a source of a single piece of information for every file in the other directories). In each of the 25 directories, I will have 9 or 36 (undecided) files that I need to reference. The collection folder contains two files - fetcher (which gets the data for me) and sesdata (which the input files need to reference). The collection folder is named "master", the other inputs are all numbered. All these directories are in the directory "qpa." I try to move the directory containing both master and the input folders (qpa), and all my links which, if relative, would read "../master/sesdata" break. I would like to be able to design this system on my pc, record it to a CD, then copy it onto different computers. Due to the number of links, the edit links option simply won't work. I am willing to move the directory structure (to some extent), try VBA (throw code at me, I don't do Basic), or whatever. Or if I need to try to use Quattro Pro, tell me. |
| 701 | On Tue, 25 Jun 2002 02:00:07 GMT, Knowledge Seeker <AbsolutelyNoSpamAccepted@earthlink.net wrote: I have a spreadsheet with about 1000 rows that has a column for name. The following are sample names: J. K. Rowling George R. R. Martin Robert J. Sawyer Ken MacLeod Lois McMaster Bujold Walter M. Miller, Jr. C. J. Cherryh Michael P. Kube-McDowell Jerry Pournelle and Larry Niven I need a formula that will take the name and create two new columns. The first new column will contain the last name and the second new column will contain the rest of the name. Can you help me?? -- Knowledge Seeker To reply to me directly, please replace the garbage before the @ sign with the name m-e-s-s-a-g-e-r-e-a-d-e-r (without the dashes) I wrote 2 horrendously complicated VBA functions (100+ lines) to do something very similar, splitting names into First, Mid and Last names columns. I also parsed Dutch style surnames (van der Valk, de Zeuuw) with the prefix as the Middle name, while with French names (Le Brun) all parts are treated as surnames. Of course then, the problem is that both French and Dutch names use 'De' as a surname prefix... Vasant Nanavati wrote in another reply "It's almost impossible to write a parser that will take all possible variations into account." Too right!!! The best you can do is a good guess, based on knowledge of the data you've got. My technique involved splitting the name on whitespace into Collection objects (lists), and then doing various horrendous manipulations with these. It is about 99% successful (on *my* data...) I'll post it if you're interested, but I'd need to clean up work-specific data first. I think that a *better* way to do this would involve regular-expressions, which are a very powerful way of manipulating text. They are already in VBA for Office 2000 (need to set a reference to Windows Scripting Regular Expressions) or something like that. For Office 97, you need to download the Scripting extension from Microsoft's website. (If this is just for personal use, and doesn't require MS-only extensions, you could download Perl from - this contains a PerlScript .dll that you can embed in Excel, and ship out Regular Expression work to that object. Maybe a bit tricky, but very powerful). -------- osfameron /chickenman/ |
| 703 | Hi drwinterton I am sure someone will come along with a possible answer for you. I just wanted to see if your area aware of the EditLinksChange source option available in Linked Workbooks. -- "drwinterton" <daniel_winterton@hk.ml.com wrote in message news:1223601c21c1a$285a9e30$3aef2ecf@TKMSFTNGXA09... | I have a problem that appears to be fairly widespread. I | haven't, however, found a solution to my exact problem on | this site... | | Here's the trouble. I have a system of files that I need | to be able to transfer over to a different drive. | | I've read that as long as everything is in the same | directory and the sub-directory structure and names of the | sub-directories are left intact that Excel will pretend | the references are relative (aka, I can move the files and | they will work). Alas, it doesn't appear to be working | for me. | | Here's the magnitude of data that I'm worried about: 26 | directories, 25 of which are designed to collect data from | different users (the whole reason this all isn't just one | file anymore), and 1 directory to compile this information | (and also serve as a source of a single piece of | information for every file in the other directories). In | each of the 25 directories, I will have 9 or 36 | (undecided) files that I need to reference. | | The collection folder contains two files - fetcher (which | gets the data for me) and sesdata (which the input files | need to reference). The collection folder is | named "master", the other inputs are all numbered. All | these directories are in the directory "qpa." | | I try to move the directory containing both master and the | input folders (qpa), and all my links which, if relative, | would read "../master/sesdata" break. I would like to be | able to design this system on my pc, record it to a CD, | then copy it onto different computers. Due to the number | of links, the edit links option simply won't work. I am | willing to move the directory structure (to some extent), | try VBA (throw code at me, I don't do Basic), or whatever. | Or if I need to try to use Quattro Pro, tell me. | | |
| 709 | Hi Jason I am sure Tom will be along soon to help you, but in the mean time you might find these links of use: /VBA/VBACode.htm /VBA/SpeedingUpVBACode.htm /VBA/VBALoops.htm Hope they help -- "ims" <kft_icq@myrealbox.com wrote in message news:#jjYydCHCHA.368@tkmsftngp13... | Dear Tom | | Hope you remember me whom you helped to write the macro below. | This really helpful to my job, however when I apply it to an excel worksheet | of 15 columns, 40 thousand records I find it has to work for several hours, | which is impractical. | I don't really know which part of the marco waste most of the time. | So I write here and want to know if the marco can be trimmed so as to make | it run more faster. | Your help is very much appreciated! | | Best regards | | Jason | | | | ----- Original Message ----- | From: "Tom Ogilvy" <twogilvy@msn.com | Newsgroups: microsoft.public.excel.misc | Sent: Monday, May 13, 2002 5:46 AM | Subject: Re: How to handle worksheet and save it as xls.file? | | | That revision was based on an earlier, slightly different version of the | original - it had hard coded paths for the file operations. This has the | sPath variable (like the original) to make it easier to specify the | location | for the new files: | | Sub StartSplitout() | Dim sh As Worksheet | Dim sh1 As Worksheet | Dim varr As Variant | Dim rng As Range | Set rng = Range(Cells(1, 1), Cells(1, "IV").End(xlToLeft)) | varr = rng.Value | Cells(1, 1).EntireRow.Delete | ActiveSheet.Copy | Set sh = ActiveSheet | Splitout sh, varr | ThisWorkbook.Activate | Cells(1, 1).EntireRow.Insert | Cells(1, 1).Resize(, UBound(varr, 2)).Value = varr | End Sub | | Sub Splitout(sh As Worksheet, varr As Variant) | Dim bContinue As Boolean | Dim rng As Range, Cell As Range | Dim rw As Long, sh1 As Worksheet | Dim sPath As String | sPath = "C:\Data\" | bContinue = False | Set rng = sh.Range(sh.Cells(1, 1), _ | sh.Cells(1, 1).End(xlDown)) | For Each Cell In rng | If Cell.Row < 1 Then | If Cell.Value < _ | Cell.Offset(-1, 0).Value Then | bContinue = True | rw = Cell.Row | Exit For | End If | End If | Next | If bContinue Then | sh.Copy | Set sh1 = ActiveSheet | sh1.Range(sh1.Cells(1, 1), sh1.Cells(rw - 1, 1)). _ | EntireRow.Delete | sh.Range(sh.Cells(rw, 1), _ | sh.Cells(Rows.Count, 1).End(xlUp)) _ | .EntireRow.Delete | sh.Cells(1, 1).EntireRow.Insert | sh.Cells(1, 1).Resize(, UBound(varr, 2)).Value = varr | On Error Resume Next | Kill sPath & sh.Cells(2, 1).Value & _ | ".xls" | On Error GoTo 0 | sh.Parent.SaveAs sPath & _ | sh.Cells(2, 1).Value & ".xls" | sh.Parent.Close SaveChanges:=False | Splitout sh1, varr | Else | On Error Resume Next | Kill sPath & sh.Cells(2, 1).Value & _ | ".xls" | On Error GoTo 0 | sh.Cells(1, 1).EntireRow.Insert | sh.Cells(1, 1).Resize(, UBound(varr, 2)).Value = varr | sh.Parent.SaveAs sPath & _ | sh.Cells(2, 1).Value & ".xls" | sh.Parent.Close SaveChanges:=False | End If | End Sub | | Regards, | Tom Ogilvy | | | | |
| 713 | Knowledge Seeker <AbsolutelyNoSpamAccepted@earthlink.net wrote in message news:<65jfhu4cnmc8kf1m14rij4lhmhs48m5303@4ax.com... I have a spreadsheet with about 1000 rows that has a column for name. The following are sample names: J. K. Rowling George R. R. Martin Robert J. Sawyer Ken MacLeod Lois McMaster Bujold Walter M. Miller, Jr. C. J. Cherryh Michael P. Kube-McDowell Jerry Pournelle and Larry Niven I need a formula that will take the name and create two new columns. The first new column will contain the last name and the second new column will contain the rest of the name. Can you help me?? As per my last post, here's my sample code: it doesn't do *exactly* what you want, but splits a full-name into First, Mid and Last names. You might be able to modify to your purposes. The meat is the F_M_L_from_F function. Then there are some helper functions, and a test subroutine to show an example. (BTW, if you are using Excel2000, you might not need to define your own Split function, but it doesn't exist in Excel97). This is 268 lines of convoluted horror, and should not be taken as an ideal of programming style... I'm definitely going to refactor/redesign this code at some point, but it might give you some sort of ideas... --------------------- Option Explicit Function F_M_L_from_F(fullname As String) As Collection ' ' Takes a string name, and returns a Collection of ' 3 items (First, Middle, Last) ' ' Horribly complicated because it has to be able to ' deal with quite a lot of complexity ' ' John Brown John Brown ' John M Brown John M Brown ' Hans van Straaten Hans van Straaten ' Hans-van Straaten Hans van Straaten ' Hans van-Straaten Hans van Straaten ' Anne Le Duc Anne Le Duc ' etc. ' (Having said that, it could probably do with being ' rewritten, possibly using something a bit more scalable ' and powerful, like regexes) ' Split the full name into a list (on spaces) Dim names As Collection Set names = split(" ", fullname) ' Create strings for first, mid, last, and various ' temporary variables ' (did I mention that this is a horrible Function) Dim First As String, Mid As String, Last As String Dim newfirst As String Dim mylast As String, myname As String ' Create lists for Middle and Last names Dim mids As New Collection Dim lasts As New Collection ' shift first name onto var 'first' If names.Count 0 Then First = names(1) names.Remove (1) ' Check firstnames for hyphenated suffixes Dim firstnames As New Collection Set firstnames = split("-", First) If firstnames.Count 1 Then newfirst = firstnames(1) firstnames.Remove (1) ' Capitalise first name properly If newfirst Like "[a-z]*" Then newfirst = TCase(newfirst) Dim nextfirst As String nextfirst = firstnames(1) ' If the next 'first name' looks like a surname ' prefix then push it onto the surnames list If nextfirst Like "[a-z]*" _ Or nextfirst Like "Von" Or nextfirst Like "Van" _ Or nextfirst Like "De" Or nextfirst Like "Der" _ Or nextfirst Like "Le" Or nextfirst Like "La" _ Then names.Add join(" ", firstnames), , names.Count First = newfirst Else ' Or, if it is like a middle initial (or two) ' the push explicitly onto the Middle Initials ' list If nextfirst Like "[A-Z]" Or nextfirst Like "[A-Z][A-Z]" Then mids.Add nextfirst firstnames.Remove (1) names.Add join(" ", firstnames), , names.Count First = newfirst End If End If Else: If First Like "[a-z]*" Then First = TCase(First) End If ' Split into Mid & Last Dim is_mid As Boolean is_mid = True While names.Count 0 myname = names(1) names.Remove (1) If is_mid Then ' Some names are added to Middle names, and ' others to Last names. le and la are prepended ' to surnames, while Von, Van, De, Der etc. ' are treated as middle names. If myname Like "[A-Z]" Or myname Like "[A-Z][A-Z]" _ Or (myname Like "[a-z]*" _ And myname < "le" _ And myname < "la") _ Or myname Like "Von" Or myname Like "Van" _ Or myname = "De" Or myname = "Der" Then If myname Like "[a-z]" Then myname = UCase(myname) mids.Add myname Else is_mid = False lasts.Add myname End If Else lasts.Add myname End If Wend ' If we've been too enthusiastic, and not supplied a ' lastname... then get one, either from middle name or ' first name If lasts.Count = 0 Then If mids.Count 0 Then lasts.Add mids(mids.Count) lasts.Add (mylast) mids.Remove (mids.Count) Else lasts.Add (First) First = "" End If End If ' Check last names for hyphenated prefixes Dim newlast As New Collection Dim lastnames As New Collection Set lastnames = split("-", lasts(1)) If lastnames.Count 1 Then lasts.Remove (1) Dim in_prefix As Boolean in_prefix = True While (lastnames.Count 0) mylast = lastnames(1) lastnames.Remove (1) ' If the last name was actually a Middle name ' then push it back onto the middle names If in_prefix = True And (mylast Like "[A-Z]" _ Or mylast Like "[A-Z][A-Z]" _ Or mylast Like "Von" Or mylast Like "Van" _ Or mylast Like "De" Or mylast Like "Der" _ Or mylast Like "von" Or mylast Like "van" _ Or mylast Like "de" Or mylast Like "der") Then mids.Add mylast Else in_prefix = False If mylast Like "[a-z]" Then mylast = TCase(mylast) newlast.Add mylast End If Wend If Not newlast Is Nothing Then Dim pref_last As String Dim mytoken As Variant Dim mytoken1 As String pref_last = join("-", map_TCase(newlast)) If lasts.Count 0 Then lasts.Add pref_last, , 1 Else lasts.Add pref_last End If End If End If ' Generate the complete First, Mid, and Last names ' from the lists we've created Mid = join(" ", mids) Last = join(" ", map_TCase(lasts)) ' Create a new list to put them in Dim res As New Collection res.Add First res.Add Mid res.Add Last ' The return value will be this list Set F_M_L_from_F = res Else ' return a list with 3 empty values... names.Add "" names.Add "" names.Add "" Set F_M_L_from_F = names End If End Function Function join(jstring As String, coll As Collection) As String ' a helper sub. Returns the list supplied interspersed with ' the join string. ' e.g. ' join("-", "Hello", "World", 1,2,3) - Hello-World-1-2-3 Dim token As Variant For Each token In coll If join < "" And token < "" Then join = join & jstring join = join & token Next End Function Function split(split_string As String, orig_string As String) As Collection ' Helper function to return a list from a string ' e.g. ' split(" ", "I like banana splits") ' - "I", "like", "banana", "splits" ' preare the new list Dim coll As New Collection Dim pos pos = 1 Dim token As String Dim my_string As String my_string = orig_string 'copied so as not to modify the original string ' As long as there's some string left to scan, look for the ' split value in it, then add everything to the left of it ' (using the Left operator) to our new list. ' Then Redefine the string as the bit to the right of that ' (using the Mid operator) While my_string < "" And pos < 0 pos = InStr(1, my_string, split_string) If pos 0 Then token = Left(my_string, pos - 1) If token < "" Then coll.Add token my_string = Mid(my_string, pos + Len(split_string)) End If Wend ' By default, only add the string if it's not blank. ' this means that multiple delimiters don't create ' multiple fields. ' e.g. ' split("-", "Hello-----World") ' - "Hello", "World" ' ' This might not be what you want ("Hello","","","","","World") ' but it's the correct default for this particular application. If my_string < "" Then coll.Add my_string Set split = coll End Function Function TCase(orig As String) As String ' Set the string to title case ' e.g. Debug.Print TCase("fRED") - Fred ' ' (Have a feeling that there might be a VBA function ' to do this. If so, then d'oh!) TCase = UCase(Left(orig, 1)) & LCase(Mid(orig, 2)) End Function Function map_TCase(coll As Collection) As Collection ' Set a whole list to Title Case Dim coll2 As New Collection Dim toke As Variant Dim toke2 As String For Each toke In coll ' This kludge with toke2 doesn't always seem ' to be necessary? Very odd! toke2 = toke ' Only modify it if it's all lower or all upper. ' (e.g. we assume that if it already has sOmE capitalization ' then it must be deliberate... e.g. we don't want to flatten ' McCarthy, JoAnn, or other capitalizations from other languages.) If toke2 = LCase(toke2) Or toke = UCase(toke2) Then toke2 = TCase(toke2) coll2.Add toke2 Next Set map_TCase = coll2 End Function Sub test() Dim x As Collection Set x = F_M_L_from_F("willem van der valk") Debug.Print "First: " & x(1) Debug.Print "Mid: " & x(2) Debug.Print "Last: " & x(3) ' Result ' ------ ' First: Willem ' Mid: van der ' Last: Valk End Sub --------------------- Cheerio, osfameron/hakim /chickenman/ |
| 714 | Hi, Some questions if I may. - How did you copy your files? - Did you copy the complete tree with the files? - what is a correct link and what does it look like after copying you files (and opening the master copy) I guess simplest should be to simply copy the entire tree to another drive. But if the links travel with that also depends on their exact type. If they are direct cell to cell links, AFAIK they should adjust to the new location. But if any part of them are hardcoded (like in the INDIRECT function), well... But I guess you already knew that <g. Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- I have a problem that appears to be fairly widespread. I haven't, however, found a solution to my exact problem on this site... Here's the trouble. I have a system of files that I need to be able to transfer over to a different drive. I've read that as long as everything is in the same directory and the sub-directory structure and names of the sub-directories are left intact that Excel will pretend the references are relative (aka, I can move the files and they will work). Alas, it doesn't appear to be working for me. Here's the magnitude of data that I'm worried about: 26 directories, 25 of which are designed to collect data from different users (the whole reason this all isn't just one file anymore), and 1 directory to compile this information (and also serve as a source of a single piece of information for every file in the other directories). In each of the 25 directories, I will have 9 or 36 (undecided) files that I need to reference. The collection folder contains two files - fetcher (which gets the data for me) and sesdata (which the input files need to reference). The collection folder is named "master", the other inputs are all numbered. All these directories are in the directory "qpa." I try to move the directory containing both master and the input folders (qpa), and all my links which, if relative, would read "../master/sesdata" break. I would like to be able to design this system on my pc, record it to a CD, then copy it onto different computers. Due to the number of links, the edit links option simply won't work. I am willing to move the directory structure (to some extent), try VBA (throw code at me, I don't do Basic), or whatever. Or if I need to try to use Quattro Pro, tell me. . |
| 717 | Don't think INDIRECT works for closed Workbooks <bg -- "Jan Karel Pieterse" <pieterse_home@compuserve.com wrote in message news:1005401c21c35$215b2120$9ee62ecf@tkmsftngxa05... | Hi, | | Some questions if I may. | | - How did you copy your files? | - Did you copy the complete tree with the files? | - what is a correct link and what does it look like after | copying you files (and opening the master copy) | | I guess simplest should be to simply copy the entire tree | to another drive. But if the links travel with that also | depends on their exact type. If they are direct cell to | cell links, AFAIK they should adjust to the new location. | | But if any part of them are hardcoded (like in the | INDIRECT function), well... But I guess you already knew | that <g. | | | Regards, | | Jan Karel Pieterse | Excel TA/MVP | | -----Original Message----- | I have a problem that appears to be fairly widespread. I | haven't, however, found a solution to my exact problem on | this site... | | Here's the trouble. I have a system of files that I need | to be able to transfer over to a different drive. | | I've read that as long as everything is in the same | directory and the sub-directory structure and names of | the | sub-directories are left intact that Excel will pretend | the references are relative (aka, I can move the files | and | they will work). Alas, it doesn't appear to be working | for me. | | Here's the magnitude of data that I'm worried about: 26 | directories, 25 of which are designed to collect data | from | different users (the whole reason this all isn't just one | file anymore), and 1 directory to compile this | information | (and also serve as a source of a single piece of | information for every file in the other directories). In | each of the 25 directories, I will have 9 or 36 | (undecided) files that I need to reference. | | The collection folder contains two files - fetcher (which | gets the data for me) and sesdata (which the input files | need to reference). The collection folder is | named "master", the other inputs are all numbered. All | these directories are in the directory "qpa." | | I try to move the directory containing both master and | the | input folders (qpa), and all my links which, if relative, | would read "../master/sesdata" break. I would like to be | able to design this system on my pc, record it to a CD, | then copy it onto different computers. Due to the number | of links, the edit links option simply won't work. I am | willing to move the directory structure (to some extent), | try VBA (throw code at me, I don't do Basic), or | whatever. | Or if I need to try to use Quattro Pro, tell me. | | | . | |
| 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. |
| 735 | A solution using VBA is fine, I just have a lot to learn in that area (writing them myself, I mean). The only hitch with your answer is that there are usually multiple (anywhere from 1 to 20) hidden columns throughout each worksheet (possible example: A, B, C, F, G, J, T, S, and AB are hidden). That would mean that I would have to check manually which columns are hidden and then type them each into the name box to delete them. I am looking for something a little more functional. Thanks! Laura -----Original Message----- Hi Laura I assume you mean without VBA, if so assume Column "C" is hidden, type C:C in the name box (left of the formula bar) and push enter. Now go to EditDelete. There is no doubt a shortcut key for deleting Columns, you should find it here: /Excel/ExcelKeyBoardShortcutKeys.htm -- "Laura Wilde" <lwilde@new.rr.com wrote in message news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07... | Is it possible to delete hidden columns without unhiding | them? I get many workbooks sent to me with hidden | columns that contain data I do not need. Some are | formulas but I usually "copy" and "paste values" so there | is no worry of linking errors. | | Thanks for any help you can give me. | | Laura . |
| 738 | Hi Laura: Sub DeleteHiddenRows() Dim c As Range For Each c In Rows(1).Cells If c.Width = 0 Then c.EntireColumn.Delete Next End Sub -- Regards, Vasant. **No direct emails please--keep discussion in newsgroup.** "Laura Wilde" <lwilde@new.rr.com wrote in message news:1278901c21cb4$bc02b420$3aef2ecf@TKMSFTNGXA09... A solution using VBA is fine, I just have a lot to learn in that area (writing them myself, I mean). The only hitch with your answer is that there are usually multiple (anywhere from 1 to 20) hidden columns throughout each worksheet (possible example: A, B, C, F, G, J, T, S, and AB are hidden). That would mean that I would have to check manually which columns are hidden and then type them each into the name box to delete them. I am looking for something a little more functional. Thanks! Laura -----Original Message----- Hi Laura I assume you mean without VBA, if so assume Column "C" is hidden, type C:C in the name box (left of the formula bar) and push enter. Now go to EditDelete. There is no doubt a shortcut key for deleting Columns, you should find it here: /Excel/ExcelKeyBoardShortcutKeys.htm -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Laura Wilde" <lwilde@new.rr.com wrote in message news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07... | Is it possible to delete hidden columns without unhiding | them? I get many workbooks sent to me with hidden | columns that contain data I do not need. Some are | formulas but I usually "copy" and "paste values" so there | is no worry of linking errors. | | Thanks for any help you can give me. | | Laura . |
| 739 | Hi Laura Push Alt+F11, then go InsertModule and paste in the code below: Sub DeleteHiddenCols() Dim rCell As Range 'Runs on the active sheet Application.Calculation = xlCalculationManual For Each rCell In Rows(1) If rCell.EntireColumn.Hidden = True Then rCell.EntireColumn.Delete End If Next rCell Application.Calculation = xlCalculationAutomatic End Sub Psuh Alt+Q, then Alt+F8 click "DeleteHiddenCols" then Options and asign a shortcut key. -- Hope this helps. "Laura Wilde" <lwilde@new.rr.com wrote in message news:1278901c21cb4$bc02b420$3aef2ecf@TKMSFTNGXA09... | A solution using VBA is fine, I just have a lot to learn | in that area (writing them myself, I mean). | | The only hitch with your answer is that there are usually | multiple (anywhere from 1 to 20) hidden columns | throughout each worksheet (possible example: A, B, C, F, | G, J, T, S, and AB are hidden). That would mean that I | would have to check manually which columns are hidden and | then type them each into the name box to delete them. I | am looking for something a little more functional. | | Thanks! | | Laura | | | -----Original Message----- | Hi Laura | | | I assume you mean without VBA, if so assume Column "C" | is hidden, type C:C | in the name box (left of the formula bar) and push | enter. Now go to | EditDelete. There is no doubt a shortcut key for | deleting Columns, you | should find it here: | /Excel/ExcelKeyBoardShortcutKeys.htm | | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | If it's Excel, then it's us! | | | "Laura Wilde" <lwilde@new.rr.com wrote in message | news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07... | | Is it possible to delete hidden columns without | unhiding | | them? I get many workbooks sent to me with hidden | | columns that contain data I do not need. Some are | | formulas but I usually "copy" and "paste values" so | there | | is no worry of linking errors. | | | | Thanks for any help you can give me. | | | | Laura | | | . | |
| 741 | As long as your careful with your formulas/values: Option Explicit Sub delhiddencols() Dim iCol As Long Dim deleteme As Range For iCol = 1 To 256 If Columns(iCol).Hidden = True Then If deleteme Is Nothing Then Set deleteme = Cells(1, iCol) Else Set deleteme = Union(Cells(1, iCol), deleteme) End If End If Next iCol If deleteme Is Nothing Then 'can't do anything Else deleteme.EntireColumn.Delete End If End Sub You may want to add some checks for protection/filters, etc. Things that will stop you from deleting columns. Maybe even a little check to ask "are you sure"? Laura Wilde wrote: A solution using VBA is fine, I just have a lot to learn in that area (writing them myself, I mean). The only hitch with your answer is that there are usually multiple (anywhere from 1 to 20) hidden columns throughout each worksheet (possible example: A, B, C, F, G, J, T, S, and AB are hidden). That would mean that I would have to check manually which columns are hidden and then type them each into the name box to delete them. I am looking for something a little more functional. Thanks! Laura -----Original Message----- Hi Laura I assume you mean without VBA, if so assume Column "C" is hidden, type C:C in the name box (left of the formula bar) and push enter. Now go to EditDelete. There is no doubt a shortcut key for deleting Columns, you should find it here: /Excel/ExcelKeyBoardShortcutKeys.htm -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Laura Wilde" <lwilde@new.rr.com wrote in message news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07... | Is it possible to delete hidden columns without unhiding | them? I get many workbooks sent to me with hidden | columns that contain data I do not need. Some are | formulas but I usually "copy" and "paste values" so there | is no worry of linking errors. | | Thanks for any help you can give me. | | Laura . -- Dave Peterson ec35720@msn.com |
| 743 | Thanks everyone...all work perfectly! -----Original Message----- A solution using VBA is fine, I just have a lot to learn in that area (writing them myself, I mean). The only hitch with your answer is that there are usually multiple (anywhere from 1 to 20) hidden columns throughout each worksheet (possible example: A, B, C, F, G, J, T, S, and AB are hidden). That would mean that I would have to check manually which columns are hidden and then type them each into the name box to delete them. I am looking for something a little more functional. Thanks! Laura -----Original Message----- Hi Laura I assume you mean without VBA, if so assume Column "C" is hidden, type C:C in the name box (left of the formula bar) and push enter. Now go to EditDelete. There is no doubt a shortcut key for deleting Columns, you should find it here: /Excel/ExcelKeyBoardShortcutKeys.ht m -- "Laura Wilde" <lwilde@new.rr.com wrote in message news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07... | Is it possible to delete hidden columns without unhiding | them? I get many workbooks sent to me with hidden | columns that contain data I do not need. Some are | formulas but I usually "copy" and "paste values" so there | is no worry of linking errors. | | Thanks for any help you can give me. | | Laura . . |
| 751 | Since XL stores times as fractional days, you can convert minutes to a time by dividing by 24*60, or 1440: A1: 80 A2: =A1/1440 === 1:20 when A2 is formatted as a time. In article <1273e01c21cc9$52668590$19ef2ecf@tkmsftngxa01, Bob <vbats2000@yahoo.com wrote: Here is my dilema I have a simple formula to find minutes and the answer comes out to 80 minutes. In the long run i need that 80 minutes converted to 1h 20 min in any format i would be happy with 1.2 but what i am getting is a percentage such as 1.4. Im sorry if this sounds confusing. What I am doing is ex.=sum(e3/60) where e3 was equal to lets say 80 and it would give me 1.4 instead of what i was looking for in 1.2. Does anyone know the correct formula please. Bob |
| 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 |
| 796 | I am trying to execute a file search with the exact file name specified. The problem is when the exact file does not exist, it still returns file names that are similar, as if there were a wild card specified in the name. I have tried setting .MatchTextExactly to true, but it does not change the result. Is this just a VBA glitch? The following code is what I am using: Set fs = Application.FileSearch With fs .NewSearch 'String containing the path to the file. .LookIn = FilePath .SearchSubFolders = True 'String containing the file name such as "APT.txt" .Filename = UserFileName .MatchTextExactly = True If .Execute 0 Then For I = 1 To .FoundFiles.Count MsgBox .FoundFiles(I) Next I End If End With |
| 797 | I do not understand why the tendency is for suggestions to convert character 160 to nothing. CHR(160) is the non breaking space character ( ) in HTML and should be converted to a normal space. FWIW the CLEAN Worksheet Function is also sometimes suggested and would only affect char(0) through Char(31), Char(129), Char(141), char(143) and Char(144). To fix up unwanted extra spaces afterwards there is TRIM to remove spaces left and right; and replace can be used to replace 2 spaces with 1 space depending on your data. There is a difference between TRIM in Excel and in VBA. Excel will reduce internal spaces, VBA will not. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "George" <george39672002@yahoo.com wrote in message news:1146201c21ab4 I tried the character 160 and wasn't getting any results, so I thought it was my computer and will troubleshoot that over next little while. It is useful info to know of the character 160. The transfer of data into the excel sheet and the sort function works well. Thanks to both of you. |
| 805 | I posted this through google, but it seemed to crash. So I thought I'd use CDO: I _think_ I understand: I dropped a button from the forms toolbar onto a worksheet. I have 3 examples of the .onaction stuff. Comment out two of them. Option Explicit Sub testme() Dim cb As Button Set cb = ActiveSheet.Buttons(1) Dim c As Range Set c = Range("A1") c.Value = "rangeA1value" Dim i As Long i = 8 With cb .OnAction = ThisWorkbook.Name _ & "!'testme1 " & Chr(34) & c.Value & Chr(34) & "'" .OnAction = ThisWorkbook.Name _ & "!'testme2 ""Hello"", 3'" .OnAction = Workbooks("book2.xls").Name _ & "!'testme3 " & Chr(34) _ & "different wkbk" & Chr(34) & "'" End With End Sub Sub testme1(myval As Variant) MsgBox "From Testme1: " & myval End Sub Sub testme2(mystr As String, myval As Long) MsgBox "From Testme2: " & mystr & " " & myval * 10 End Sub and from Book2.xls Option Explicit Sub testme3(mystr As String) MsgBox "From " & ThisWorkbook.Name & ": " & mystr End Sub I've never used the CDO before. Watch out for linewrap problems. -----Original Message----- 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 . |
| 836 | Hi Dave, I think I may have made it unclear what I am trying achieve. I would like any old Excel developer to call an existing macro in an excel add-in I have written, passing parameters to it without the need to create a Macro themselves. The way I perceived it would work is by the developer dragging a button from the 'Forms' toolbar (which I wrongly called a control) and then right-button mouse clicking on the button and selecting the 'Assign Macro...' dialog, and from there just typing in something like: 'MyAddin.xla'!'MyMacro "param1","param2"' without worrying about any VBA code. Sorry I confused things with the example I gave.. Thanks again, Steve "Dave Peterson" <ec35720@msn.com wrote in message news:106f001c21d1d$b90eb0c0$9ee62ecf@tkmsftngxa05... I posted this through google, but it seemed to crash. So I thought I'd use CDO: I _think_ I understand: I dropped a button from the forms toolbar onto a worksheet. I have 3 examples of the .onaction stuff. Comment out two of them. Option Explicit Sub testme() Dim cb As Button Set cb = ActiveSheet.Buttons(1) Dim c As Range Set c = Range("A1") c.Value = "rangeA1value" Dim i As Long i = 8 With cb .OnAction = ThisWorkbook.Name _ & "!'testme1 " & Chr(34) & c.Value & Chr(34) & "'" .OnAction = ThisWorkbook.Name _ & "!'testme2 ""Hello"", 3'" .OnAction = Workbooks("book2.xls").Name _ & "!'testme3 " & Chr(34) _ & "different wkbk" & Chr(34) & "'" End With End Sub Sub testme1(myval As Variant) MsgBox "From Testme1 |