add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
| Article | Body |
| 12 | Put this code in the workbook where the lists will be created. It will add Sheets as it needs them. Select multiple files in the file open dialog It will prompt for the number of addresses per sheet. Sub GetData() Dim flist As Variant Dim i As Long, j As Long Dim rw As Long, shCnt As Long Dim FileNo As Long Dim sh As Worksheet Dim sName As String Dim sLine As String Dim NumAddresses As Variant Dim MailList() as String ChDrive "C" ChDir "C:\Data" flist = Application.GetOpenFilename( _ FileFilter:="Text Files (*.txt),*.txt", _ MultiSelect:=True) If TypeName(flist) = "Boolean" Then Exit Sub Do NumAddresses = InputBox("Enter the number of Addresses" _ & " per sheet") Loop Until IsNumeric(NumAddresses) And Len(Trim(NumAddresses)) 0 ReDim MailList(1 To NumAddresses, 1 To 1) Set sh = ThisWorkbook.Worksheets.Add( _ After:=ThisWorkbook.Worksheets( _ ThisWorkbook.Worksheets.Count)) sName = "Data" & 1 On Error Resume Next Application.DisplayAlerts = False Worksheets(sName).Delete Application.DisplayAlerts = True On Error GoTo 0 sh.Name = sName shCnt = 1 rw = 1 For i = LBound(flist) To UBound(flist) FileNo = FreeFile() Open flist(i) For Input As #FileNo j = 0 Do While Not EOF(FileNo) Line Input #FileNo, sLine j = j + 1 MailList(rw, 1) = Trim(sLine) rw = rw + 1 If rw NumAddresses Then sh.Cells(1, 1).Resize(NumAddresses, 1).Value _ = MailList ReDim MailList(1 To NumAddresses, 1 To 1) rw = 1 Set sh = ThisWorkbook.Worksheets.Add( _ After:=ThisWorkbook.Worksheets( _ ThisWorkbook.Worksheets.Count)) sName = "Data" & shCnt + 1 On Error Resume Next Application.DisplayAlerts = False Worksheets(sName).Delete Application.DisplayAlerts = True On Error GoTo 0 sh.Name = sName shCnt = shCnt + 1 End If Loop Debug.Print flist(i), j Next sh.Cells(1, 1).Resize(NumAddresses, 1).Value _ = MailList ReDim MailList(1 To NumAddresses, 1 To 1) End Sub Richard <richard@clarknet.com wrote in message news:d65101c214a2$d20278b0$37ef2ecf@TKMSFTNGXA13... Here's the deal: I have successfully created an Excel 2000 macro that imports a large amount of text into a worksheet. The file being imported is so large that I have to import the remainder into a second sheet. The content is email addresses only so only one column is used. I have many such files and need to import a lot of them. I would like help or the code to accomplish a simple task: 1. Create an automated means for importing the large text file specifying increments of 2500, 5000, 10000, and 20000 at a time with a new sheet being created based on the chosen number to extract. I want it to run until the end of the first file and automatically go to the next file. I'm not a VB programmer so I'm looking for either the scripting already done (a few modifications are ok) or very detailed and specific instructions on how to do it. Thanks. |
| 16 | My data validation uses as criteria, a list of values stored in cells off to the right of my regular data in the worksheet. This is inconvenient as somebody may inadvertantly delete certain rows that contain data. When I try to copy these cells to another hidden worksheet in the workbook, I get an error. Is there a way to use cells referenced to a list in another worksheet function proeprly with data validation? Thanks, Greg. |
| 23 | You can either name the list on the other sheet, insertnamedefine, select the range, type a name and use it in the list source box as =name, or you could put the list directly in the list box a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,x,y for example - Regards, Peo Sjoblom "GZ" <gz@yahoo.com wrote in message news:3D0BCF90.EDA3AFB3@yahoo.com... My data validation uses as criteria, a list of values stored in cells off to the right of my regular data in the worksheet. This is inconvenient as somebody may inadvertantly delete certain rows that contain data. When I try to copy these cells to another hidden worksheet in the workbook, I get an error. Is there a way to use cells referenced to a list in another worksheet function proeprly with data validation? Thanks, Greg. |
| 28 | Hi Splash I am getting a bit confused now. Why would you want a Workbook that has a toolbar always to refer to Book1.xls? This would mean that whenever a user clicked one of the menu options Book1.xls (if found) would open. Don't you want the toolbar just to run the correct code no matter what it is save as? Try this. In Book1.xls (one with the toolbar and code) right click on any menu option and select "Customize" no on the "Toolbars" page click "Attach" then select the custom toolbar and click Copy. Now come back out and Save. Now whenever you save Book1 as any name the toolbar and code will be part of the new Workbook. It also means that to make changes to your custom toolbar you will have to "Delete" it from the Workbook, make your changes then Attach it again and described above. You "Delete" it following the same instructions as for "Attaching". -- "Splash" <splash@mosquitonet.com wrote in message news:ugmv9nk9qo1j1e@corp.supernews.com... | Hej Again. I just tried Add-ins, and got the same problem I discussed | last -- the toolbar changes to refer to the workbook last saved as, and I | want the workbook to always refer to the same "Book1.xls" workbook. Help! | | "" <DavidH@OzGrid.com wrote in message | news:5sEO8.3$Z14.9212@vicpull1.telstra.net... | Hi Splash | | An Excel Add-in is simply a Workbook saved as *.xla Go to | FileSave as and select "Excel Add-in". Once saved the | Workbook will always open as very hidden. You can install the | add-in via ToolAdd-ins. | | For the option of having the code within the Workbook (normal | or add-in) open the VBE (Alt+F11), push Ctrl+R then expand | your Personal Workbook folder within the "Project Explorer", | double click the Module(s) and simply copy the code into a | Module within the other Workbook. To get a Module go to | InsertModule. | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | If it's Excel, then it's us! | | "Splash" <splash@mosquitonet.com wrote in message | news:ugm4clinj4t7c5@corp.supernews.com... | | Thanks, David. | | | | The code for the macros currently resides in the personal.xls (and I | send | | the personal.xls to the other guy). I have tried also putting the macro | | code in the actual workbook, unsuccessfully. | | | | How do you save an Excel file as an Add-in? I'm unfamiliar with that | | method, but am willing to try it. | | | | "" <DavidH@OzGrid.com wrote in message | | news:4ywO8.2$wV3.17265@vicpull1.telstra.net... | | Hi Splash | | | | You can either have the code reside in the Workbook which the toolbar | is | | attached to, or attach the toolbar to an Excel file saved as an | Add-in, | | and | | have the code reside in the Add-in. | | | | The add-in approach is more generic and flexible. | | | | | | | | -- | | | | | | | | | | FREE EXCEL NEWSLETTER | | /News/2home.htm | | Get the OzGrid Add-in | | /Services/AddinExamples.htm | | If it's Excel, then it's us! | | | | | | "Splash" <splash@mosquitonet.com wrote in message | | news:ugktip6cn72p71@corp.supernews.com... | | | I want to share a spreadsheet, with attached toolbar, by email. The | | trouble | | | is, when the other guy receives it, an error occurs when one tries | to | | use | | | the toolbar. The assigned macros are "showing" the absolute path to | the | | | personal.xls from my computer (which does not exist on the other | guy's | | | computer), and each macro has to be re-set (through | | Tools|Customize|Assign | | | Macros). | | | | | | Is there a way to avoid this problem? (I.e., can I somehow have | only | | the | | | filename, and not the absolute path for the personal.xls file appear | for | | the | | | assigned macro?) | | | | | | I hope this is clearer than the last time I asked the same question. | | | Thanks for any help, guys. | | | | | | | | | | | | | | | | | | |
| 40 | Thanks Tom, I will try that first. However I think in the near future I will build an Acess database with user friendly inputs and easy form printouts. Jeff -----Original Message----- Usually there would be a database of daily values with date and amount. Then on another sheet you can have formulas that sum up the monthly and quarterly amounts. Since you say you would refer back to the particular day, it sounds like you have a separate workbook with each days data. To do what you want would require intentional circular references, but I am not sure how you would expect the formula to know what the current quarter and month are knowing when to restart the accumulated total. Do use intentional circular references, you would have to go to the calculate tab in tools=options and check mark iteration. Change max iterations to 1. Then you can have a formula refer to itself In A1 =if(A2<0,A1+A2,0) as an example. Of couse every time the sheet is calculated, the value in A1 would be incremented by the value in A2. Jeff Mouras <mouras@bellsouth.net wrote in message news:d6fe01c21523$f2c54b50$36ef2ecf@tkmsftngxa12... Tom, I want to be able to open up my excell file, change the numbers for the day (daily cell), then print the workbook displaying: Daily, monthly and quarterly numbers. I hadn't put much thought to saving the daily numbers because I could just refer back to the particular day. Another goal of mine was to make this as simple as possible so anyone could enter the data and get the correct print out. I am open for suggestions though... Thanks, Jeff -----Original Message----- What do your formulas look like now. Are you using intentional circular references. This seems like a good way to get the wrong answer to me. Why don't you not want to store your daily numbers and then use formulas to summarize them? Regards, Tom Ogilvy Jeff Mouras <mouras@bellsouth.net wrote in message news:eb1701c21468$c5f1aee0$b1e62ecf@tkmsftngxa04... Hello, my question is: Can I build a formula that I can input data into a single cell (Daily cell) and have that data totaled up and saved in a "Monthly" and Quarterly" cell? My trouble is every time I change the daily data the Monthly and Quarterly cells do not calculate from their previous total. Thanks in advance for any help with this. Jeff Mouras . . |
| 43 | Does anyone know how to have a macro open up a locked file? Look up the workbook Open method in VBE Help: Workbooks.Open "book1.xls", , , , "password" -- Jim Rech Excel MVP |
| 46 | Thanks for the response. It took a while, but I made a macro for the problem, just had to make sure that I wasn't leaving any of my fields blank. I had to extract the data from an old program manually and delete program code that was through it. I think it was cobol. Anyway, I've attached the macro incase someone can make use of it. Sub Merging() Dim propi As String Dim i As Integer With Worksheets("Sheet1") For n = 1 To .UsedRange.Column + .UsedRange.Columns.Count For irow = 3 To .UsedRange.Row + .UsedRange.Rows.Count If Not Worksheets("Sheet1").Cells(irow, n) = "" Then propi = .Cells(irow, n) property = property & Chr$(10) & propi Else Worksheets("Sheet2").Cells(irow, n) = property property = "" End If Next irow Next n End With ActiveWorkbook.Worksheets("Sheet2").Select Selection.SpecialCells(xlCellTypeBlanks) _ .Delete Shift:=xlUp End Sub George -----Original Message----- Are you using xl as a database and using Word to produce the mailmerge. If yes, then I think it would be much easier to put each person's info on one row (in separate columns). It'll make a lot of things easier. You might want to read 's notes on mailmerge: /dmcritchie/excel/mailmerg.htm and more notes at: /word/FAQs/MailMerge/index.html George J wrote: I am trying to produce a mailmerge, but require the names and addresses to be in one cell instead of cover 4/5 rows in the column. The logic as I see it would be For usedrange 1. find the first cell in column 1 with text 2. if cell in next row has text, combine (without losing any of the data) 3. repeat until next row is blank AAAname AAAaddress AAAtown BBBname BBBaddress BBBtown BBBcounty The data above would look exactly as is, but the text would be in only 2 cells. I know it should read something like A3 & Chr(10) & A4 & chr(10) etc, but i can't figure it out. I had hoped that this had been answered before, but couldn't find anything Thanks for any input George -- Dave Peterson ec35720@msn.com . |
| 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 . |
| 72 | I am using XL2000 and Win 98. I don’t know if this problem is a Macro issue or a general Excel issue, but I start with this news group. I get an error message, “Cannot paste that macro formula into a worksheet” when ever I try to copy and paste a cell or group of cells form one workbook to another. This only happens in workbooks with macros, however the message occurs regardless of whether the copied cell is references in a macro. I can use the Paste Special, Formulas but that loses formatting. The Paste Special, Values or Paste Special, Formats always yields the error message. The details of the error say that I have caused an invalid page fault in Excel.exe. I have seen other posts in which the same error seems to prevent the file from opening, but this is not my problem. Any advice appreciated. Ray Wright -- raycyn.wright@prodigy.net |
| 94 | I need to know how to link a formula in a cell in one worksheet to a cell in another worksheet in the same workbook. So if it works the numbers and formulas will continue to add up in both worksheets as data is added. Thank you. |
| 97 | In VB, I am currently catching the WorkbookBeforePrint event of the Excel Application object. Inside this event, I am setting the LeftHeader property of each Worksheet to a cell value in the Worksheet... In all instances, the PageSetup dialog is brought up ahead of this. I have found that, depending on what is pressed on the PrintSetup dialog, the WorkbookBeforePrint event acts the same, but the PageSetup properties act differently. If Print or Preview are pressed on the PageSetup dialog, I am able to set the LeftHeader. If Ok is pressed on the PageSetup dialog, then I am unable to set the LeftHeader. No error is thrown. Its acts just as if it's locked. Here is the snippet of code I am using in the event. 120 For i = 1 To .Worksheets.Count 125 With .Worksheets(i) 130 .PageSetup.LeftHeader = "&B" & .Cells(1, 1).Value & "&B :: " & Replace(.Cells(3, 1).Value, " ", " ", 1, , vbTextCompare) Debug.Print .PageSetup.LeftHeader 'this generates a blank line when coming from OK. 135 .Cells(1, 1).Value = "" 140 .Cells(3, 1).Value = "" End With Next Any suggestions? Thank, T. Anderson |
| 98 | 2 files open first file enter = in formula bar, switch to 2nd book select cell, press enter Dave -----Original Message----- I need to know how to link a formula in a cell in one worksheet to a cell in another worksheet in the same workbook. So if it works the numbers and formulas will continue to add up in both worksheets as data is added. Thank you. . |
| 111 | I am looking for a VBA statement that will coun the # of worksheets in an excel workbook. Can you help me with this? |
| 112 | I guess I just don't understand how toolbars work. I have attached TB1 to Worksheet1 and TB2 to Worksheet2. I want it to be that TB1 only "shows" when Worksheet1 is open, and v.v. As it is, have to manually open or close TB1 or TB2 depending on which one I want to appear. (TB1 and TB2 both appear in all my workbooks.) |
| 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? |
| 115 | The pagesetup does NOT trigger the beforeprint event, so your code doesn't run. Only the Print or Printpreview will trigger the beforeprint event. Bob Umlas Excel MVP "Tyler Anderson" <tanderson@REMOVEadaytum.com wrote in message news:#UWLU0hFCHA.1740@tkmsftngp07... In VB, I am currently catching the WorkbookBeforePrint event of the Excel Application object. Inside this event, I am setting the LeftHeader property of each Worksheet to a cell value in the Worksheet... In all instances, the PageSetup dialog is brought up ahead of this. I have found that, depending on what is pressed on the PrintSetup dialog, the WorkbookBeforePrint event acts the same, but the PageSetup properties act differently. If Print or Preview are pressed on the PageSetup dialog, I am able to set the LeftHeader. If Ok is pressed on the PageSetup dialog, then I am unable to set the LeftHeader. No error is thrown. Its acts just as if it's locked. Here is the snippet of code I am using in the event. 120 For i = 1 To .Worksheets.Count 125 With .Worksheets(i) 130 .PageSetup.LeftHeader = "&B" & .Cells(1, 1).Value & "&B :: " & Replace(.Cells(3, 1).Value, " ", " ", 1, , vbTextCompare) Debug.Print .PageSetup.LeftHeader 'this generates a blank line when coming from OK. 135 .Cells(1, 1).Value = "" 140 .Cells(3, 1).Value = "" End With Next Any suggestions? Thank, T. Anderson |
| 118 | Splash, Your toolbars have become part of the family of toolbars stored with "Excel.xlb". If you want to have them only available when the file is open, then you will either need to delete the commandbar through code when the workbook is closed, using the before close event, or set the visible property to false (which you could then set to true in the open event). The prefered method is to create the commandbar each time the workbook is opened, and delete it each time the workbook is closed. Do a google newsgroup search on Deitrick Commandbar Create Delete and you will find some code to help. HTH, Bernie Splash wrote: I guess I just don't understand how toolbars work. I have attached TB1 to Worksheet1 and TB2 to Worksheet2. I want it to be that TB1 only "shows" when Worksheet1 is open, and v.v. As it is, have to manually open or close TB1 or TB2 depending on which one I want to appear. (TB1 and TB2 both appear in all my workbooks.) |
| 119 | Can someone pls tell me why this won't work?? I copied from a post here but modified it to take out an extra email address. Thank you Sub mailfast() ActiveSheet.Copy ActiveWorkbook.SendMail _ Recipient:="lisa@guildstationers.com", _ Subject:="(" & ActiveSheet.Name & ") Flyer Run List" ActiveWorkbook.Close False End Sub -- Lisa Hetherington Marketing Assistant Guild Stationers Limited/M. J. Richmond Wholesale 362 Dewitt Rd N Stoney Creek, ON L8E 2T2 (905) 662 4939 ext. 236 lisa@guildstationers.com |
| 123 | I found a file named BOOK.XLT in the /XLSTART folder. It apparently was the template that each new workbook was based on. I deleted two work sheets from it and now get a one worksheet workbook when I start a new one. Save As is a little less simple. Best I can tell, this function only saves the whole workbook. It doesn't allow for saving individual worksheets. Even the Save command does not allow for selective saves of worksheets only, it save the entire workbook. I really miss being able to manipulate individual worksheets. BrianG "Barbara wiseman" <b@nbpwiseman.fsnet.co.uk wrote in message news:<uBjf67GFCHA.1996@tkmsftngp07... Brian, Save as is in the file option on the menu, or press F12. Your 3 sheets may be in a book which is opened with every session of excel. In 2000 this is usually in a directory XLSTART which is often in windows\application data\microsoft\excel if there is a excel sheet there it will be opened every time you open an excel session. If you move it to another directory or delete it this may solve your problem. NB there is sometimes a file personal.xls there, this contains any macros saved by the user, do not delete this. Barbara BrianG <decc@dec-clt.com wrote in message news:698e0e54.0206141016.4c7060c7@posting.google.com... This is old stuff I'm sure but at work we have just been forced into using Excel97 instead of 4.0. Many of us have stuck with 4.0 because it suits our purposes and is more user friendly (read "less bells and whistles that we don't use anyways"). The problem we're running into is that we are accustomed to using worksheets. We used to open an existing worksheet, modify it, do a "save as" and have both the old and new. What's happening now is that we make changes to a worksheet within a workbook but then can't figure out how to save it as a new worksheet. Best we can figure, you have to make a copy first then modify it, what a pain. Is this the way it's supposed to work or am I missing something. Also, we've set "sheets in new workbook" to 1 but new workbooks still open with 3 blank worksheets. Any suggestions would be appreciated. BrianG |
| 125 | It could be that comma after the first address. -- Jeff McAhren Dallas, Texas "Lisa Hetherington" <lisa@guildstationers.com wrote in message news:AApP8.78131$Yt.2979239@read1.cgocable.net... Can someone pls tell me why this won't work?? I copied from a post here but modified it to take out an extra email address. Thank you Sub mailfast() ActiveSheet.Copy ActiveWorkbook.SendMail _ Recipient:="lisa@guildstationers.com", _ Subject:="(" & ActiveSheet.Name & ") Flyer Run List" ActiveWorkbook.Close False End Sub -- Lisa Hetherington Marketing Assistant Guild Stationers Limited/M. J. Richmond Wholesale 362 Dewitt Rd N Stoney Creek, ON L8E 2T2 (905) 662 4939 ext. 236 lisa@guildstationers.com |
| 130 | I cannot get the following code to work (which is in the Excel Help files)--I keep getting the error message "Your formula contains an invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct and try again." However, this doesn't make sense since everything else in the Sub Procedure works. I cannot get any code to return the trendline count--I always get various types of error messages. I want to use an IF statement to determine whether a trendline exists so that it can be deleted or added for different chart datasets (series collections). (Count code is almost at end of Sub Procedure) Private Sub DataSetList_Change() Dim strfile As String, intTrendline As Integer, intCount As Integer strfile = Application.ThisWorkbook.Name With Worksheets("Review").ChartObjects(3).Chart .HasTitle = True .ChartTitle.Text = DataSetList Select Case DataSetList Case "Total Authorized-RBM (Cum)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_Q" .SeriesCollection(1).Values = "='" & strfile & "'! Tot_Proj_Author_Cum" .SeriesCollection(2).Values = "='" & strfile & "'! Tot_Proj_Act_Cum" intTrendline = 1 Case "Total Authorized-RBM (Qtr)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_Q" .SeriesCollection(1).Values = "='" & strfile & "'! Tot_Proj_Author_Qtr" .SeriesCollection(2).Values = "='" & strfile & "'! Tot_Proj_Act_Qtr" intTrendline = 0 Case "2002 RBM Budget-Actual (Cum)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_M" .SeriesCollection(1).Values = "='" & strfile & "'! RBM_2002_Budget_Cum" .SeriesCollection(2).Values = "='" & strfile & "'! RBM_2002_Act_Cum" intTrendline=1 Case "2002 RBM Budget-Actual (M)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_M" .SeriesCollection(1).Values = "='" & strfile & "'! RBM_2002_Budget_M" .SeriesCollection(2).Values = "='" & strfile & "'! RBM_2002_Act_M" intTrendline=0 End Select End With MsgBox Worksheets("Review").Charts(3).SeriesCollection (2).Trendlines.Count 'If Worksheets("Review").Charts(3).SeriesCollection(2) '.Trendlines.Count = 1 And intTrendline = 0 Then 'ActiveSheet.ChartObjects("Chart 3").Activate 'ActiveChart.SeriesCollection(2).Trendlines(1).Select 'Selection.Delete 'Else If Worksheets("Review").Charts (3).SeriesCollection(2) '.Trendlines.Count = 0 And intTrendline = 1 Then 'Worksheets("Review").ChartObjects (3).Chart.SeriesCollection(2) '.SeriesCollection(2).Trendlines.Add Type:=xlLinear, Name:="Linear Trend" 'Worksheets("Review").ChartObjects(3).Activate 'End If Worksheets("Review").Range("B5").Activate End Sub |
| 143 | William - You skipped a level in the hierarchy of objects. Change this line: MsgBox Worksheets("Review").Charts(3).SeriesCollection(2).Trendlines.Count to this: MsgBox Worksheets("Review").ChartObjects(3).Chart.SeriesCollection(2) .Trendlines.Count (all one line - watch the email wordwrap). - Jon _______ In article <f36f01c21633$c64fb1d0$b1e62ecf@tkmsftngxa04, bartusek_william_c@cat.com says... I cannot get the following code to work (which is in the Excel Help files)--I keep getting the error message "Your formula contains an invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct and try again." However, this doesn't make sense since everything else in the Sub Procedure works. I cannot get any code to return the trendline count--I always get various types of error messages. I want to use an IF statement to determine whether a trendline exists so that it can be deleted or added for different chart datasets (series collections). (Count code is almost at end of Sub Procedure) Private Sub DataSetList_Change() Dim strfile As String, intTrendline As Integer, intCount As Integer strfile = Application.ThisWorkbook.Name With Worksheets("Review").ChartObjects(3).Chart .HasTitle = True .ChartTitle.Text = DataSetList Select Case DataSetList Case "Total Authorized-RBM (Cum)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_Q" .SeriesCollection(1).Values = "='" & strfile & "'! Tot_Proj_Author_Cum" .SeriesCollection(2).Values = "='" & strfile & "'! Tot_Proj_Act_Cum" intTrendline = 1 Case "Total Authorized-RBM (Qtr)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_Q" .SeriesCollection(1).Values = "='" & strfile & "'! Tot_Proj_Author_Qtr" .SeriesCollection(2).Values = "='" & strfile & "'! Tot_Proj_Act_Qtr" intTrendline = 0 Case "2002 RBM Budget-Actual (Cum)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_M" .SeriesCollection(1).Values = "='" & strfile & "'! RBM_2002_Budget_Cum" .SeriesCollection(2).Values = "='" & strfile & "'! RBM_2002_Act_Cum" intTrendline=1 Case "2002 RBM Budget-Actual (M)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_M" .SeriesCollection(1).Values = "='" & strfile & "'! RBM_2002_Budget_M" .SeriesCollection(2).Values = "='" & strfile & "'! RBM_2002_Act_M" intTrendline=0 End Select End With MsgBox Worksheets("Review").Charts(3).SeriesCollection (2).Trendlines.Count 'If Worksheets("Review").Charts(3).SeriesCollection(2) '.Trendlines.Count = 1 And intTrendline = 0 Then 'ActiveSheet.ChartObjects("Chart 3").Activate 'ActiveChart.SeriesCollection(2).Trendlines(1).Select 'Selection.Delete 'Else If Worksheets("Review").Charts (3).SeriesCollection(2) '.Trendlines.Count = 0 And intTrendline = 1 Then 'Worksheets("Review").ChartObjects (3).Chart.SeriesCollection(2) '.SeriesCollection(2).Trendlines.Add Type:=xlLinear, Name:="Linear Trend" 'Worksheets("Review").ChartObjects(3).Activate 'End If Worksheets("Review").Range("B5").Activate End Sub |
| 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 |
| 152 | Mary - This partially debugged macro will do this for up to 256 points per series: Sub MoveChartDataFromSheetToFormula() Dim myCht As Chart Dim mySrs As Series Dim iSrsCt As Integer, iSrsIx As Integer Dim iPtsCt As Long, iPtsIx As Long Dim strName As String Set myCht = ActiveChart iSrsCt = myCht.SeriesCollection.Count For iSrsIx = 1 To iSrsCt Set mySrs = myCht.SeriesCollection(iSrsIx) iPtsCt = mySrs.Points.Count Select Case TypeName(ActiveChart.Parent) Case "ChartObject" strName = "Cht" & myCht.Parent.Index Case "Workbook" strName = "Cht" & myCht.Index End Select ' Feeble attempt to prevent duplicate names strName = "Sht" & ActiveSheet.Index & strName & "Srs" & iSrsIx ActiveWorkbook.Names.Add _ Name:=strName & "X", _ RefersTo:=mySrs.XValues ActiveWorkbook.Names.Add _ Name:=strName & "Y", _ RefersTo:=mySrs.Values mySrs.Values = "='" & ActiveWorkbook.Name & "'!" & strName & "Y" mySrs.XValues = "='" & ActiveWorkbook.Name & "'!" & strName & "X" Next End Sub What it does is store the X and Y values into static named formulas in the workbook, and aplies these to the chart. - Jon _______ In article <dfa601c216db$e1a91a40$a5e62ecf@tkmsftngxa07, mary_johansen@roi.com says... I have a huge amount of data and many charts. However, I need to break the link in one of the charts so it won't update every time I change the data. Nothing Works! I've tried using the little camera icon, paste special is not available...all I could do was take a screen shot - has anyone else got a solution! We're crunching a deadline here -Help! |
| 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 |
| 165 | Steven, Search for a file called *.xlb (the exact name and location will depend on your Excel version and Windows version). Copy this file to the other machine, and then open it. A new XLB file will be created in the correct location with the correct name. If you have buttons that cause execution of your own custom macros, you will of course have to copy the files containing those macro to the new machine, and probably modify the macro assignment to point to the new location of the workbooks. If you are using only standard command buttons, you simply moving and opening the XLB file should be enough. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "sckeels" <sckeels@lucent.com wrote in message news:utv2pSvFCHA.2712@tkmsftngp05... Hello, How do you copy the toolbars from one pc to another? -- Thanks In Advance, Steven |
| 172 | I have an Excel 2000,SR1(9.0.4402) workbook that will NOT recalculate properly on cell references between worksheets. Recalc set to Automatic. I can open the file on some machines and it works perfectly; not on others. Can find no settings that are different between the various machines. Saved the file from a machine on which it worked; then it worked on the first machine for awhile, but eventually reverted to not working. Any ideas? Sheila |
| 177 | You may have some problems if you go from Excel 95 to a later version in your move; otherwise, shouldn't be a problem. If your assignments of menus and buttons are damaged due to a change in pathname, see this macro by Bernie Deitrick, misc, 2002-04-16, RepairUserDefinedButtons() to Repair pathnames involving reassigned personal.xls that get modified when redoing systems or Excel versions. The macro saves work having to change assigned macros on menus and toolbar buttons when the path to xlstart\personal.xls changes due to system and or Excel version changes. /groups?as_umsgid=eNXt5XX5BHA.1020@tkmsftngp07 "Chip Pearson" <chip@cpearson.com wrote in message news:OP0J#bvFCHA.1360@tkmsftngp05... Steven, Search for a file called *.xlb (the exact name and location will depend on your Excel version and Windows version). Copy this file to the other machine, and then open it. A new XLB file will be created in the correct location with the correct name. If you have buttons that cause execution of your own custom macros, you will of course have to copy the files containing those macro to the new machine, and probably modify the macro assignment to point to the new location of the workbooks. If you are using only standard command buttons, you simply moving and opening the XLB file should be enough. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "sckeels" <sckeels@lucent.com wrote in message news:utv2pSvFCHA.2712@tkmsftngp05... Hello, How do you copy the toolbars from one pc to another? -- Thanks In Advance, Steven |
| 184 | Hi Sheila, Excel takes the calculation setting from the first workbook opened, rather than from each workbook. So this can happen if you open other workbooks which are set to manual, or have a workbook in Xlstart which is set to manual, or ... Charles Williams ______________________ Decision Models The Excel Calculation Site www.DecisionModels.com "Sheila" <sleppert@chguernsey.com wrote in message news:10d5301c216fd$78c35780$35ef2ecf@TKMSFTNGXA11... I have an Excel 2000,SR1(9.0.4402) workbook that will NOT recalculate properly on cell references between worksheets. Recalc set to Automatic. I can open the file on some machines and it works perfectly; not on others. Can find no settings that are different between the various machines. Saved the file from a machine on which it worked; then it worked on the first machine for awhile, but eventually reverted to not working. Any ideas? Sheila |
| 197 | Just a question and guesses: Does excel blow up (with the same error) with any other workbook? If yes, then it's probably not this workbook. If no, then maybe the workbook is corrupted (like Dave suggested). I've had luck using xl2002 opening files that both xl97 and xl2k couldn't open. Sometimes formatting was lost, but the data/formulas/macros were there. Other people have reported that Sun's OpenOffice/StarOffice can sometimes open corrupted excel files. (It used to be between 80-110 meg download. I looked and decided that it's too big for my puny modem!) Not-for-free StarOffice. /software/download/desktop_app.html Free version /dev_docs/source/1.0.0/index.html IrelandA wrote: Dear All I have got a 3MB worksheet which I cannot open. When I try to open it, I get the following dialogue box: =================================== Application Error The instruction at "0x30099749f" referenced memory at "0x00000002". The memory could not be "read". Click on OK to terminate the application. Click on Cancel to debug the application. =================================== Even if I click on "Cancel", it doesn't open Excel, so I can't view the spreadsheet at all. Any help/advice gratefully received... Regards Alistair -- Dave Peterson ec35720@msn.com |
| 221 | 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 . . |
| 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 . . |
| 235 | I have recorded a macro to sort, copy and paste text data (Name, Sex, Age) in a defined dynamic range onto another worksheet. If I actually type the data into the defined range, the macro works fine, but if I copy and paste the data from another workbook into the defined range, the macro does not recognize any of the data I have pasted. I have even typed a few sets of data in, then pasted (and even tried "paste special - values") a few sets beneath them, then typed in a few more below those and when I run the macro, only the typed-in data is recognized. Can anyone explain this to me? I thought pasted or pasted-special data was treated the same as data that was keyed in directly. Thank you for any assistance. -Dave Trop- dmtrop@cox.net |
| 238 | In working with the data further, I find it is just the Age field that is affecting the macro. Even if I format it as General or Number, I cannot get the macro to work. If I type the same number into each cell that was copied there, the macro works fine. ???? "Dave Trop" <dmtrop@cox.net wrote in message news:53QP8.58314$Hn4.1998285@news1.east.cox.net... I have recorded a macro to sort, copy and paste text data (Name, Sex, Age) in a defined dynamic range onto another worksheet. If I actually type the data into the defined range, the macro works fine, but if I copy and paste the data from another workbook into the defined range, the macro does not recognize any of the data I have pasted. I have even typed a few sets of data in, then pasted (and even tried "paste special - values") a few sets beneath them, then typed in a few more below those and when I run the macro, only the typed-in data is recognized. Can anyone explain this to me? I thought pasted or pasted-special data was treated the same as data that was keyed in directly. Thank you for any assistance. -Dave Trop- dmtrop@cox.net |
| 261 | I am running Microsoft Excel for Windows 95 and am constantly receiving notificetions of a circular reference error witin one of my workbooks. It is supose to tell you where the circular reference error is but is not. Is there a way that I can search for the circular reference error? Regards, James |
| 276 | Chris, try =SUM('Patient 1:P10'!D38)/SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Patient 1","P2","P3","P4","P5","P6","P7","P8","P9","P10"}&"'!D38"),"<0")) Btw, why are you not using the same naming system for your sheets? if the first sheet was named P1, then you could use this =SUM('Patient 1:P10'!D38)/SUMPRODUCT(COUNTIF(INDIRECT("'P"&ROW(INDIRECT("1:10"))&"'!D38"),"<0")) also note that this will return a #DIV/0 error if all cells are empty, you can prevent that by using wrap it in an IF function like =IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Patient 1","P2","P3","P4","P5","P6","P7","P8","P9","P10"}&"'!D38"),"<0"))=0,0,SUM('Patient 1:P10'!D38)/SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Patient 1","P2","P3","P4","P5","P6","P7","P8","P9","P10"}&"'!D38"),"<0"))) if there are no negative numbers you could wrap with =if(sum('Patient 1:P10'!D38)=0,0,etc.. instead.. Also answered through email with sample attached -- Regards, Peo Sjoblom "Chris Bullock" <JohnCBullock@hotmail.com wrote in message news:e94701c217b5$6b9c73d0$36ef2ecf@tkmsftngxa12... Peo Sjoblom Thank You for your help with this, I did enter the formula you gave, although I did not understand what you meant by; entered with ctrl+shift&enter. I changed the cells referenced and this is the formula as I tried to use it =AVERAGE(IF('Patient 1:P10'! D38<0,'Patient 1:P10'!D38)) I am getting the #REF! error message, cell reference not valid. I do not understand why, Worksheet 1 thru 10 column D row 38 are all valid. If you are still willing to help with this I could definitely use the expert advice -----Original Message----- Try =AVERAGE(IF(A1:A100<0,A1:A100)) entered with ctrl+shift&enter -- Regards, Peo Sjoblom "Rob Fenn" <rob-fenn@maurice-phillips.co.uk wrote in message news:eBZb0h6FCHA.1360@tkmsftngp05... Chris Try the following. SUM(A1:A100)/COUNTIF(A1:A100,"0") but this will only work if there are no values less than 0. I am sure someone can think of an easier way but presumably this would work SUM(A1:A100)/(COUNT(A1:A100)-COUNTIF(A1:A100,"0")) A1:A100 is your range of data. HTH Rob "Chris Bullock" <JohnCBullock@hotmail.com wrote in message news:1027d01c217a7$48d06770$19ef2ecf@tkmsftngxa01... I am using Average functions accross worksheets and I need it to not use the cells that have a zero value in the average calculations. Is there a formula that will take care of this? The workbook is huge and deleting each cell with a zero value by hand will be very difficult. I have tried some If statements but have not been able to make any work because of limited knowledge. . |
| 292 | You are super. Thanks for all your wonderful code. "" <dmcritchie@msn.com wrote in message news:#iu1L46FCHA.2272@tkmsftngp09... Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column < 3 And Target.Column < 5 Then Exit Sub If UCase(Target.Value) = "XXX" Then _ Target.Value = "'" & Format(Date, "yyyy-mm-dd") End Sub Target is a variable name. See Help -- Index -- ByVal look at Sub Statement about half way down. That is about as well as I can answer what Target is you don't want to substitute activecell for anything there. The activecell for me would be target.offset(1,0) since the cursor goes down after entry (tools option) and if you want to change other cells you want to turn off EnableEvents Application.EnableEvents = False '--ooo coding ooo--- Application.EnableEvents = True Worksheet Events and Workbook Events /dmcritchie/excel/event.htm HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Splash" <splash@mosquitonet.com wrote in message news:uh19i93l24r367@corp.supernews.com... Now I'm trying something different. If every time I am in column 4 I want to check to see if the text is "XXX", and, if it is, to run a little code, do I replace "Text" with "Activecell"? [That is, in "ByVal Target As Excel.Range", have "ByVal activecell as Excel.range", and then throughout the code, where Target occurred put activecell??] If so, this opens up all possiblities. |
| 295 | Hey All, I have a workbook that has 100's of cells with formulas linking to values in another worksheet - although the locations of both of these worksheets has remained the same, somehow the worksheet with the links has 'lost' the worksheet with the source data. Every time I open it I now have to point excel to the source worksheet. is there any way to fix this short of rewriting the formulas? Thanks, Justin |
| 296 | Hi Marguerite, Administrative privileges. Excel needs to create a copy of the file to protect data if copy of file gets messed up and is not to be saved. The filename will be random looking characters and no file extension. Something that does not go over to well with network administrators or with antivirus programs. Anyway that is the problem. I'm not on a network so didn't try to answer deeper than that the MS KB has four articles that should help. Normally I would not include things directly out of the MS KB, but for those who don't know about the knowledge base, it, and Google Groups are our primary references. /dmcritchie/excel/xlnews.htm Searching the Knowledge Base /default.aspx?ln=EN-US&pr=kbinfo or if you can't stand the "improvements" above /oldmskb.htm Product: Excel for windows Type: All words Search: antivirus administrator Search Results Your search for antivirus administrator found 4 matches. 1 through 4 are listed below. {if you do your own search, you will have links to the following articles} 1) XL2000: "Your Changes Could Not Be Saved" Error While Saving to Network Drive (Q214032) Excerpt from this page:In Microsoft Excel, when you save a workbook file on a network drive, if you do not have both Modify and Delete rights for the directory to which you are saving the file, one of the following occurs:(size 9976 bytes, updated 12/3/2001 8:05:00 PM GMT) 2) XL2002: "Your Changes Could Not Be Saved" Error While Saving to a Network Drive (Q291070) Excerpt from this page:In Microsoft Excel, when you save a workbook file on a network drive, if you do not have both Modify and Delete rights for the directory to which you are saving the file, one of the following occurs:(size 10086 bytes, updated 12/3/2001 8:04:00 PM GMT) 3) XL: "Your Changes Could Not Be Saved" Error While Saving to Network Drive (Q113600) Excerpt from this page:In Microsoft Excel, when you save a workbook file on a network drive, if you do not have both Modify and Delete rights for the directory to which you are saving the file, one of the following occurs:(size 13286 bytes, updated 7/21/2001 3:14:00 AM GMT) 4) OFF2000: Error Message: "Internal Error 2351" or "Internal Error 2355" During Setup (Q236437) Excerpt from this page:When you run Setup for Microsoft Office 2000 or any Microsoft Office 2000 program, you may receive one or both of the following error messages:(size 25103 bytes, updated 1/9/2002 7:44:00 AM GMT) HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Marguerite Borck" <mborck@magliner.com wrote ... I have two users on a Windows 2000 Server LAN than cannot open Excel 2000 workbooks saved on a LAN drive in anything other than Read Only mode. As administrator or logged in as myself I can open and close the workbooks all day long. Anyone have any ideas for this mystery happening? thanks Margi |
| 302 | I did that with no success. The Age field is being used as Criteria for sorting. Does that make a difference? Thanks. -Dave Trop- "Tom Ogilvy" <twogilvy@msn.com wrote in message news:uYKh8z4FCHA.2296@tkmsftngp05... I suspect the number in the Age field is being stored as Text. Changing the format after the value is in the cell will not change how it is stored. Format the cells as general before you do the paste special (although a straight paste should work - as you state). Regards, Tom Ogilvy "Dave Trop" <dmtrop@cox.net wrote in message news:hyQP8.58441$Hn4.2007994@news1.east.cox.net... In working with the data further, I find it is just the Age field that is affecting the macro. Even if I format it as General or Number, I cannot get the macro to work. If I type the same number into each cell that was copied there, the macro works fine. ???? "Dave Trop" <dmtrop@cox.net wrote in message news:53QP8.58314$Hn4.1998285@news1.east.cox.net... I have recorded a macro to sort, copy and paste text data (Name, Sex, Age) in a defined dynamic range onto another worksheet. If I actually type the data into the defined range, the macro works fine, but if I copy and paste the data from another workbook into the defined range, the macro does not recognize any of the data I have pasted. I have even typed a few sets of data in, then pasted (and even tried "paste special - values") a few sets beneath them, then typed in a few more below those and when I run the macro, only the typed-in data is recognized. Can anyone explain this to me? I thought pasted or pasted-special data was treated the same as data that was keyed in directly. Thank you for any assistance. -Dave Trop- dmtrop@cox.net |
| 311 | Excel is capable of displaying only 56 colors at a time (it's color palette). The default color palette contains 46 different colors - 10 of them are duplicated. If you use VBA to specify an RGB color that's not in the palette, Excel will use the closest match. In other words, if you write a macro to change a cell's color using RGB values, there is no guarantee that the actual color will be displayed. Here's a macro that will give you some info about the color palette in the active workboook. It adds a new worksheet and then write six columns of data. Sub DisplayColorPalette() Dim r As Long Worksheets.Add Range("A1") = "Index" Range("B1") = "Color" Range("C1") = "Value" Range("D1") = "Red" Range("E1") = "Green" Range("F1") = "Blue" Range("A1:F1").Font.Bold = True For r = 2 To 57 Cells(r, 1) = r - 1 Cells(r, 2).Interior.Color = ActiveWorkbook.Colors(r - 1) Cells(r, 3) = ActiveWorkbook.Colors(r - 1) Cells(r, 4) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 0 And 255 Cells(r, 5) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 1 And 255 Cells(r, 6) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 2 And 255 Next r End Sub John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Work Fast Party Horror" <news@busymonster.com wrote in message news:tQ5Q8.360$bK.67166@news.uswest.net... i'm trying to create a spreadsheet with 4 columns: red, green, blue, and color. the red, green, and blue cells are rgb values. i want the 4th cell to have its background color set to the rgb values in its row. help! this seems like it should be a simple thing to do in excel, but i can't find a way to make it happen. i've explored conditional formatting, script editing, and vb, but no luck. any suggestions on how to make this work? thanks! |
| 312 | Hi Justin, One possibility, if that other workbook is personal.xls it should be in your XLSTART library that you say you didn't change any locations. It should also be hidden Window, hide Perhaps you just need to open one of the workbooks before the other. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Justin" <arki3345@amtrak.com wrote in message news:1041701c217c9$04920920$19ef2ecf@tkmsftngxa01... Hey All, I have a workbook that has 100's of cells with formulas linking to values in another worksheet - although the locations of both of these worksheets has remained the same, somehow the worksheet with the links has 'lost' the worksheet with the source data. Every time I open it I now have to point excel to the source worksheet. is there any way to fix this short of rewriting the formulas? Thanks, Justin |
| 315 | Hi James, If that doesn't work try Stephen Bullen's page findcirc.zip /Excel/Default.htm might lead one to wonder with all the hype on the merits of circular links that just maybe Stephen once had a problem with them. (probably on someone else's workbooks) HTH, , Microsoft MVP - Excel My Excel Macros: /dmcritchie/excel/excel.htm "Richard Price" <rjpriceuk@yahoo.co.uk wrote in message The status bar only indicates the cell reference when the sheet containing it is selected - at least in XL97 and 2000. So if you run through all 15 (quite quick using Ctrl+PgDn) you should see the reference pop up after "Circular:" on one of them. "James" <james_lumsden@baa.com wrote ... I am seeing Circular in the status bar but no cell reference. I have about 15 sheets and the error could be on any one of them! I was hoping there was some way to force Excel to search for the error. It could be that there are multiple errors and it does not know which one to point to. |
| 317 | Hello there, is there a way to change the chart window name in excel? If I write a macro and want it to change a chart in the workbook it needs the name of this chart (i.e. "chart 1"). However, if there are many charts the number may change. So I want to give the chart my own name but I couldn't figure out how... HELP - PLEASE? |
| 321 | I needed to do the same thing and the best way (lowest overhead) is to use a simple txt file In my case QuoteNumber.txt. Simple open Notepad and save a blank file titled QuoteNumber in the c:\ root directory, each time Sub GetQuoteNumber is run the macro will do the rest. Have fun! . Sub GetQuoteNumber() Dim TedsNumber As Long Dim FileNum As Integer 'gets any available file number FileNum = FreeFile Open "c:\QuoteNumber.txt" For Random As #FileNum Len = Len(TedsNumber) 'gets the current value Get #FileNum, 1, TedsNumber 'increments by one TedsNumber = TedsNumber + 1 'places the new value to file Put #FileNum, 1, TedsNumber Close #FileNum 'places the new incremented number into a worksheet called Sheet2 in cell C8 Sheets("Sheet2").Range("C8").Value = TedsNumber End Sub "Wilson" <jwilson@wickes.com wrote in message news:eBrqY#5FCHA.1692@tkmsftngp05... Hi Ian, One way would be to store the invoice number in an unused cell with the font color the same as the background color of that cell, say N1 for example Then get the invoice number that is visible with =N1 In ThisWorkbook module, increment the number by one whenever the file is opened Private Sub Workbook_Open() Range("N1").Value = Range("N1").Value + 1 End Sub HTH "Ian Roberts" <Ian_Roberts@SPAMOFFBTinternet.com wrote in message news:aeq2su$dds$1@nntp-m01.news.aol.com... Hi I've made a custom invoice template using Excel 2000. It would be useful if there was a way to keep a record of the invoice number so that it could automatically be incremented each time a new invoice is created. I tried doing a search but couldn't find much. The few 3rd party invoice templates I've seen have the macros hidden so I don't know how to reproduce the function on my sheet. I would appreciate any assistance or links to relevant info. Many thanks. Ian I^) |
| 323 | Thanks for the info. (50 meg might be worth it to find out if it can open files). Ron de Bruin wrote: 50 mb Dave I have download it for my brother two weeks ago.(10 minuts that is better than your puny modem) O yes I forgot somthing <vbg Regards Ron "Dave Peterson" <ec35720@msn.com schreef in bericht news:3D0FA860.66014496@msn.com... Just a question and guesses: Does excel blow up (with the same error) with any other workbook? If yes, then it's probably not this workbook. If no, then maybe the workbook is corrupted (like Dave suggested). I've had luck using xl2002 opening files that both xl97 and xl2k couldn't open. Sometimes formatting was lost, but the data/formulas/macros were there. Other people have reported that Sun's OpenOffice/StarOffice can sometimes open corrupted excel files. (It used to be between 80-110 meg download. I looked and decided that it's too big for my puny modem!) Not-for-free StarOffice. /software/download/desktop_app.html Free version /dev_docs/source/1.0.0/index.html IrelandA wrote: Dear All I have got a 3MB worksheet which I cannot open. When I try to open it, I get the following dialogue box: =================================== Application Error The instruction at "0x30099749f" referenced memory at "0x00000002". The memory could not be "read". Click on OK to terminate the application. Click on Cancel to debug the application. =================================== Even if I click on "Cancel", it doesn't open Excel, so I can't view the spreadsheet at all. Any help/advice gratefully received... Regards Alistair -- Dave Peterson ec35720@msn.com -- Dave Peterson ec35720@msn.com |
| 324 | This worked for me in xl2002 and it worked when I was running xl97. Tools|Macros|macros... In the "Macro Name" box: as soon as you type in the name correctly, then the "step into", "edit", "delete" and "options" button became enabled. Are you in the workbook that contains the function? Are you sure you typed in the name of the function correctly? Either of those would make it not work. Miguel Enriquez wrote: no man not see none, i can choose any Macro, if i type the name of my macro not enable the button Options...... i only can see my code if i go to: Tools-Macros-Visual Basic Editor ... thanks.... -- Dave Peterson ec35720@msn.com |
| 329 | By quick fill, I assume you mean dragging the Fill Handle at the bottom right of the current selection. What doesn't work? What happens when you point to the Fill Handle? (the pointer should change to a black plus sign) What happens if you drag the Fill Handle? You can create custom lists, and then edit them: 1. Select the cells which contain your list 2. Choose ToolsOptions, go to the Custom Lists tab 3. Click the Import button. 4. Click OK To edit the list: 1. Choose ToolsOptions, go to the Custom Lists tab 2. Select your custom list on the left 3. Edit the entries list on the right 4. Click OK Lee wrote: My quick fill does not seem to work on some of the workbooks. Is there a way to correct this? Also, how can I edit a quick fill list? -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 339 | If you're using xl2k or higher, you can select the worksheets (click on the first, control click on subsequent) and then do the find. You may want to try Jan Karel Pieterse's FlexFind at: /MVP/Default.htm (In fact, xl2002 has an option on the Find dialog that asks where to look: Within sheet or within workbook.) Andrew wrote: If i use the 'find' option to find a certain instance of a value or word, excel finds me every instance on that worksheet, but how do i get it to search every worksheet also within that file ? Cheers Andy -- Dave Peterson ec35720@msn.com |
| 341 | I have a question about values common to 2 lists, and have read many interesting postings from a variety of searches (also read MVP Pearson’s suggestions). Regretfully, I have not located any similar issues. So if someone would help I would be greatly appreciative. If cell A200 = abc and M200 = 10, then any new entry in column A which is not unique, say “abc” has a restriction on the other list (cell in same row; column M). Example: if someone types in “abc” in cell A3, then M3 wouldn’t accept data input. I am counting the values in column M, and only one instance per unique value in column A is permitted. There are several shared workbooks and one unshared archive workbook which all need to be checked to prevent incorrect data. Maybe it should be described as preventing duplicates on values common to 2 lists spanning several workbooks. This works when I put in validation custom in the cells in column M, but doesn’t span all the workbooks. =COUNTIF ($A$1:$A$50,A1)=1 |
| 343 | How do you change the font color on the tabs in a workbook? I either want to change the font color (different for each tab) or the backround color of the tab. Thanks for your help. LJ |
| 347 | buy xl2002 -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "lj" <johnsol@eaglehomemortgage.com wrote in message news:eb3501c217e5$0b42c4f0$2ae2c90a@hosting.microsoft.com... How do you change the font color on the tabs in a workbook? I either want to change the font color (different for each tab) or the backround color of the tab. Thanks for your help. LJ |
| 348 | activeworkbook.name=[a9] -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Bob" <bobnanw@optonline.net wrote in message news:103ba01c217e1$923e1da0$9be62ecf@tkmsftngxa03... Is there a way to name a tab with the contents of a cell? For example if A9 ="Soap" the worksheet tab will be labeled Soap. |
| 350 | Peter, If you'd like to send the workbook to me by email (not to the newsgroup), I can take a look. Debra PeterM wrote: Dear Debra............ I tried it and it still has a border around it. Maybe it is not a border, could it be looking the same, but it is something else? When I click on the sequence of the suggested procedure, it shows no borders in the place where the text box is. The box that shows the borders when you click on the icon with the different border settings I mean. Is that darn thing maybe a frame or something. I just don't know how else to describe it. I hope you don't mind helping me some more. My senior heart appreciates you a lot.............Peter |
| 351 | Super, I will do it..............Peter "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D1118BC.5000503@contextures.com... Peter, If you'd like to send the workbook to me by email (not to the newsgroup), I can take a look. Debra PeterM wrote: Dear Debra............ I tried it and it still has a border around it. Maybe it is not a border, could it be looking the same, but it is something else? When I click on the sequence of the suggested procedure, it shows no borders in the place where the text box is. The box that shows the borders when you click on the icon with the different border settings I mean. Is that darn thing maybe a frame or something. I just don't know how else to describe it. I hope you don't mind helping me some more. My senior heart appreciates you a lot.............Peter |
| 356 | I have a large spreadsheet that contains several links to data in other sheets. Every time I open the workbook, it asks if I want to update these links. As they are no longer useful, I want to get rid of them (freeze the data), but I can't find where they are! Is there a way to see a list of links or jump from one link to the next? Thanks. Charlie |
| 359 | Excel was opening workbooks and placing a task icon on task bar for each workbook open. This made it easy to jump from one workbook to another. Now for some reason when opening multiple workbooks there's only one task icon on the task bar. I have to click "window" command and select the workbook from dropdown list to jump between workbooks. How do I change Excel to display task icons for each workbook open on the task bar? |
| 360 | In my workbook, i need to do a lookup on Sheet 5 On sheet 5 F25 needs to find G25 on Sheet 4 C8:D45,2 On sheet 4 C8 the numbers are not in ascending order, I have tried Vlookup,find, search , since the numbers are not in ascending order, vlookup does not work properly Any help would be appreciated |
| 363 | Get a copy of Bill Manville's Findlink.xla. Download FINDLINK.ZIP from /mvp "Charles V. Stancampiano" wrote: I have a large spreadsheet that contains several links to data in other sheets. Every time I open the workbook, it asks if I want to update these links. As they are no longer useful, I want to get rid of them (freeze the data), but I can't find where they are! Is there a way to see a list of links or jump from one link to the next? Thanks. Charlie -- Dave Peterson ec35720@msn.com |
| 364 | Tools|options|View (check Windows in Taskbar) But you can also ctrl-F6 (or ctrl-shift-f6) to cycle through the open workbooks. (and you didn't ask, but ctrl-pageup/pagedown cycles through the sheets of the activeworkbook.) dan wrote: Excel was opening workbooks and placing a task icon on task bar for each workbook open. This made it easy to jump from one workbook to another. Now for some reason when opening multiple workbooks there's only one task icon on the task bar. I have to click "window" command and select the workbook from dropdown list to jump between workbooks. How do I change Excel to display task icons for each workbook open on the task bar? -- Dave Peterson ec35720@msn.com |
| 365 | Vlookup has a 4th parm that says find the first exact match. =vlookup(F25,sheet4!$c$8:$d$45,2,false) (false or 0 will mean find the exact match) (but I'm not sure I got the F25/G25 stuff correct.) Roger McDaniel wrote: In my workbook, i need to do a lookup on Sheet 5 On sheet 5 F25 needs to find G25 on Sheet 4 C8:D45,2 On sheet 4 C8 the numbers are not in ascending order, I have tried Vlookup,find, search , since the numbers are not in ascending order, vlookup does not work properly Any help would be appreciated -- Dave Peterson ec35720@msn.com |
| 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! |
| 376 | I have a spreadsheet with several combo boxes, which list data referenced from other cells in my workbook. I often get the message "Not Enough System Resources to Display Completely" when I click on one of the combo boxes. Maybe my computer is just wimpy. (but I have 256 MB of RAM, although not the fastest machine on the market). Anyone know what is causing the message to appear? I am running Office '97 on a Windows NT system. Thanks, Dan Winterton |
| 379 | Click on the chart whilst holding down the Ctrl key. You can then edit it's name in the name box near the top left corner of the screen. "Chris" <creug@mse.ufl.edu wrote in message news:1031d01c217d2$c61b63d0$9be62ecf@tkmsftngxa03... Hello there, is there a way to change the chart window name in excel? If I write a macro and want it to change a chart in the workbook it needs the name of this chart (i.e. "chart 1"). However, if there are many charts the number may change. So I want to give the chart my own name but I couldn't figure out how... HELP - PLEASE? |
| 381 | Hi I'm not sure, but maybe you can do it in Excel2002 (I remember vaquely somebody like it from one of Excel NG's). Arvi Laanemets "lj" <johnsol@eaglehomemortgage.com wrote in message news:eb3501c217e5$0b42c4f0$2ae2c90a@hosting.microsoft.com... How do you change the font color on the tabs in a workbook? I either want to change the font color (different for each tab) or the backround color of the tab. Thanks for your help. LJ |
| 386 | How are you applying the names and what do those names look like. You can have workbook level names and you can have worksheet level names. Worksheet level names start with the worksheet name then exclamation point then the range. 'my Sheet'!Test could refer to 'my sheet'!$a$1:$b$99 But since it starts with the worksheetname, it's not available on other sheets (unless you qualify it nicely). So I can have a workbook level name called Test, and worksheet level names of Test, too. == One more thing to check. Insert|Name|Define Do you see a worksheet name to the right of that box? If yes, then you've been using worksheet names. If not, then it must be something else! Troy Thiele wrote: I have been given a workbook with many formulas. I have been trying to name cells and apply those names to references in formulas on other sheets within the same workbook. Although it works fine if I am on the same sheet, I get a message indicating that Microsoft cannot find any references if I try to apply the names on different sheets. Ideas as to why this is occuring? Thanks, Troy -- Dave Peterson ec35720@msn.com |
| 388 | You can do it in xl2002, but you'd have to write a macro in earlier versions. You can protect a worksheet so that your code can make changes. But it has to be reset each time you open the workbook: something like: Worksheets("sheet1").Protect Password:="pass", UserInterFaceOnly:=True could go into workbook_open or auto_open. then give the users some buttons assigned to your macros: ActiveCell.EntireRow.Insert You could even prompt them and ask how many and where. Sue wrote: Is there any way to protect individual cells in a worksheet with disabling the ability to insert rows into the same worksheet? As soon as I turn on the protection, I loose the ability to insert. -- Dave Peterson ec35720@msn.com |
| 390 | I'm not real sure, but I think that this is a Mouse setting. If I go to Start|Settings|Control Panel|Mouse|Wheel tab, there's an option for "Scroll x lines at at time". (I'm using the MS Intellipoint(??) mouse.) matt wrote: I am trying to scroll 4 rows at a time (with one turn of a mouse wheel) on an Excel spread sheet. It appears that the default is set to 3 rows. Is it possible to change the setting for individual sheets within a workbook? Thank you for your help in advance. Matt -- 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 |
| 407 | Ian, You want duplicates to be allowed in column A, but where they exist, don't allow an entry in M for a new (duplicate) A value, in the same sheet. M must remain empty in any duplicate of A. And other lists must be checked for these dups of col A? I suspect that this will be more straightforward if you combine the tables. Is that practical in your situation? It's often easier to keep data combined, then extract what you need. Your formula will work then. You can always use an additional field (column) to differentiate rows that came from different tables, if needed. Other operations may be more straightforward too, with these tables combined. Worth looking at. If that isn't doable, it may be necessary to use a macro to look for dups in the other sheets in other workbooks. Data-Validation doesn't let you refer to other sheets, as you've found out. Regards from Virginia Beach, EarlK ------------------------------------------------------------- "ian berger" <i_berger@yahoo.com wrote in message news:dc7301c217e1$ae4e4240$95e62ecf@tkmsftngxs02... I have a question about values common to 2 lists, and have read many interesting postings from a variety of searches (also read MVP Pearson’s suggestions). Regretfully, I have not located any similar issues. So if someone would help I would be greatly appreciative. If cell A200 = abc and M200 = 10, then any new entry in column A which is not unique, say “abc” has a restriction on the other list (cell in same row; column M). Example: if someone types in “abc” in cell A3, then M3 wouldn’t accept data input. I am counting the values in column M, and only one instance per unique value in column A is permitted. There are several shared workbooks and one unshared archive workbook which all need to be checked to prevent incorrect data. Maybe it should be described as preventing duplicates on values common to 2 lists spanning several workbooks. This works when I put in validation custom in the cells in column M, but doesn’t span all the workbooks. =COUNTIF ($A$1:$A$50,A1)=1 |
| 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 |
| 430 | Eric One way: Use this UDF (UserDefinedFunction) to get the date. Copy and paste the code to a general module (<Alt<F11 Insert Module) In a cell enter =creadate() Remember the empty parentheses. Format the cell as Date. -- Best regards Leo Heuser MVP Excel Function CreaDate() As Date CreaDate = ActiveWorkbook. _ BuiltinDocumentProperties("Creation date") End Function "Eric" <eric.goodrich@paccoast.com skrev i en meddelelse news:11c9701c218a8$83d85380$35ef2ecf@TKMSFTNGXA11... Is there a function in Excel that will return the date a file is created into a cell? |
| 444 | HI, This could help, used in a other file. Sub RevertFile() Workbooks("Test2.xls").Activate ActiveWorkbook.Close SaveChanges:=False Workbooks.Open FileName:="C:\MYdocs\Test2.xls" End Sub HTH Paul "Et il en est qui s'étonnent encore qu'un message qui ne comporte ni bonjour, ni merci, ne recoive pas de réponse" (Lao Tseu Atch Oum) "Bruce" <bruce@nha.co.za a écrit dans le message de news: 11e8201c218ff$3286fe30$35ef2ecf@TKMSFTNGXA11... Is there a way that a macro can revert to the saved version of a spreadsheet? ie. If a user has made lots of chnages to the spreadsheet, then clicks a "clear" button it reverts to its original template format. thanks |
| 458 | I'm trying to create a spreadsheet programmatically using OWC (using VS.NET). The file ('Book1.xls') gets created but when I try to open it excel can't display it - it appears crashed, as the main window is not painted. Also, contrary to available documentation, the excel spreadsheet becomes visible as soon as the application object is created, which creates an annoying flicker even if I hide it immediately. Excel::_ApplicationPtr a(__uuidof(Excel::Application)); a-Visible[LOCALE_USER_DEFAULT]=false; Excel::_WorkbookPtr wb = a-Workbooks-Add(); wb-Activate(LOCALE_USER_DEFAULT); Excel::_WorksheetPtr ws = wb-Worksheets-Add(); // Other stuff here, filling in worksheet etc. wb-Save(LOCALE_USER_DEFAULT); wb-Close(); a-Quit(); What have I missed? Tony |
| 475 | merci beaucoup! -----Original Message----- HI, This could help, used in a other file. Sub RevertFile() Workbooks("Test2.xls").Activate ActiveWorkbook.Close SaveChanges:=3DFalse Workbooks.Open FileName:=3D"C:\MYdocs\Test2.xls" End Sub HTH Paul "Et il en est qui s'=E9tonnent encore qu'un message qui ne=20 comporte ni bonjour, ni merci, ne recoive pas de r=E9ponse" (Lao=20 Tseu Atch Oum) "Bruce" <bruce@nha.co.za a =E9crit dans le message de=20 news: 11e8201c218ff$3286fe30$35ef2ecf@TKMSFTNGXA11... Is there a way that a macro can revert to the saved version of a spreadsheet? ie. If a user has made lots=20 of chnages to the spreadsheet, then clicks a "clear"=20 button it reverts to its original template format. thanks . |
| 481 | Using XL 97 on Win 95. I inherited a simple workbook (no macros to worry about). 1 page with raw data, another page with graphs linked to the raw data (for an "official" looking report). A few simple formulas, but nothing complex in the workbook. I haven't touched any of that- I want to make an easier data entry page, so I figured I'd build the interface, then copy the data over. The I can change the references from the "report" page to my new raw data page. So, I created a new sheet & renamed 'KRef' to hold some lists that I want in comboboxes related to data entry. Then I created a new sheet & renamed 'KEntry' and added 3 comboboxes. When I enter linked ranges, most of them work fine: KRef!A1:A4 KRef!D1:D12 _but_ if I press the '8' key above the alpha keys (inside the linked range property box), it crashes XL. I can replicate this either as part of correct syntax: KRef!A1:A8 <crash or incorrect syntax KRef!8<crash It does not wait for a further event- it crashes the moment I press '8' (e.g. it doesn't wait for me to leave the linked range property) The message I get is: [Excel] "This program has performed an illegal operation and will be shut down...if the problem persists, contact the program vendor" [Details] - EXCEL cause an invalid page fault in Module VBE.DLL as 0137:6500e1d0 (etc) My first thought was that I had a sticky alt or shift or control key, so I switched keyboards- but it still happens, even with a different keyboard. I tried using the keypad, and that 8 works ok (e.g. does not crash XL). I always thought that the same signal was sent to the CPU from the two "8" keys, but maybe not? Either way, it shouldn't be crashing- anyone have any ideas? Thanks in advance, Keith |
| 489 | I am trying to troubleshoot a problem where the format cells dialog box will not appear when trying to format a cell with data in it or if the cell has had previous formatting applied to it. The dialog box appears normally when trying to format an empty cell or if the range selected included an empty cell. I've tried pasting the data into a clean workbook with no luck. Has anyone ever seen this behavior before? I am using Excel 97. Thanks in advance! Chris Lunde |
| 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 |
| 511 | Dave, That's great, thanks. Is there a way I can ask for a password in order to run this macro - if not correct then it exits? Then I could leave this macro in the workbook and just protect the code. Rob. -----Original Message----- Hi Rob This code will toggle sheet protection on/off Sub ToggleProtectContent() Dim i As Integer For i = 3 To 50 If Sheets(i).ProtectContents = False Then Sheets(i).Protect Password:="rob1" Else Sheets(i).Unprotect Password:="rob1" End If Next i End Sub It only checks for Content protection. -- "Rob Pearce" <rob.pearce@consignia.com wrote in message news:f81c01c219bb$7dfb11a0$a4e62ecf@tkmsftngxa06... | I have a workbook with 50 worksheets. | | I protect sheets 3 to 50 [all being the same template for | data entry] with the same password. | | When I want to change some formula/formatting on these 48 | sheets I have to unprotect each sheet individually so that | I can eventually group them together to alter. | | Would it be possible to have some code that will loop from | shhet 3 to 50 and unprotect - the password is rob1 if | anyone could lend a hand - and conversely then to re | protect. | | I would obviously remove the macros from the workbook | afterwards! | | Thanks, | | Rob. . |
| 527 | Here is one approach. This will not re-protect the sheets. You will have to use the code from the previous response for that. Sub Get_Password() On Error GoTo errPassword Dim strPassword As String Dim i As Integer '*** GET THE PASSWORD FROM USER strPassword = InputBox("Enter Password", "Unprotection Password") '*** TRY TO UNPROTECT THE FIRST SHEET Sheets(3).Unprotect Password:=strPassword '*** IF NO ERROR THEN UNPROTECT THE REST OF THE SHEETS For i = 4 To 50 Sheets(i).Unprotect Password:=strPassword Next i Exit Sub errPassword: '*** DISPLAY A MESSAGE IF PASSWORD INCORRECT AND EXIT MsgBox "You entered and incorrect password!" End Sub -----Original Message----- Dave, That's great, thanks. Is there a way I can ask for a password in order to run this macro - if not correct then it exits? Then I could leave this macro in the workbook and just protect the code. Rob. -----Original Message----- Hi Rob This code will toggle sheet protection on/off Sub ToggleProtectContent() Dim i As Integer For i = 3 To 50 If Sheets(i).ProtectContents = False Then Sheets(i).Protect Password:="rob1" Else Sheets(i).Unprotect Password:="rob1" End If Next i End Sub It only checks for Content protection. -- "Rob Pearce" <rob.pearce@consignia.com wrote in message news:f81c01c219bb$7dfb11a0$a4e62ecf@tkmsftngxa06... | I have a workbook with 50 worksheets. | | I protect sheets 3 to 50 [all being the same template for | data entry] with the same password. | | When I want to change some formula/formatting on these 48 | sheets I have to unprotect each sheet individually so that | I can eventually group them together to alter. | | Would it be possible to have some code that will loop from | shhet 3 to 50 and unprotect - the password is rob1 if | anyone could lend a hand - and conversely then to re | protect. | | I would obviously remove the macros from the workbook | afterwards! | | Thanks, | | Rob. . . |
| 541 | Hi David, Stephen once had a problem with them. (probably on someone else's workbooks) Yes and Yes <g Regards Stephen Bullen Microsoft MVP - Excel www.BMSLtd.co.uk |
| 566 | Hi Pete: You need to write your formula as a function and put it in a standard module. If you want the function to be available to all workbooks, you would generally put it in your Personal Macro workbook. An example: Function DoubleSquared(iInt As Long) As Long DoubleSquared = (2 * iInt) ^ 2 End Function Now, if you put the number 3 in cell A1, and put the formula: =DoubleSquared(A1) in another cell, the result will be the square of twice the number in A1; that is, 36. -- Regards, Vasant. **No direct emails please--keep discussion in newsgroup.** "Pete" <derounpr@hotmail.com wrote in message news:f98701c21a42$2611a710$2ae2c90a@hosting.microsoft.com... I was wondering if anyone out there knows how to create user-defined formulas in Excel. Thanks, Pete |
| 578 | You could add some code that will insert the date in the footer before the sheet is printed. For example, the following code (in the ThisWorkbook module) adds the formatted date to the right footer: Private Sub Workbook_BeforePrint(Cancel As Boolean) Worksheets("Sheet1").PageSetup.RightFooter = _ Format(Date, "dd-mmm-yy") End Sub Vittal wrote: So I am the first visitor. Can anybody tell me how to change the date format in footer. I am using WIN 98, Office 2000. Presently it is coming in dd-mm-yy format. The Regional Settings properties for date tab are as follow: dd-mmm-yy I want the date format to look like 21-Jun-02. Thanks in advance. Vittal -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 586 | Hallo, I'd like to find out how I can query another sheet in the same workbook and put the result in a combobox. The list in this combobox is the result of a selection in a previous combobox. I this way, I like to refine the possibilities. Greetings, Hans. |
| 588 | Hi John Place in the ThisWorkbook module: Private Sub Workbook_SheetChange(ByVal _ Sh As Object, ByVal Target As Range) If Target(1).Address = "$K$2" Then If Target(1).Value < "" Then 'add more validation here Sh.Name = Replace(Target(1).Value, " ", "_") End If End If End Sub it renames the sheet on entry in any K2. HTH. Best wishes Harald "John Vera" <emperorj@hotmail.com wrote in message news:f70501c21a0b$48cd6d20$37ef2ecf@TKMSFTNGXA13... Thanks for your help Harald. I'll try it out soon. For my sheetname, I guess I'd like any sheet to refer to its own K2 cell. Do you have that macro? Thanks again, John -----Original Message----- Hi John I too fail to see a system here. But see if this get you started: =CELL("address",OFFSET(INDIRECT(F3),0,1)) As for sheetnames, not without a macro. Want that ? Which cell if so ? HTH. Best wishes Harald "John Vera" <emperorj@hotmail.com skrev i melding news:f83201c21a05$2da08b50$36ef2ecf@tkmsftngxa12... Hi again. What I'm trying to do is figure out this: I have " $A$3 " (no quotes) as a character string in my F3 cell. I want to automatically make cell F5 relate to that string and display "$B$3" as a character string (no quotes). Any ideas? As a bonus, is there any way to make a sheet name automatically be the same as a given cell in said sheet? for instance, cell D4 says "Program", I want the sheet to be Program as well, or whatever I type in the cell. Thanks again! John . |
| 589 | For the first combobox you can simply use data/data validation/ list/ - the list source is the range with the list items. for the second combo you can use the same menu but the range for the list should be dynamic i.e use Offset combined with Match to allocate the right list range for your first selection. for example =OFFSET($A$2,MATCH($A$9,$A$2:$A$5,0)-1,1,1,100) $A$9 is the address of the first combobox $A$2:$A$5 is the address of the list items for the first combobox The items for each selection are on columns B onward (100 columns). Alon "Hans Dijckmans" <hans.dijckmans@laterio.com wrote in message news:117fa01c21a8f$a04558e0$3aef2ecf@TKMSFTNGXA09... Hallo, I'd like to find out how I can query another sheet in the same workbook and put the result in a combobox. The list in this combobox is the result of a selection in a previous combobox. I this way, I like to refine the possibilities. Greetings, Hans. |
| 599 | In most versions of Excel, if the worksheet is protected, users will not be able to change the cell formatting, which includes the borders, font colour, font size and cell colour. In Excel 2002, you can choose options as you set the protection, and cell formatting can be allowed. Maybe you have protected the workbook, instead of the worksheet. arthur w. mohr, jr. wrote: How can I protect the format of a worksheet. For example; frame around a cell font size and color cell color I have protect all attributes of the worksheet using tools/protect but the users of the worksheet are still able to modify the items mentioned above. Thanks. -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 614 | As Peo says, hide it. Once you've done this and close Excel, it'll ask if you want to save your changes to Personal.xls. Choosing 'Yes' will mean that the next time you open Excel, Personal.xls will be hidden from the outset. It doesn't say anything about Personal.xls in Excel Help, but I *believe* that the *only* use of Personal.xls (which is stored in your 'alternate startup file location' - via Tools | Options | General) is for storing macros that you want to have available for all spreadsheets. (Please can somebody correct me if this is wrong.) I know that it ain't the default template (cf. Normal.dot in Word) because that's Book.xlt, if you have it, which sits in your XLStart folder. "Supereal" <none@none.com wrote in message news:11e6901c21b91$9e6ebae0$3aef2ecf@TKMSFTNGXA09... Whenever I start up excel, rather than a new workbook, PERSONAL.XLS starts up in its place. Also, whenever I double click another excel file to open up, excel starts, open my file and also opens up PERSONAL.XLS. Does anyone know why this is happening and how to stop it? Thanks. SnR |
| 620 | The Personal.xls problem seems to be well covered. The second instance of Excel opening upon double-click is usually resolved by ToolsOptionsGeneral uncheck "Ignore other Applications". HTH Gord Dibben Excel MVP - XL97 SR2 On Mon, 24 Jun 2002 18:35:00 +0100, "ikr" <ripsher[nospam]@btinternet.com wrote: As Peo says, hide it. Once you've done this and close Excel, it'll ask if you want to save your changes to Personal.xls. Choosing 'Yes' will mean that the next time you open Excel, Personal.xls will be hidden from the outset. It doesn't say anything about Personal.xls in Excel Help, but I *believe* that the *only* use of Personal.xls (which is stored in your 'alternate startup file location' - via Tools | Options | General) is for storing macros that you want to have available for all spreadsheets. (Please can somebody correct me if this is wrong.) I know that it ain't the default template (cf. Normal.dot in Word) because that's Book.xlt, if you have it, which sits in your XLStart folder. "Supereal" <none@none.com wrote in message news:11e6901c21b91$9e6ebae0$3aef2ecf@TKMSFTNGXA09... Whenever I start up excel, rather than a new workbook, PERSONAL.XLS starts up in its place. Also, whenever I double click another excel file to open up, excel starts, open my file and also opens up PERSONAL.XLS. Does anyone know why this is happening and how to stop it? Thanks. SnR |
| 630 | Deb Your question, if I can call it that, is too broad to handle. You have to resolve to work this problem like you would eat a cow. One piece at a time. Be more specific about what you have to start, what you want to finish with, and how you propose to get there. The people in these newsgroups can help you with virtually anything but we have to start with something smaller than the whole cow. HTH Otto "Deb Koplen" <deb.koplen@verizon.com wrote in message news:fc8801c21b27$cdee0d80$36ef2ecf@tkmsftngxa12... First, thanks for the responses. But I see I need to be more detailed in describing my "opportunity"! I have a tendancy to overcompensate for my natural wordiness. I prepare 17 different workbooks containing product sales results vs. plan on anywhere from 1 to 20 "sub-products". One of the things that has been added this month is a forcast for the "best view" of how the year will turn out. As usual, management (in their infinate wisdom) has said what the year-end results are to be. What is needed from the field is their estimate by month of how this "best view" will be reached. For June they will fill in July thru December. And the total for each of the sub- products must equal management's best view less actuals to date. I need a way to enforce that the input adds up to the required total -- down to the decimal. AND I don't want to give the field the opportunity of fudging management's best view. When the sheets come back, it is a very quick turn-around befor I need to publish the results back to management and already involves a number of processes. I don't want to have to add checking each spread sheet just to make sure that the field has input the correct number for each sub-product. I was thinking a pop-up would help point out any error in imput. I was looking at activate/deactivate as a way of double-checking before the workbook is returned to me. It is always best for the field to "find" their own mistakes, and fix them, rather than me having to call them up and tell them of their error and finding out how they want to correct it. Comments and suggestions, please. |
| 633 | I need to have a button in Macro which when pressed shd. access worksheet in another workbook. I want a generic macro c |