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 code for this as there are many worksheets in the other workbook which I would like to access. I would like to pass on the name of the worksheet alone as a parameter and assign the macro to the button. Help me please... |
| 637 | Jan's method works with foreground colors. Dave's works with background colors. Both are great, and I can make use of this myself. The major problem with both methods is that when you change a color, the sum or the count does not change. For example, if I change a green color to a red, neither the green totals nor the red totals changes. Can either of you offer a solution to this? Many thanks, Paul "" <DavidH@OzGrid.com wrote in message news:<jiDR8.19$926.7591@vicpull1.telstra.net... Hi Debbie I have a UDF that does this here: /VBA/Sum.htm It looks remarkably similar to Jans. -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Jan Karel Pieterse" <pieterse_home@compuserve.com wrote in message news:1186301c21b6d$0194cf30$9be62ecf@tkmsftngxa03... | Hi, | | I assumed the text has the color, not the interior. | Try this user defined function: | | Option Explicit | | Function CountColour(rRange As Range, iColor As Integer) | Dim rCell As Range | Dim lTotal As Long | Application.Volatile | For Each rCell In rRange | If rCell.Font.ColorIndex = iColor Then | lTotal = lTotal + 1 | End If | Next | CountColour = lTotal | End Function | | Now to count red cells in the range A1:A5, use this | function: | | =CountColour(A1:A5,3) | | How to enter a macro (=VBA code): | | 1. Open the Excel file you want to add the code to. | | 2. Choose Tools, Macro, Visual Basic Editor to open the | Visual Basic Editor (or press Alt+F11). This is the | design environment that stores the VBA code. If this | is | the first time you have opened the editor, you will | probably see three windows: the Project window, the | Properties window, and the Code window. | | 3. In the Project window, select the name of the workbook | you want to add the sub to (one project is listed for | each open file). | | 4. After selecting the project icon, choose Insert, | Module. | This inserts a VBA code module into the project and | places the insertion point in the Code window of that | module. | | 5. You can either type the snippet code exactly as | written, | or you can copy the code directly from this message | and | paste it into the Code window. | | Regards, | | Jan Karel Pieterse | Excel TA/MVP | | -----Original Message----- | I am wanting to count dates in a range, but by colour. | Ie, all red dates to give a total number, all blue dates | to give a total number. | | Can anyone help?? | | Debbie. | . | |
| 640 | One way: Public Sub Button1_Click() MyMacro "Sheet1" End Sub Public Sub Button2_Click() MyMacro "Sheet2" End Sub Public Sub MyMacro(shtNum As String) With Workbooks("Workbook2.xls").Sheets(shtNum) 'Do something End With End Sub In article <120d001c21bb5$c9e4c9c0$39ef2ecf@TKMSFTNGXA08, Excelwhiz <sriam@hotamil.com wrote: I need to have a button in Macro which when pressed shd. access worksheet in another workbook. I want a generic macro code for this as there are many worksheets in the other workbook which I would like to access. I would like to pass on the name of the worksheet alone as a parameter and assign the macro to the button. Help me please... |
| 651 | What XL stores internally and what it displays are two separate things. If you need the calculated value of 2.108 which is displayed with no digits after the decimal point (i.e., as 2) to be 2 you have at least 3 ways: 1) In the cell which displays 2, wrap your calculation with =ROUND(your calc, 0) 2) Choose Tools/Options/Calculation and check the Precision as displayed checkbox. Note that this applies to ALL cells in the workbook, so calculations which depend on non-displayed digits will be incorrect. 3) In your =2x2 cell, use ROUND(): =ROUND(A1,0) * ROUND(A2,0) (note: NOT =ROUND(A1 * A2, 0) !) In article <1215f01c21bc4$3f173260$39ef2ecf@TKMSFTNGXA08, Scott <sss935@hotmail.com wrote: Here is my problem! When I am multiplying two sets of numbers I need to get the exact amount. However, Excel rounds to two decimal places. Example is 2x2=4.44444 How do I make my totaling cell equal what the true amount is. Any Help Is Appreciated. |
| 653 | Hello All, I've been working with charts that generate into userforms and I'm having alot of fun with it (can you tell that I'm in finance...). Anyway, I've been using John Walkenbach's fabulous code from the "Power Programming with VBA". It's working really well right now, but what I'd like to do is have more than one dataobject when I generate the graph. I'll show you what I have so far... Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) UserRow = ActiveCell.Row If UserRow < 2 Or IsEmpty(Cells(UserRow, 1)) Then MsgBox "Move the cell cursor to a row that contains data." Exit Sub End If CreateChart (UserRow) UserForm1.Show End Sub Sub CreateChart(r) Dim Charttitle1 As String Dim ChartTitle2 As String Dim Charttitle3 As String Dim Tempchar As Chart Application.ScreenUpdating = False Set CatTitles = ActiveSheet.Range("E2:AZ2") Set ScrRange = ActiveSheet.Range(Cells(r, 5), Cells(r, 52)) Charttitle1 = Cells(r, 2) ChartTitle2 = Cells(r, 3) Charttitle3 = Cells(r, 1) Set SourceData = Union(CatTitles, ScrRange) Set tempchart = Charts.Add With tempchart .ChartType = xlLineMarkers .SetSourceData Source:=SourceData, PlotBy:=xlRows .HasLegend = False .ApplyDataLabels Type:=xlDataLabelsShowNone, LegendKey:=False .HasTitle = True .Charttitle.Text = "Transit: " & Charttitle3 & " " & Charttitle1 & "-" & ChartTitle2 .Charttitle.Font.Bold = True .Charttitle.Font.Size = 12 '.Axes(xlValue).MaximumScale = 30000 .Axes(xlCategory).TickLabels.Font.Size = 6 .Axes(xlCategory).TickLabels.Orientation = xlHorizontal .Location where:=xlLocationAsObject, Name:="Volumes" End With With ActiveSheet.ChartObjects(1) .Width = 600 .Height = 300 .Visible = False End With Private Sub UserForm_Initialize() Set currentchart = ActiveSheet.ChartObjects(1).Chart fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif" currentchart.Export Filename:=fname, filtername:="GIF" Sheets("volumes").ChartObjects(1).Delete Image1.Picture = LoadPicture(fname) UserForm1.Caption = "Charts 'n Stuff" Application.ScreenUpdating = True End Sub Is there any way to modify this code so that the user can select more than one row and be able to compare and contrast the data? Any help would be greatly appreciated, and a special thanks to John for the great resource book he's written, Thanks in advance, Mike |
| 654 | I keep my personal.xl* in my XLStart folder (along with book.xlt and sheet.xlt). I don't think I've ever pointed to any folder for the "alternate startup file location" (renamed in xl2002 to: "at startup, open all files in:"). ikr wrote: As Peo says, hide it. Once you've done this and close Excel, it'll ask if you want to save your changes to Personal.xls. Choosing 'Yes' will mean that the next time you open Excel, Personal.xls will be hidden from the outset. It doesn't say anything about Personal.xls in Excel Help, but I *believe* that the *only* use of Personal.xls (which is stored in your 'alternate startup file location' - via Tools | Options | General) is for storing macros that you want to have available for all spreadsheets. (Please can somebody correct me if this is wrong.) I know that it ain't the default template (cf. Normal.dot in Word) because that's Book.xlt, if you have it, which sits in your XLStart folder. "Supereal" <none@none.com wrote in message news:11e6901c21b91$9e6ebae0$3aef2ecf@TKMSFTNGXA09... Whenever I start up excel, rather than a new workbook, PERSONAL.XLS starts up in its place. Also, whenever I double click another excel file to open up, excel starts, open my file and also opens up PERSONAL.XLS. Does anyone know why this is happening and how to stop it? Thanks. SnR -- Dave Peterson ec35720@msn.com |
| 682 | Does anyone know if it is possible to allow sheets to be re-ordered in a protected workbook but NOT added, deleted or renamed? |
| 684 | Hi Dawn: I don't believe that this is possible, at least through Excel 97. -- Regards, Vasant. **No direct emails please--keep discussion in newsgroup.** "Dawn" <dsirras@attbi.com wrote in message news:11ca201c21bf0$f914dbf0$b1e62ecf@tkmsftngxa04... Does anyone know if it is possible to allow sheets to be re-ordered in a protected workbook but NOT added, deleted or renamed? |
| 695 | Hi Mr NDP Not sure whether you set your Workbook to manual calculation on purpose or not, but if you do be very careful that you do not feed yourself erroneous results. It's all too easy to forget to force a calculation when not in Automatic. If you have a Workbook that needs to run in manual calculation it normally means you have a design problem. See the links below for spreadsheet designs: /News/CalculationRangeIndirect.htm /News/ConFormatCheckFind.htm /News/GoodVsBadDesignSpeedUpEvents.htm -- "Mr NDP" <mr_ndp@yahoo.com wrote in message news:1245801c21bfe$a8004bb0$3bef2ecf@TKMSFTNGXA10... | That was it. I had my calculations set to manual. | | Thanks for the help... | | | -----Original Message----- | Hi: | | Do you have the calculation mode set to manual? If so, | hitting F9 should | update the formulas. | -- | Regards, | | Vasant. | | **No direct emails please--keep discussion in | newsgroup.** | | | "Mr NDP" <mr_ndp@yahoo.com wrote in message | news:12ed401c21bfb$928d7300$35ef2ecf@TKMSFTNGXA11... | All, | | I've noticed in my spreadsheets where I use the countif | function that the cells will not always update. Saving | the document seems to refresh the sheet and the | calculations are emplemented. Is there a way to | refresh | the formulas in a worksheet without having to save it? | | Thanks, | | Mr NDP | | | . | |
| 697 | Hi Dave, Not sure if I agree on the design problem Dave. I have a not-too-large workbook, but since it contains lots of array formula's it's recalc is very time consuming. When I need to do some data entry in that workbook I am glad I set calc to manual... Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- Hi Mr NDP Not sure whether you set your Workbook to manual calculation on purpose or not, but if you do be very careful that you do not feed yourself erroneous results. It's all too easy to forget to force a calculation when not in Automatic. If you have a Workbook that needs to run in manual calculation it normally means you have a design problem. See the links below for spreadsheet designs: /News/CalculationRangeIndirect.htm /News/ConFormatCheckFind.htm /News/GoodVsBadDesignSpeedUpEvents.ht m -- "Mr NDP" <mr_ndp@yahoo.com wrote in message news:1245801c21bfe$a8004bb0$3bef2ecf@TKMSFTNGXA10... | That was it. I had my calculations set to manual. | | Thanks for the help... | | | -----Original Message----- | Hi: | | Do you have the calculation mode set to manual? If so, | hitting F9 should | update the formulas. | -- | Regards, | | Vasant. | | **No direct emails please--keep discussion in | newsgroup.** | | | "Mr NDP" <mr_ndp@yahoo.com wrote in message | news:12ed401c21bfb$928d7300$35ef2ecf@TKMSFTNGXA11... | All, | | I've noticed in my spreadsheets where I use the countif | function that the cells will not always update. Saving | the document seems to refresh the sheet and the | calculations are emplemented. Is there a way to | refresh | the formulas in a worksheet without having to save it? | | Thanks, | | Mr NDP | | | . | . |
| 699 | Hi Jan If you read the links you will see that I say array formulae are the main cause of sloooowww calculations. Arrays (IMO) are a quick fix superficial approach to a possible complex problem. The use of Excels database functions can often be used in place of arrays as can Pivot Tables etc I still hold firm that any spreadsheet that requires manual calculation has a spreadsheet design problem. You run the risk of reading uncalculated results. It's a bit like you car brakes are rubbing (and slowing you down) so you disconnect them and rely on the handbrake. -- "Jan Karel Pieterse" <pieterse_home@compuserve.com wrote in message news:ff2101c21c0e$9c93ffa0$9ee62ecf@tkmsftngxa05... | Hi Dave, | | Not sure if I agree on the design problem Dave. | I have a not-too-large workbook, but since it contains | lots of array formula's it's recalc is very time | consuming. When I need to do some data entry in that | workbook I am glad I set calc to manual... | | Regards, | | Jan Karel Pieterse | Excel TA/MVP | | -----Original Message----- | Hi Mr NDP | | Not sure whether you set your Workbook to manual | calculation on purpose or | not, but if you do be very careful that you do not feed | yourself erroneous | results. It's all too easy to forget to force a | calculation when not in | Automatic. If you have a Workbook that needs to run in | manual calculation it | normally means you have a design problem. See the links | below for | spreadsheet designs: | | /News/CalculationRangeIndirect.htm | /News/ConFormatCheckFind.htm | /News/GoodVsBadDesignSpeedUpEvents.ht | m | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | If it's Excel, then it's us! | | "Mr NDP" <mr_ndp@yahoo.com wrote in message | news:1245801c21bfe$a8004bb0$3bef2ecf@TKMSFTNGXA10... | | That was it. I had my calculations set to manual. | | | | Thanks for the help... | | | | | | -----Original Message----- | | Hi: | | | | Do you have the calculation mode set to manual? If so, | | hitting F9 should | | update the formulas. | | -- | | Regards, | | | | Vasant. | | | | **No direct emails please--keep discussion in | | newsgroup.** | | | | | | "Mr NDP" <mr_ndp@yahoo.com wrote in message | | news:12ed401c21bfb$928d7300$35ef2ecf@TKMSFTNGXA11... | | All, | | | | I've noticed in my spreadsheets where I use the | countif | | function that the cells will not always update. | Saving | | the document seems to refresh the sheet and the | | calculations are emplemented. Is there a way to | | refresh | | the formulas in a worksheet without having to save | it? | | | | Thanks, | | | | Mr NDP | | | | | | . | | | | | . | |
| 703 | Hi drwinterton I am sure someone will come along with a possible answer for you. I just wanted to see if your area aware of the EditLinksChange source option available in Linked Workbooks. -- "drwinterton" <daniel_winterton@hk.ml.com wrote in message news:1223601c21c1a$285a9e30$3aef2ecf@TKMSFTNGXA09... | I have a problem that appears to be fairly widespread. I | haven't, however, found a solution to my exact problem on | this site... | | Here's the trouble. I have a system of files that I need | to be able to transfer over to a different drive. | | I've read that as long as everything is in the same | directory and the sub-directory structure and names of the | sub-directories are left intact that Excel will pretend | the references are relative (aka, I can move the files and | they will work). Alas, it doesn't appear to be working | for me. | | Here's the magnitude of data that I'm worried about: 26 | directories, 25 of which are designed to collect data from | different users (the whole reason this all isn't just one | file anymore), and 1 directory to compile this information | (and also serve as a source of a single piece of | information for every file in the other directories). In | each of the 25 directories, I will have 9 or 36 | (undecided) files that I need to reference. | | The collection folder contains two files - fetcher (which | gets the data for me) and sesdata (which the input files | need to reference). The collection folder is | named "master", the other inputs are all numbered. All | these directories are in the directory "qpa." | | I try to move the directory containing both master and the | input folders (qpa), and all my links which, if relative, | would read "../master/sesdata" break. I would like to be | able to design this system on my pc, record it to a CD, | then copy it onto different computers. Due to the number | of links, the edit links option simply won't work. I am | willing to move the directory structure (to some extent), | try VBA (throw code at me, I don't do Basic), or whatever. | Or if I need to try to use Quattro Pro, tell me. | | |
| 705 | Dear All I am using Excel 97. Is there any way that, as in an Access database, that an Excel workbook can be opened by many users and updated? If this requires DAO/ADO programming or general Excel functions please offer some advice on the way to target the problem. Alastair MacFarlane |
| 707 | Dear Tom Hope you remember me whom you helped to write the macro below. This really helpful to my job, however when I apply it to an excel worksheet of 15 columns, 40 thousand records I find it has to work for several hours, which is impractical. I don't really know which part of the marco waste most of the time. So I write here and want to know if the marco can be trimmed so as to make it run more faster. Your help is very much appreciated! Best regards Jason ----- Original Message ----- From: "Tom Ogilvy" <twogilvy@msn.com Newsgroups: microsoft.public.excel.misc Sent: Monday, May 13, 2002 5:46 AM Subject: Re: How to handle worksheet and save it as xls.file? That revision was based on an earlier, slightly different version of the original - it had hard coded paths for the file operations. This has the sPath variable (like the original) to make it easier to specify the location for the new files: Sub StartSplitout() Dim sh As Worksheet Dim sh1 As Worksheet Dim varr As Variant Dim rng As Range Set rng = Range(Cells(1, 1), Cells(1, "IV").End(xlToLeft)) varr = rng.Value Cells(1, 1).EntireRow.Delete ActiveSheet.Copy Set sh = ActiveSheet Splitout sh, varr ThisWorkbook.Activate Cells(1, 1).EntireRow.Insert Cells(1, 1).Resize(, UBound(varr, 2)).Value = varr End Sub Sub Splitout(sh As Worksheet, varr As Variant) Dim bContinue As Boolean Dim rng As Range, Cell As Range Dim rw As Long, sh1 As Worksheet Dim sPath As String sPath = "C:\Data\" bContinue = False Set rng = sh.Range(sh.Cells(1, 1), _ sh.Cells(1, 1).End(xlDown)) For Each Cell In rng If Cell.Row < 1 Then If Cell.Value < _ Cell.Offset(-1, 0).Value Then bContinue = True rw = Cell.Row Exit For End If End If Next If bContinue Then sh.Copy Set sh1 = ActiveSheet sh1.Range(sh1.Cells(1, 1), sh1.Cells(rw - 1, 1)). _ EntireRow.Delete sh.Range(sh.Cells(rw, 1), _ sh.Cells(Rows.Count, 1).End(xlUp)) _ .EntireRow.Delete sh.Cells(1, 1).EntireRow.Insert sh.Cells(1, 1).Resize(, UBound(varr, 2)).Value = varr On Error Resume Next Kill sPath & sh.Cells(2, 1).Value & _ ".xls" On Error GoTo 0 sh.Parent.SaveAs sPath & _ sh.Cells(2, 1).Value & ".xls" sh.Parent.Close SaveChanges:=False Splitout sh1, varr Else On Error Resume Next Kill sPath & sh.Cells(2, 1).Value & _ ".xls" On Error GoTo 0 sh.Cells(1, 1).EntireRow.Insert sh.Cells(1, 1).Resize(, UBound(varr, 2)).Value = varr sh.Parent.SaveAs sPath & _ sh.Cells(2, 1).Value & ".xls" sh.Parent.Close SaveChanges:=False End If End Sub Regards, Tom Ogilvy |
| 709 | Hi Jason I am sure Tom will be along soon to help you, but in the mean time you might find these links of use: /VBA/VBACode.htm /VBA/SpeedingUpVBACode.htm /VBA/VBALoops.htm Hope they help -- "ims" <kft_icq@myrealbox.com wrote in message news:#jjYydCHCHA.368@tkmsftngp13... | Dear Tom | | Hope you remember me whom you helped to write the macro below. | This really helpful to my job, however when I apply it to an excel worksheet | of 15 columns, 40 thousand records I find it has to work for several hours, | which is impractical. | I don't really know which part of the marco waste most of the time. | So I write here and want to know if the marco can be trimmed so as to make | it run more faster. | Your help is very much appreciated! | | Best regards | | Jason | | | | ----- Original Message ----- | From: "Tom Ogilvy" <twogilvy@msn.com | Newsgroups: microsoft.public.excel.misc | Sent: Monday, May 13, 2002 5:46 AM | Subject: Re: How to handle worksheet and save it as xls.file? | | | That revision was based on an earlier, slightly different version of the | original - it had hard coded paths for the file operations. This has the | sPath variable (like the original) to make it easier to specify the | location | for the new files: | | Sub StartSplitout() | Dim sh As Worksheet | Dim sh1 As Worksheet | Dim varr As Variant | Dim rng As Range | Set rng = Range(Cells(1, 1), Cells(1, "IV").End(xlToLeft)) | varr = rng.Value | Cells(1, 1).EntireRow.Delete | ActiveSheet.Copy | Set sh = ActiveSheet | Splitout sh, varr | ThisWorkbook.Activate | Cells(1, 1).EntireRow.Insert | Cells(1, 1).Resize(, UBound(varr, 2)).Value = varr | End Sub | | Sub Splitout(sh As Worksheet, varr As Variant) | Dim bContinue As Boolean | Dim rng As Range, Cell As Range | Dim rw As Long, sh1 As Worksheet | Dim sPath As String | sPath = "C:\Data\" | bContinue = False | Set rng = sh.Range(sh.Cells(1, 1), _ | sh.Cells(1, 1).End(xlDown)) | For Each Cell In rng | If Cell.Row < 1 Then | If Cell.Value < _ | Cell.Offset(-1, 0).Value Then | bContinue = True | rw = Cell.Row | Exit For | End If | End If | Next | If bContinue Then | sh.Copy | Set sh1 = ActiveSheet | sh1.Range(sh1.Cells(1, 1), sh1.Cells(rw - 1, 1)). _ | EntireRow.Delete | sh.Range(sh.Cells(rw, 1), _ | sh.Cells(Rows.Count, 1).End(xlUp)) _ | .EntireRow.Delete | sh.Cells(1, 1).EntireRow.Insert | sh.Cells(1, 1).Resize(, UBound(varr, 2)).Value = varr | On Error Resume Next | Kill sPath & sh.Cells(2, 1).Value & _ | ".xls" | On Error GoTo 0 | sh.Parent.SaveAs sPath & _ | sh.Cells(2, 1).Value & ".xls" | sh.Parent.Close SaveChanges:=False | Splitout sh1, varr | Else | On Error Resume Next | Kill sPath & sh.Cells(2, 1).Value & _ | ".xls" | On Error GoTo 0 | sh.Cells(1, 1).EntireRow.Insert | sh.Cells(1, 1).Resize(, UBound(varr, 2)).Value = varr | sh.Parent.SaveAs sPath & _ | sh.Cells(2, 1).Value & ".xls" | sh.Parent.Close SaveChanges:=False | End If | End Sub | | Regards, | Tom Ogilvy | | | | |
| 717 | Don't think INDIRECT works for closed Workbooks <bg -- "Jan Karel Pieterse" <pieterse_home@compuserve.com wrote in message news:1005401c21c35$215b2120$9ee62ecf@tkmsftngxa05... | Hi, | | Some questions if I may. | | - How did you copy your files? | - Did you copy the complete tree with the files? | - what is a correct link and what does it look like after | copying you files (and opening the master copy) | | I guess simplest should be to simply copy the entire tree | to another drive. But if the links travel with that also | depends on their exact type. If they are direct cell to | cell links, AFAIK they should adjust to the new location. | | But if any part of them are hardcoded (like in the | INDIRECT function), well... But I guess you already knew | that <g. | | | Regards, | | Jan Karel Pieterse | Excel TA/MVP | | -----Original Message----- | I have a problem that appears to be fairly widespread. I | haven't, however, found a solution to my exact problem on | this site... | | Here's the trouble. I have a system of files that I need | to be able to transfer over to a different drive. | | I've read that as long as everything is in the same | directory and the sub-directory structure and names of | the | sub-directories are left intact that Excel will pretend | the references are relative (aka, I can move the files | and | they will work). Alas, it doesn't appear to be working | for me. | | Here's the magnitude of data that I'm worried about: 26 | directories, 25 of which are designed to collect data | from | different users (the whole reason this all isn't just one | file anymore), and 1 directory to compile this | information | (and also serve as a source of a single piece of | information for every file in the other directories). In | each of the 25 directories, I will have 9 or 36 | (undecided) files that I need to reference. | | The collection folder contains two files - fetcher (which | gets the data for me) and sesdata (which the input files | need to reference). The collection folder is | named "master", the other inputs are all numbered. All | these directories are in the directory "qpa." | | I try to move the directory containing both master and | the | input folders (qpa), and all my links which, if relative, | would read "../master/sesdata" break. I would like to be | able to design this system on my pc, record it to a CD, | then copy it onto different computers. Due to the number | of links, the edit links option simply won't work. I am | willing to move the directory structure (to some extent), | try VBA (throw code at me, I don't do Basic), or | whatever. | Or if I need to try to use Quattro Pro, tell me. | | | . | |
| 721 | Hi, Itried creating an add-in tohave some Macros and functions of my own available on any workbook, but Ican't seem to be able access the macros and funs on the add-in I create after I install it with the add-in manager. I can access the code with the VB Editor, but can't find neither macros' name on the "run macro" menu nor functions' name in the insert "function menu" Guess there's something wrong with the code itself, but I can't figure out what exactly. Please mail me an answer directly, since I got problem accessing newsgroups. In case you do, please remove "NOSPAM" from address. Thanks a lot |
| 724 | Hi, I am able to pass parameters to a VBA macro from the 'Assign Macro' dialog by enclosing in single quotes. e.g. Book1!'Msg "Hello"' to execute the macro in the open workbook 'Book1': Public Sub Msg(m As String) MsgBox m End Sub However I am unable to do the same for a macro in an open Add-in. If I try 'MyAddin.xla'!'Foo "param1"' I am getting an error saying The macro 'Book1'!'Foo "param1"' cannot be found. I can invoke Macros with no parameters in the Add-in. Anybody know why this is not working, and if there is a way to make it work? Thanks in advance. |
| 727 | Hi Dave, If you read the links you will see that I say array formulae are the main cause of sloooowww calculations. Arrays (IMO) are a quick fix superficial approach to a possible complex problem. I totally disagree on the last remark. Yes, they tend to be slow. But they are often an elegant solution to an indeed complex problem. They are far from superficial and seldomly a quick fix. The use of Excels database functions can often be used in place of arrays as can Pivot Tables etc That may go for extracting information from simple tables. As soon as one has to combine data from different subsets, XL's database functions just aren't good enough. Sometimes an array formula can then come to rescue. I still hold firm that any spreadsheet that requires manual calculation has a spreadsheet design problem. You run the risk of reading uncalculated results. Disagreed again. I just happen to have a lot of (very complex) data and calc sometimes. But there is always the risk of uncalculated data, indeed. It is a matter of knowing ones business. These workbooks are not for the masses, they are for me. Printouts are for my colleagues, which are always automatically calculated. Regards, Jan Karel Pieterse Excel TA/MVP |
| 731 | Jan, I agree to disagree. If your methods work for you then stick with them. -- "Jan Karel Pieterse" <pieterse_home@compuserve.com wrote in message news:11fa701c21c4e$f10c30d0$b1e62ecf@tkmsftngxa04... | Hi Dave, | | | | If you read the links you will see that I say array | formulae are the main | cause of sloooowww calculations. Arrays (IMO) are a quick | fix superficial | approach to a possible complex problem. | | I totally disagree on the last remark. Yes, they tend to | be slow. But they are often an elegant solution to an | indeed complex problem. They are far from superficial and | seldomly a quick fix. | | The use of Excels database functions | can often be used in place of arrays as can Pivot Tables | etc | | That may go for extracting information from simple tables. | As soon as one has to combine data from different subsets, | XL's database functions just aren't good enough. Sometimes | an array formula can then come to rescue. | | | I still hold firm that any spreadsheet that requires | manual calculation has | a spreadsheet design problem. You run the risk of reading | uncalculated | results. | | Disagreed again. I just happen to have a lot of (very | complex) data and calc sometimes. But there is always the | risk of uncalculated data, indeed. It is a matter of | knowing ones business. These workbooks are not for the | masses, they are for me. Printouts are for my colleagues, | which are always automatically calculated. | | | Regards, | | Jan Karel Pieterse | Excel TA/MVP | |
| 735 | A solution using VBA is fine, I just have a lot to learn in that area (writing them myself, I mean). The only hitch with your answer is that there are usually multiple (anywhere from 1 to 20) hidden columns throughout each worksheet (possible example: A, B, C, F, G, J, T, S, and AB are hidden). That would mean that I would have to check manually which columns are hidden and then type them each into the name box to delete them. I am looking for something a little more functional. Thanks! Laura -----Original Message----- Hi Laura I assume you mean without VBA, if so assume Column "C" is hidden, type C:C in the name box (left of the formula bar) and push enter. Now go to EditDelete. There is no doubt a shortcut key for deleting Columns, you should find it here: /Excel/ExcelKeyBoardShortcutKeys.htm -- "Laura Wilde" <lwilde@new.rr.com wrote in message news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07... | Is it possible to delete hidden columns without unhiding | them? I get many workbooks sent to me with hidden | columns that contain data I do not need. Some are | formulas but I usually "copy" and "paste values" so there | is no worry of linking errors. | | Thanks for any help you can give me. | | Laura . |
| 738 | Hi Laura: Sub DeleteHiddenRows() Dim c As Range For Each c In Rows(1).Cells If c.Width = 0 Then c.EntireColumn.Delete Next End Sub -- Regards, Vasant. **No direct emails please--keep discussion in newsgroup.** "Laura Wilde" <lwilde@new.rr.com wrote in message news:1278901c21cb4$bc02b420$3aef2ecf@TKMSFTNGXA09... A solution using VBA is fine, I just have a lot to learn in that area (writing them myself, I mean). The only hitch with your answer is that there are usually multiple (anywhere from 1 to 20) hidden columns throughout each worksheet (possible example: A, B, C, F, G, J, T, S, and AB are hidden). That would mean that I would have to check manually which columns are hidden and then type them each into the name box to delete them. I am looking for something a little more functional. Thanks! Laura -----Original Message----- Hi Laura I assume you mean without VBA, if so assume Column "C" is hidden, type C:C in the name box (left of the formula bar) and push enter. Now go to EditDelete. There is no doubt a shortcut key for deleting Columns, you should find it here: /Excel/ExcelKeyBoardShortcutKeys.htm -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Laura Wilde" <lwilde@new.rr.com wrote in message news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07... | Is it possible to delete hidden columns without unhiding | them? I get many workbooks sent to me with hidden | columns that contain data I do not need. Some are | formulas but I usually "copy" and "paste values" so there | is no worry of linking errors. | | Thanks for any help you can give me. | | Laura . |
| 739 | Hi Laura Push Alt+F11, then go InsertModule and paste in the code below: Sub DeleteHiddenCols() Dim rCell As Range 'Runs on the active sheet Application.Calculation = xlCalculationManual For Each rCell In Rows(1) If rCell.EntireColumn.Hidden = True Then rCell.EntireColumn.Delete End If Next rCell Application.Calculation = xlCalculationAutomatic End Sub Psuh Alt+Q, then Alt+F8 click "DeleteHiddenCols" then Options and asign a shortcut key. -- Hope this helps. "Laura Wilde" <lwilde@new.rr.com wrote in message news:1278901c21cb4$bc02b420$3aef2ecf@TKMSFTNGXA09... | A solution using VBA is fine, I just have a lot to learn | in that area (writing them myself, I mean). | | The only hitch with your answer is that there are usually | multiple (anywhere from 1 to 20) hidden columns | throughout each worksheet (possible example: A, B, C, F, | G, J, T, S, and AB are hidden). That would mean that I | would have to check manually which columns are hidden and | then type them each into the name box to delete them. I | am looking for something a little more functional. | | Thanks! | | Laura | | | -----Original Message----- | Hi Laura | | | I assume you mean without VBA, if so assume Column "C" | is hidden, type C:C | in the name box (left of the formula bar) and push | enter. Now go to | EditDelete. There is no doubt a shortcut key for | deleting Columns, you | should find it here: | /Excel/ExcelKeyBoardShortcutKeys.htm | | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | If it's Excel, then it's us! | | | "Laura Wilde" <lwilde@new.rr.com wrote in message | news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07... | | Is it possible to delete hidden columns without | unhiding | | them? I get many workbooks sent to me with hidden | | columns that contain data I do not need. Some are | | formulas but I usually "copy" and "paste values" so | there | | is no worry of linking errors. | | | | Thanks for any help you can give me. | | | | Laura | | | . | |
| 741 | As long as your careful with your formulas/values: Option Explicit Sub delhiddencols() Dim iCol As Long Dim deleteme As Range For iCol = 1 To 256 If Columns(iCol).Hidden = True Then If deleteme Is Nothing Then Set deleteme = Cells(1, iCol) Else Set deleteme = Union(Cells(1, iCol), deleteme) End If End If Next iCol If deleteme Is Nothing Then 'can't do anything Else deleteme.EntireColumn.Delete End If End Sub You may want to add some checks for protection/filters, etc. Things that will stop you from deleting columns. Maybe even a little check to ask "are you sure"? Laura Wilde wrote: A solution using VBA is fine, I just have a lot to learn in that area (writing them myself, I mean). The only hitch with your answer is that there are usually multiple (anywhere from 1 to 20) hidden columns throughout each worksheet (possible example: A, B, C, F, G, J, T, S, and AB are hidden). That would mean that I would have to check manually which columns are hidden and then type them each into the name box to delete them. I am looking for something a little more functional. Thanks! Laura -----Original Message----- Hi Laura I assume you mean without VBA, if so assume Column "C" is hidden, type C:C in the name box (left of the formula bar) and push enter. Now go to EditDelete. There is no doubt a shortcut key for deleting Columns, you should find it here: /Excel/ExcelKeyBoardShortcutKeys.htm -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Laura Wilde" <lwilde@new.rr.com wrote in message news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07... | Is it possible to delete hidden columns without unhiding | them? I get many workbooks sent to me with hidden | columns that contain data I do not need. Some are | formulas but I usually "copy" and "paste values" so there | is no worry of linking errors. | | Thanks for any help you can give me. | | Laura . -- Dave Peterson ec35720@msn.com |
| 743 | Thanks everyone...all work perfectly! -----Original Message----- A solution using VBA is fine, I just have a lot to learn in that area (writing them myself, I mean). The only hitch with your answer is that there are usually multiple (anywhere from 1 to 20) hidden columns throughout each worksheet (possible example: A, B, C, F, G, J, T, S, and AB are hidden). That would mean that I would have to check manually which columns are hidden and then type them each into the name box to delete them. I am looking for something a little more functional. Thanks! Laura -----Original Message----- Hi Laura I assume you mean without VBA, if so assume Column "C" is hidden, type C:C in the name box (left of the formula bar) and push enter. Now go to EditDelete. There is no doubt a shortcut key for deleting Columns, you should find it here: /Excel/ExcelKeyBoardShortcutKeys.ht m -- "Laura Wilde" <lwilde@new.rr.com wrote in message news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07... | Is it possible to delete hidden columns without unhiding | them? I get many workbooks sent to me with hidden | columns that contain data I do not need. Some are | formulas but I usually "copy" and "paste values" so there | is no worry of linking errors. | | Thanks for any help you can give me. | | Laura . . |
| 754 | Hi there. Windows 2000, Service Pack 2 Excel 2000, Service Pack 1 When the user logs on to the domain, he launches his spreadsheet directly from the server folder, it takes several minutes to load. When I log on as Administrator on the same domain, the workbook opens straight away. This also happens when I log on locally as Administrator or local user on the server. Any idea why so slow? Thanks, Neal Blackie. |
| 756 | Thanks Dave, However my problem is not calling it from within a VBA macro but I want an Excel developer who may know nothing about VBA to pass parameters to a VBA macro contained in a loaded Add-in from a button he has added to the spreadsheet. The syntax I used ('MyAddin.xla'!'Foo "param1"') from within the Assign Macro dialog of the excel button control seemed to be ignoring the 'MyAddin.xla' prefix and assuming it was in the workbook containing the button (Book1.xls). Steve What I was hoping for was "Dave Peterson" <ec35720@msn.com wrote in message news:3D18E8DF.4D4DEB54@msn.com... When I tried to pass a string to your Msg sub, I could do it like this: Application.Run "'book1.xla'!msg", "hi there!" I could also do it like this, if I did a Tools|references and set a reference to that other project. msg "hi there2" But I'm not sure if this was the answer to your question.... Steve Legg wrote: Hi, I am able to pass parameters to a VBA macro from the 'Assign Macro' dialog by enclosing in single quotes. e.g. Book1!'Msg "Hello"' to execute the macro in the open workbook 'Book1': Public Sub Msg(m As String) MsgBox m End Sub However I am unable to do the same for a macro in an open Add-in. If I try 'MyAddin.xla'!'Foo "param1"' I am getting an error saying The macro 'Book1'!'Foo "param1"' cannot be found. I can invoke Macros with no parameters in the Add-in. Anybody know why this is not working, and if there is a way to make it work? Thanks in advance. -- Dave Peterson ec35720@msn.com |
| 791 | Otto, Nothing happens at all!! Thanks anyway, Rob. -----Original Message----- Rob I'm assuming that your workbook has only one sheet. If that is not the case, then add code to first select the pertinent sheet. I also added code to scroll the cell containing today's date to the top left of the display. Put this macro in your Workbook module. HTH Otto Private Sub Workbook_Open() Range("A:A").Find(What:=Date).Select With ActiveWindow .ScrollRow = ActiveCell.Row .ScrollColumn = ActiveCell.Column End With End Sub "Rob Pearce" <rob.pearce@consignia.com wrote in message news:123a201c21c5b$d54f7070$19ef2ecf@tkmsftngxa01... I have a worksheet that I use for a diary. In column A I have dates going on for the next 3 years. How can I get the workbook to open up on todays date everytime I open it up? I presume some kind of code in the auto_open sub. [Platform 2000] Thanks, Rob. . |
| 805 | I posted this through google, but it seemed to crash. So I thought I'd use CDO: I _think_ I understand: I dropped a button from the forms toolbar onto a worksheet. I have 3 examples of the .onaction stuff. Comment out two of them. Option Explicit Sub testme() Dim cb As Button Set cb = ActiveSheet.Buttons(1) Dim c As Range Set c = Range("A1") c.Value = "rangeA1value" Dim i As Long i = 8 With cb .OnAction = ThisWorkbook.Name _ & "!'testme1 " & Chr(34) & c.Value & Chr(34) & "'" .OnAction = ThisWorkbook.Name _ & "!'testme2 ""Hello"", 3'" .OnAction = Workbooks("book2.xls").Name _ & "!'testme3 " & Chr(34) _ & "different wkbk" & Chr(34) & "'" End With End Sub Sub testme1(myval As Variant) MsgBox "From Testme1: " & myval End Sub Sub testme2(mystr As String, myval As Long) MsgBox "From Testme2: " & mystr & " " & myval * 10 End Sub and from Book2.xls Option Explicit Sub testme3(mystr As String) MsgBox "From " & ThisWorkbook.Name & ": " & mystr End Sub I've never used the CDO before. Watch out for linewrap problems. -----Original Message----- Thanks Dave, However my problem is not calling it from within a VBA macro but I want an Excel developer who may know nothing about VBA to pass parameters to a VBA macro contained in a loaded Add-in from a button he has added to the spreadsheet. The syntax I used ('MyAddin.xla'!'Foo "param1"') from within the Assign Macro dialog of the excel button control seemed to be ignoring the 'MyAddin.xla' prefix and assuming it was in the workbook containing the button (Book1.xls). Steve What I was hoping for was "Dave Peterson" <ec35720@msn.com wrote in message news:3D18E8DF.4D4DEB54@msn.com... When I tried to pass a string to your Msg sub, I could do it like this: Application.Run "'book1.xla'!msg", "hi there!" I could also do it like this, if I did a Tools|references and set a reference to that other project. msg "hi there2" But I'm not sure if this was the answer to your question.... Steve Legg wrote: Hi, I am able to pass parameters to a VBA macro from the 'Assign Macro' dialog by enclosing in single quotes. e.g. Book1!'Msg "Hello"' to execute the macro in the open workbook 'Book1': Public Sub Msg(m As String) MsgBox m End Sub However I am unable to do the same for a macro in an open Add-in. If I try 'MyAddin.xla'!'Foo "param1"' I am getting an error saying The macro 'Book1'!'Foo "param1"' cannot be found. I can invoke Macros with no parameters in the Add-in. Anybody know why this is not working, and if there is a way to make it work? Thanks in advance. -- Dave Peterson ec35720@msn.com . |
| 806 | Hello, I have a form template (write-up rev 031902) and need help with the following: In my form I have B14......B29 ...in these cells I put in a part number... and would like the description for that part number to be added into C14....C29 For the description and part number I have created another workbook named "partdesc"......were column A..has the part numbers and they start from Cell 3 down and column B has the description for those part numbers. How can I write a macro for this... Thanks -- JR |
| 817 | "Jason" <jlittlet@torontohydro.com wrote in message news:1065601c21d09$267b6f50$9ee62ecf@tkmsftngxa05... Is there a way to delete a choice from the custom header or footer from the drop down choice list. Hi Jason, Unfortunately, as far as I know there isn't any way to remove these once you've added them short of recreating the workbook. -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals / * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * |
| 829 | Hi, You don't really need a macro for this. The VLOOKUP commmand should do the job nicely. Just search for vlookup in excel help for all the info you need. It would be easier if your sheet containing part numbers and descriptions was a different sheet in the same workbook, rather than in another workbook. HTH, Steve -----Original Message----- Hello, I have a form template (write-up rev 031902) and need help with the following: In my form I have B14......B29 ...in these cells I put in a part number... and would like the description for that part number to be added into C14....C29 For the description and part number I have created another workbook named "partdesc"......were column A..has the part numbers and they start from Cell 3 down and column B has the description for those part numbers. How can I write a macro for this... Thanks -- JR . |
| 831 | We have a user with low vision. He can read yellow on dark blue pretty well. MS Word has an option to display all text as white on blue background, which is pretty good also. I changed his default font to yellow and his system (Windows) background to dark blue, but of course when he sends a workbook to someone else they see yellow fonts on their white background, and when it gets printed on a laser printer it's very faint gray on white. So obviously I need the color of the data in the displayed cells for this user to be different than the actual color(s) of the underlying fonts. Short of writing some macros, which I can do, is there a system setting that can help? He's using Office 2000 on Windows XP. Thanks. David Walker |
| 832 | Hi Guys I'v got a xls and it just hangs with 100% cpu usage. It looks like its looping forever. I tried to disable all macros but it complains saying this workbook contains excel 4 macros which cannot be disabled. I even tried pressing shift when opening the xls but no luck. Please help cheers Ted |
| 835 | Hi Gianfranco, (posted with email copy) Add-ins, Private Subs, and User Defined Functions to not appear in the macro list. Was that the only problem, or are they also not working. Email copy sent, if you wish to continue this question please keep conversation in this thread within the newsgroup. You are connected directly to the newsgroups using Microsoft CDO there have been recent problems but they are fixed on Microsoft's side. I would suggest that Outlook Express is a better vehicle for reading newsgroups than anything web based. microsoft.public.excel no longer exists on microsoft servers, the correct newsgroup is microsoft.public.excel.misc HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Gianfranco Pellacani" <gianfranco_pellacani@NOSPAMcomergroup.com wrote in message news:ec6901c21c3b$41462740$95e62ecf@tkmsftngxs02... Hi, Itried creating an add-in tohave some Macros and functions of my own available on any workbook, but Ican't seem to be able access the macros and funs on the add-in I create after I install it with the add-in manager. I can access the code with the VB Editor, but can't find neither macros' name on the "run macro" menu nor functions' name in the insert "function menu" Guess there's something wrong with the code itself, but I can't figure out what exactly. Please mail me an answer directly, since I got problem accessing newsgroups. In case you do, please remove "NOSPAM" from address. Thanks a lot |
| 836 | Hi Dave, I think I may have made it unclear what I am trying achieve. I would like any old Excel developer to call an existing macro in an excel add-in I have written, passing parameters to it without the need to create a Macro themselves. The way I perceived it would work is by the developer dragging a button from the 'Forms' toolbar (which I wrongly called a control) and then right-button mouse clicking on the button and selecting the 'Assign Macro...' dialog, and from there just typing in something like: 'MyAddin.xla'!'MyMacro "param1","param2"' without worrying about any VBA code. Sorry I confused things with the example I gave.. Thanks again, Steve "Dave Peterson" <ec35720@msn.com wrote in message news:106f001c21d1d$b90eb0c0$9ee62ecf@tkmsftngxa05... I posted this through google, but it seemed to crash. So I thought I'd use CDO: I _think_ I understand: I dropped a button from the forms toolbar onto a worksheet. I have 3 examples of the .onaction stuff. Comment out two of them. Option Explicit Sub testme() Dim cb As Button Set cb = ActiveSheet.Buttons(1) Dim c As Range Set c = Range("A1") c.Value = "rangeA1value" Dim i As Long i = 8 With cb .OnAction = ThisWorkbook.Name _ & "!'testme1 " & Chr(34) & c.Value & Chr(34) & "'" .OnAction = ThisWorkbook.Name _ & "!'testme2 ""Hello"", 3'" .OnAction = Workbooks("book2.xls").Name _ & "!'testme3 " & Chr(34) _ & "different wkbk" & Chr(34) & "'" End With End Sub Sub testme1(myval As Variant) MsgBox "From Testme1: " & myval End Sub Sub testme2(mystr As String, myval As Long) MsgBox "From Testme2: " & mystr & " " & myval * 10 End Sub and from Book2.xls Option Explicit Sub testme3(mystr As String) MsgBox "From " & ThisWorkbook.Name & ": " & mystr End Sub I've never used the CDO before. Watch out for linewrap problems. -----Original Message----- Thanks Dave, However my problem is not calling it from within a VBA macro but I want an Excel developer who may know nothing about VBA to pass parameters to a VBA macro contained in a loaded Add-in from a button he has added to the spreadsheet. The syntax I used ('MyAddin.xla'!'Foo "param1"') from within the Assign Macro dialog of the excel button control seemed to be ignoring the 'MyAddin.xla' prefix and assuming it was in the workbook containing the button (Book1.xls). Steve What I was hoping for was "Dave Peterson" <ec35720@msn.com wrote in message news:3D18E8DF.4D4DEB54@msn.com... When I tried to pass a string to your Msg sub, I could do it like this: Application.Run "'book1.xla'!msg", "hi there!" I could also do it like this, if I did a Tools|references and set a reference to that other project. msg "hi there2" But I'm not sure if this was the answer to your question.... Steve Legg wrote: Hi, I am able to pass parameters to a VBA macro from the 'Assign Macro' dialog by enclosing in single quotes. e.g. Book1!'Msg "Hello"' to execute the macro in the open workbook 'Book1': Public Sub Msg(m As String) MsgBox m End Sub However I am unable to do the same for a macro in an open Add-in. If I try 'MyAddin.xla'!'Foo "param1"' I am getting an error saying The macro 'Book1'!'Foo "param1"' cannot be found. I can invoke Macros with no parameters in the Add-in. Anybody know why this is not working, and if there is a way to make it work? Thanks in advance. -- Dave Peterson ec35720@msn.com . |
| 842 | Thank you Steven for the information... They also mentioned Lookup......but I can't seem to be able to add that function in. It's an add-in. Thanks.... JR "Steven McDonald" <a@a.com wrote in message news:12a8c01c21d27$b9729720$3aef2ecf@TKMSFTNGXA09... Hi, You don't really need a macro for this. The VLOOKUP commmand should do the job nicely. Just search for vlookup in excel help for all the info you need. It would be easier if your sheet containing part numbers and descriptions was a different sheet in the same workbook, rather than in another workbook. HTH, Steve -----Original Message----- Hello, I have a form template (write-up rev 031902) and need help with the following: In my form I have B14......B29 ...in these cells I put in a part number... and would like the description for that part number to be added into C14....C29 For the description and part number I have created another workbook named "partdesc"......were column A..has the part numbers and they start from Cell 3 down and column B has the description for those part numbers. How can I write a macro for this... Thanks -- JR . |
| 870 | In Excel's Help File Under: Elements you can protect in worksheets and workbooks, it states, . . . When you protect a workbook, you can protect or unprotect the structure of the workbook or workbook windows in the Protect Workbook dialog box (Protection menu, Tools command) by selecting or clearing check boxes for each element. Structure When selected, users are prevented from: . . . Moving or copying worksheets to another workbook. This is NOT TRUE!! I have tried every thing I know to get this to work and I can copy and paste worksheets to a new workbook and the password protection is gone. Anybody can unhide, see and change my data! This is NOT GOOD! Any Ideas? |
| 898 | Thanks for the help...I am going to give this a whirl and=20 see what I can make happen! =20 Scott -----Original Message----- Scott, Expletive deleted. Take your original formula and paste into any cell. ANY=20 CELL. Any cell that does NOT contain data. "Replace the first a1 with the cell that contains a phone=20 number on the MasterList (E5?)." "The formula should be on the MasterList sheet in the=20 column you want to use as a flag." I'm assuming the first telephone number is in E5 and your=20 match formula is in F5. If it is not in E5 then type in the actual=20 location. Yes. Both sheets need to be open. If you have two=20 different workbooks they both have to be open. John "Scott A. Jones" <sajones@rhtc.net wrote in message news:1380701c21d22$cdab89e0$35ef2ecf@TKMSFTNGXA11... Hey! Thanks for the help, but I am still a bit=20 confused. I am guessing when you say ,"Replace the first a1 with=20 the cell that contains a phone number on the MasterList (E5?)." You mean to put the match function there? That is confusing me a bit. Also, am I understanding correctly in believing that both sheets=20 need to be open for the function to work? Should I have one=20 as Sheet one, and the other as sheet 2, or what? What I tried was what you said, but I put the match function line in e1, and did a fill down, but that of course wiped out all my phone numbers, so that doesn't seem to be right...:) Anyway, than you for your help,=20 and any more help you can offer is greatly appreciated. Scott A. Jones -----Original Message----- Hi Scott, =3Dif(iserror(match(a1,donotcall!$a$1:$a$999,0)),"ok to call","do not call") Looks like an example. You just need to modify it. The formula should be on the MasterList sheet in the column you want to use as a flag. Replace the first a1 with the cell that contains a=20 phone number on the MasterList (E5?). "donotcall!" is the name of a sheet. Change it to "DoNotCallList" without the quotes. The "!" is required at the end of the sheetname. $a$1:$a$999 is where it is looking for a phone number match and probably should be $e$1:$e$999 Once you get it working select the cell and drag the mouse down the column. Do an Editfill down John "Scott A. Jones" <sajones@rhtc.net wrote in message news:12b8201c21b8c$d4073a10$35ef2ecf@TKMSFTNGXA11... Hi! I have posted about this problem once before and got some very useful information as a reply, but I am=20 still having trouble with making it work. As I am not an experienced Excel User, I am still trying to learn=20 how all the functions work. Anyway, here's the problem. I have 2 files. They are called: MasterList DoNotCallList The Master List is composed of a list of names and=20 phone numbers. The DoNotCallList is comprised of the same. The Names in the Lists start in Column A, Row 5. The Numbers in the Lists start in Column E, Row 5. All the Rows above are filled with headers and titles for the lists. =20 Both of the lists have about 110 names and numbers in them, though this may vary. What I want to do is use the Match Function to=20 compare the DoNotCallList to the Master List, and to either flag=20 or delete any matching numbers. The match will be done usi=CD{=9Dw=C0=1F=BC=03=DCy=F0p=EC<=F1pO=DA4=18 =EC=05ng the phone number, in an xxx-xxx-xxxx format. I have tried many different variations on the Match Function line=20 to compare the two, but after I apply the AutoFilter, it doesn't flag or delete any of the names. To test, I made sure the fist 10 numbers in each list were=20 duplicates. I suppose what the trouble is that I am having is=20 getting the Match Function to compare the two files, and not just matching something within the same list. So, what I need help on, is trying to determine the exact line that I would need to use to compare the two=20 files, and how I go about applying the line to the lists,=20 and how to start the filter once it is applied. I am very close, but there must be something I am doing wrong. The line I am using a variation on was originally provided to me as a reply to my first message. It is as=20 follows: =3Dif(iserror(match(a1,donotcall!$a$1:$a$999,0)),"ok to call","do not call") If anyone can help me figure out the exact changes I would need to make to this line to make it work with my files, I would be most appreciative. Thanks! Scott A. Jones . . . |
| 926 | General module - tried to find ThisWorkbook module but have been unable to..... Could you point me in the right direction? Thanks. -----Original Message----- It worked ok for me. Did you put the code under ThisWorkbook module or in a General module? It's an easy mistake to make... Rob Pearce wrote: Otto, Nothing happens at all!! Thanks anyway, Rob. -----Original Message----- Rob I'm assuming that your workbook has only one sheet. If that is not the case, then add code to first select the pertinent sheet. I also added code to scroll the cell containing today's date to the top left of the display. Put this macro in your Workbook module. HTH Otto Private Sub Workbook_Open() Range("A:A").Find(What:=Date).Select With ActiveWindow .ScrollRow = ActiveCell.Row .ScrollColumn = ActiveCell.Column End With End Sub "Rob Pearce" <rob.pearce@consignia.com wrote in message news:123a201c21c5b$d54f7070$19ef2ecf@tkmsftngxa01... I have a worksheet that I use for a diary. In column A I have dates going on for the next 3 years. How can I get the workbook to open up on todays date everytime I open it up? I presume some kind of code in the auto_open sub. [Platform 2000] Thanks, Rob. . -- Dave Peterson ec35720@msn.com . |
| 936 | Hi Gene, There really isn't any recommended size limitation that I'm aware of, and the decision whether or not to use Access is much more dependent on the structure of the data and what you're trying to do with it than how big it is. The size of workbook that you can handle in Excel is limited only by your available memory. I've got an application in production that routinely generate 25MB+ workbooks, and since the client runs it on reasonably beefy computers they don't have any trouble with these workbooks at all. They also wouldn't be a candidate for Access because they rely heavily on Excel's calculation engine. If you've got a workbook that contains mostly plain data in tabular format and you want to look at various subsets of that data with minimal calculation needs, that workbook would be a good candidate for Access. -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals / * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "Gene" <eugene.golden@motorola.com wrote in message news:1314e01c21e02$0d3ee9f0$19ef2ecf@tkmsftngxa01... Can someone please tell me what the recommended size limit is for Excel. Another words, when an Excel spread sheet is around xx mb in size you should either migrate to Access or bust up the spread sheet. Thanks |
| 937 | Hi, How do I create a list of all the worksheets that I have in a workbook. Ideally I would like to do something like =worksheetname(4) and have the formula return the name of the 4th sheet in the workbook. Thanks. |
| 940 | Help Is there a way to quickly apply a 'password to open' to lots of excel workbooks? TIA Steve email to steve@pctamers.co.uk |
| 942 | Gene, There isn't any "magic number" below which all is right with the world, and above which all hell will break loose. Moreover, there isn't even a good "range" to use, because it depends to a very large degree on what it is that makes up the size. For example, you can have a huge workbook consisting of primarily data, with relatively few formulas, that will work fine. You can also have a workbook of exactly the same file size, with a large proportion of formulas, that will not operate "acceptably" (usually in terms of recalculation time). Likewise, having a very large number of cell formats (every combination of font, font size, font color, background color, italics, bold, etc) can lead corruption of the file. Excel claims that it supports up to 4000, but I've run into corruption problems (in very complicated workbooks) when the number is around 2000 (client's penchant for formatting, not mine). Depending on the specific application, you may well find it useful to create a "hybrid" application of Access (or, better, SQL Server) for data storage and Excel for a calculation machine and front end. Access or another DB system is good for storage of very large amounts of data (Excel isn't that great at that), but databases are generally lousy at complex calculations, something that Excel is extremely good at. You may find it useful to take advantage of the strengths of both Excel and a DB. You can use Excel as the front end, and Access or SQL Server as the back end. Of course, this may require a lot of code, but there are ways to make all that work quite well. Your question is a good one. Unfortunately, there is not a good answer. It is sort of like asking "When I am too fat?". If you're single, probably now. If you are married, well maybe not yet. If you want a simple "rule of thumb", I wouldn't generally worry if the workbook file size is less than say 10-15 MB (assuming you are using a fairly new PC -- all bets are off if you have less than 64 MB RAM or a CPU less than, say, 600 MHz). When you get up around 15+ MB, it might be time to rethink the basic architecture of the application. But again, it depends greatly on specifically what it is that makes up that 15 MB. I guess, the real answer is "it depends". I'm willing to commit to that. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "Gene" <eugene.golden@motorola.com wrote in message news:1314e01c21e02$0d3ee9f0$19ef2ecf@tkmsftngxa01... Can someone please tell me what the recommended size limit is for Excel. Another words, when an Excel spread sheet is around xx mb in size you should either migrate to Access or bust up the spread sheet. Thanks |
| 944 | Function sheetname(a As Integer) sheetname = Sheets(a).Name End Function if you put this in a normal module you can use it like this =sheetname(3) Regards Ron "Salman" <sahmadmit@hotmail.com schreef in bericht news:12e5701c21e04$8f97fc00$9be62ecf@tkmsftngxa03... Hi, How do I create a list of all the worksheets that I have in a workbook. Ideally I would like to do something like =worksheetname(4) and have the formula return the name of the 4th sheet in the workbook. Thanks. |
| 945 | Salman, Use the Worksheets collection. Dim nameVar As String nameVar = Worksheets(4).Name If you want to include graphs and such, use the Sheets collection. HTH, Bernie "Salman" <sahmadmit@hotmail.com wrote in message news:12e5701c21e04$8f97fc00$9be62ecf@tkmsftngxa03... Hi, How do I create a list of all the worksheets that I have in a workbook. Ideally I would like to do something like =worksheetname(4) and have the formula return the name of the 4th sheet in the workbook. Thanks. |
| 946 | password protect the folder? "Stephen Simons" <steve@pctamers.co.uk wrote in message news:3d1b54ba.121703244@news.btinternet.com... : Help : : Is there a way to quickly apply a 'password to open' to lots of : excel workbooks? : : TIA : : Steve : email to steve@pctamers.co.uk |
| 949 | You can't do this with a simple formula. You'll need to write some VBA to do the job. Put the following function in a standard code module (not ThisWorkbook or one of the Sheet modules). '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Function SheetNames(Optional Index As Long = 0) As Variant Dim Arr() As String Dim Ndx As Long With Application.Caller.Worksheet.Parent.Worksheets If Index = 0 Then ReDim Arr(1 To Application.Caller.Cells.Count) For Ndx = 1 To Application.Min(.Count, UBound(Arr)) Arr(Ndx) = .Item(Ndx).Name Next Ndx If Application.Caller.Rows.Count 1 Then SheetNames = Application.Transpose(Arr) Else SheetNames = Arr End If Else If (Index < 0) Or (Index .Count) Then SheetNames = CVErr(xlErrValue) Else SheetNames = .Item(Index).Name End If End If End With End Function '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' You can call this function directly from a worksheet cell as either a simple formula or as an array formula. For example, if you enter =SheetNames(3) it will return the name of the 3rd worksheet. To return a list of all the sheets in the workbook, first select the range of cells (all in a single row or a single column), type =SheetNames(0) or =SheetNames() and press Ctrl+Shift+Enter rather than just Enter. The enters the formulas as what is called an "array formula" and the sheet names will be listed in those cells. If you array-enter the formula in to more cells than there are worksheets, the first N cells will have the sheet names, and the remaining cells will be blank. If you array-enter the formula into fewer cells that there are worksheets, then only the first N sheets will be listed. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "Salman" <sahmadmit@hotmail.com wrote in message news:12e5701c21e04$8f97fc00$9be62ecf@tkmsftngxa03... Hi, How do I create a list of all the worksheets that I have in a workbook. Ideally I would like to do something like =worksheetname(4) and have the formula return the name of the 4th sheet in the workbook. Thanks. |
| 950 | Please ignore this - I thought I was in the .programming group. Bernie "Bernie Deitrick" <deitbe@consumer.org wrote in message news:uKOiahgHCHA.2988@tkmsftngp12... Salman, Use the Worksheets collection. Dim nameVar As String nameVar = Worksheets(4).Name If you want to include graphs and such, use the Sheets collection. HTH, Bernie "Salman" <sahmadmit@hotmail.com wrote in message news:12e5701c21e04$8f97fc00$9be62ecf@tkmsftngxa03... Hi, How do I create a list of all the worksheets that I have in a workbook. Ideally I would like to do something like =worksheetname(4) and have the formula return the name of the 4th sheet in the workbook. Thanks. |
| 963 | To close a file when you know the name of the file: Application.Workbooks("book2test.xls").Close To close Excel: Application.Quit HTH Jeff -----Original Message----- Trying to do a macro that simply does a File/Close as part of a larger macro, even the recorder does not seem to want to record a line of VB code that will accomplish this function. After attempting to record a File/Close the macro comes up empty. (I am NOT a VB expert - a newbie at best) Is there an easy way to do this or is there a reason that even Excel's macro recorder will not record the menu selections as part of its code? . |
| 970 | One way to get there. Open your workbook in Excel. hit alt-F11 (this gets you to the VBE) hit ctrl-F4 (this displays the project explorer--no harm if it's already shown) You should see something that looks like VBAProject (Yourworkbookfilenamehere.xls) There's either a box with a + or - directly in front of this. If it's a +, then click on it to fan out its subordinates You should see an item named "microsoft Excel Objects" (expand this one if necessary) You should see an object for each worksheet and one named ThisWorkbook. Doubleclick on that one and you're there. (Code window should open on right hand side.) ========= Alternative way. Make the workbook less than fullscreen inside excel. (Window|Arrange|Tiled will work) Right click on that workbook's title bar and select view code. You're there. Good luck, Rob Pearce wrote: General module - tried to find ThisWorkbook module but have been unable to..... Could you point me in the right direction? Thanks. -----Original Message----- It worked ok for me. Did you put the code under ThisWorkbook module or in a General module? It's an easy mistake to make... Rob Pearce wrote: Otto, Nothing happens at all!! Thanks anyway, Rob. -----Original Message----- Rob I'm assuming that your workbook has only one sheet. If that is not the case, then add code to first select the pertinent sheet. I also added code to scroll the cell containing today's date to the top left of the display. Put this macro in your Workbook module. HTH Otto Private Sub Workbook_Open() Range("A:A").Find(What:=Date).Select With ActiveWindow .ScrollRow = ActiveCell.Row .ScrollColumn = ActiveCell.Column End With End Sub "Rob Pearce" <rob.pearce@consignia.com wrote in message news:123a201c21c5b$d54f7070$19ef2ecf@tkmsftngxa01... I have a worksheet that I use for a diary. In column A I have dates going on for the next 3 years. How can I get the workbook to open up on todays date everytime I open it up? I presume some kind of code in the auto_open sub. [Platform 2000] Thanks, Rob. . -- Dave Peterson ec35720@msn.com . -- Dave Peterson ec35720@msn.com |
| 973 | To insert the name of the current worksheet, try: =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND ("]",CELL("filename",A1))) Note: workbook must be saved to work. HTH Jason Atlanta, GA -----Original Message----- Is is possible to insert the name of a worksheet into a cell (as a function)? TIA Zorro . |
| 974 | This sounds contradictory to me, you don't want the today function because it changes with the computer's date but you don't want to use ctrl+; because it doesn't change with the computer's date? Sub InsertDate() Range("A1").Value = Date End Sub will insert today's date in A1 which will be the same as doing ctrl+; -- Regards, Peo Sjoblom <ski_34_4@hotmail.com wrote in message news:135f501c21ea7$3abdbc80$3aef2ecf@TKMSFTNGXA09... I am looking for a function to insert into a macro that is similair to TODAY which enters todays date but I would like one that keeps that date. For example if I insert a date today 6/28/02 and I open the workbook tomorrow that date will still have 6/28/02 where I inserted it previously. I tried ctl+; which inserts the todays date but it enters it as a string so 6/28/02 will be there everyday wich is not what I want. Can anyone help me? Maybe there is a VBA statement that will allow this? |
| 975 | How do you open different workbooks in different windows like Word. I want to be able to double click the workbooks I want in explorer and have each open in it's own window. |
| 977 | Brilliant! Just what I needed. Many thanks. Zorro "Jason Morin" <jason.morin@us.exel.com wrote in message news:115f601c21ea9$c8a24820$36ef2ecf@tkmsftngxa12... To insert the name of the current worksheet, try: =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND ("]",CELL("filename",A1))) Note: workbook must be saved to work. HTH Jason Atlanta, GA -----Original Message----- Is is possible to insert the name of a worksheet into a cell (as a function)? TIA Zorro . |
| 979 | What I am trying to do is create a page the keep track of purchase orders so there is many dates involed. Each PO is represented on a worksheet. I want a macro that will insert the date of when that current sheet was created, and will stay that way, it will not change to the current date everyday like the TODAY function does. does this help clear anything up? -----Original Message----- I, for one, am confused by *****I would like one that keeps that date****** and *****so 6/28/02 will be there everyday wich is not what I want. ****** ???? <ski_34_4@hotmail.com wrote in message news:135f501c21ea7$3abdbc80$3aef2ecf@TKMSFTNGXA09... I am looking for a function to insert into a macro that is similair to TODAY which enters todays date but I would like one that keeps that date. For example if I insert a date today 6/28/02 and I open the workbook tomorrow that date will still have 6/28/02 where I inserted it previously. I tried ctl+; which inserts the todays date but it enters it as a string so 6/28/02 will be there everyday wich is not what I want. Can anyone help me? Maybe there is a VBA statement that will allow this? . |
| 980 | Hi, Warning: if your workbook contains dates, setting the date system to 1904 will change all of them by some 4 years! So be careful with this option. Could you send me some of those negative times? I'm short in time at the moment. A day or two should do it <vbg. Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- I want to subtract hours and minutes from each other but when the answer is less than zero it won't give an answer please help. . |
| 981 | Just what I needed, thanks. Zorro "Jason Morin" <jason.morin@us.exel.com wrote in message news:115f601c21ea9$c8a24820$36ef2ecf@tkmsftngxa12... To insert the name of the current worksheet, try: =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND ("]",CELL("filename",A1))) Note: workbook must be saved to work. HTH Jason Atlanta, GA -----Original Message----- Is is possible to insert the name of a worksheet into a cell (as a function)? TIA Zorro . |
| 990 | I have created a macro that inserts a new worksheet, copys the template and pastes it to the new worksheet. However the problem I am having is that I want to move this worksheet to the end of the workbook in the macro. You can write click on the worksheet and slect move to end, but that just references the current last sheet. As soon as you add another worksheet your not at the end anymore. Can nayone help? |
| 995 | Here is something I found in the archives. Sub NewSheet() Dim myDate as string myDate = Date '==== Worksheets.Add.Move after:=Worksheets(Worksheets.Count) '====== Sheets(Worksheets.count).name = myDate Worksheets("ExistingWorksheet").Range("myArea").copy Sheets(Worksheets.count).Range("A1").pastespecial xlpastevalues End Sub -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "dflannery" <dflannery34@hotmail.com wrote in message news:1377d01c21eb0$e94bc6d0$39ef2ecf@TKMSFTNGXA08... I have created a macro that inserts a new worksheet, copys the template and pastes it to the new worksheet. However the problem I am having is that I want to move this worksheet to the end of the workbook in the macro. You can write click on the worksheet and slect move to end, but that just references the current last sheet. As soon as you add another worksheet your not at the end anymore. Can nayone help? |
| 998 | This should do it for you Sub InsertMove() Dim wks As Worksheet Dim i As Integer Sheets.Add i = Worksheets.Count Set wks = ActiveSheet wks.Move After:=Sheets(i) End Sub HTH "dflannery" <dflannery34@hotmail.com wrote in message news:1377d01c21eb0$e94bc6d0$39ef2ecf@TKMSFTNGXA08... I have created a macro that inserts a new worksheet, copys the template and pastes it to the new worksheet. However the problem I am having is that I want to move this worksheet to the end of the workbook in the macro. You can write click on the worksheet and slect move to end, but that just references the current last sheet. As soon as you add another worksheet your not at the end anymore. Can nayone help? |
| 1000 | Hello, My problem is very weird. I have entered a formula into a cell. i.e. =IF(ISERROR(VLOOKUP(R21,'Hours'!$Y$4:$AA$1089,3,0)),"0",(VL OOKUP(R21,'Work'!$Y$4:$AA$1089,3,0))) However, when i press enter, instead of the cell showing the number or zero, it displays =IF(ISERROR(V This makes all my other pages not work. can there be a limit on how many formulas you can use in a sheet or workbook?? please help me. Thank you in advance. -Tony |
| 1003 | I think i figured it out. i had the formula in text format. But if i'm wrong, please do tell me. Thanks again. Tony -----Original Message----- Hello, My problem is very weird. I have entered a formula into a cell. i.e. =IF(ISERROR(VLOOKUP(R21,'Hours'!$Y$4:$AA$1089,3,0)),"0",(V L OOKUP(R21,'Work'!$Y$4:$AA$1089,3,0))) However, when i press enter, instead of the cell showing the number or zero, it displays =IF(ISERROR(V This makes all my other pages not work. can there be a limit on how many formulas you can use in a sheet or workbook?? please help me. Thank you in advance. -Tony . |
| 1006 | Hi all, Is there a way to programmatically (via automation) to undo all changes in excel workbook since it was last opened? (Closing it and opening again is not good, since it's quite large). |
| 1009 | Something like this maybe? Private Sub Workbook_NewSheet(ByVal Sh As Object) Range("A1").Activate ActiveCell.Value = Now ActiveCell.NumberFormat = "dddd, mmmm dd, yyyy" End Sub Jon dflannery wrote: What I am trying to do is create a page the keep track of purchase orders so there is many dates involed. Each PO is represented on a worksheet. I want a macro that will insert the date of when that current sheet was created, and will stay that way, it will not change to the current date everyday like the TODAY function does. does this help clear anything up? -----Original Message----- I, for one, am confused by *****I would like one that keeps that date****** and *****so 6/28/02 will be there everyday wich is not what I want. ****** ???? <ski_34_4@hotmail.com wrote in message news:135f501c21ea7$3abdbc80$3aef2ecf@TKMSFTNGXA09... I am looking for a function to insert into a macro that is similair to TODAY which enters todays date but I would like one that keeps that date. For example if I insert a date today 6/28/02 and I open the workbook tomorrow that date will still have 6/28/02 where I inserted it previously. I tried ctl+; which inserts the todays date but it enters it as a string so 6/28/02 will be there everyday wich is not what I want. Can anyone help me? Maybe there is a VBA statement that will allow this? . |
| 1025 | Or maybe this? This will enter the date and the sheetname when a new worksheet has been created. Private Sub Workbook_NewSheet(ByVal Sh As Object) Sheet1.Activate ' a summary sheet of the PO's Range("A1").End(xlDown).Offset(1, 0).Activate ActiveCell.Value = Now ActiveCell.NumberFormat = "dddd, mmmm dd, yyyy" ActiveCell.Offset(0, 1).Value = Sh.Name End Sub Jon Crash wrote: Something like this maybe? Private Sub Workbook_NewSheet(ByVal Sh As Object) Range("A1").Activate ActiveCell.Value = Now ActiveCell.NumberFormat = "dddd, mmmm dd, yyyy" End Sub Jon dflannery wrote: What I am trying to do is create a page the keep track of purchase orders so there is many dates involed. Each PO is represented on a worksheet. I want a macro that will insert the date of when that current sheet was created, and will stay that way, it will not change to the current date everyday like the TODAY function does. does this help clear anything up? -----Original Message----- I, for one, am confused by *****I would like one that keeps that date****** and *****so 6/28/02 will be there everyday wich is not what I want. ****** ???? <ski_34_4@hotmail.com wrote in message news:135f501c21ea7$3abdbc80$3aef2ecf@TKMSFTNGXA09... I am looking for a function to insert into a macro that is similair to TODAY which enters todays date but I would like one that keeps that date. For example if I insert a date today 6/28/02 and I open the workbook tomorrow that date will still have 6/28/02 where I inserted it previously. I tried ctl+; which inserts the todays date but it enters it as a string so 6/28/02 will be there everyday wich is not what I want. Can anyone help me? Maybe there is a VBA statement that will allow this? . |
| 1048 | Wilson I need to get smarter to figure out how this works. It appears that your code clears the contents of every cell whose name is in the names range. I don't see how your code deletes the names from the workbook. Thanks for your help. Otto "Wilson" <jwilson@wickes.com wrote in message news:uOBIb7rHCHA.1696@tkmsftngp09... With the names to get rid of llisted on another sheet Sub NamesCleaner() Dim Nam As Variant Dim NamRng As Range Dim cell As Range Set NamRng = Worksheets("Name Sheet").Range("A2:A5") For Each Nam In NamRng For Each cell In ActiveSheet.UsedRange If cell = Nam Then cell.ClearContents End If Next cell Next Nam End Sub HTH "B. Teele" <bteele@pranainvestments.com wrote in message news:1174701c21ebd$14d4d560$a4e62ecf@tkmsftngxa06... Is there a way get rid of numerous names in our worksheets (other than by deleting them one by one)? |
| 1051 | You could use a Workbook_BeforePrint sub in ThisWorkbook module with Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveWindow.DisplayZeros = False End Sub and then turn them back on with F9 and Private Sub Workbook_SheetCalculate(ByVal Sh As Object) ActiveWindow.DisplayZeros = True End Sub HTH "Jackie" <genmaicha@aol.com wrote in message news:1184d01c21ed5$7ec4a8c0$2ae2c90a@hosting.microsoft.com... I have been recently been converting really, really, old versions of Lotus files into Excel 2000 workbooks. In doing so I have had to modify formulas etc. There is a formula in Lotus that says if a cell equals zero, then do not print that row/column (or the designated range of cells). I have not been able to reproduce this in Excel. Is there a way to supress zeros when printing an Excel file? |
| 1054 | Hi Otto, I've read your postings and you don't have any need to "get smarter". Maybe I did not understand what the OP wants? I really wish they would all post back and say either "thanks, that works fine" or "hey, stupid, that's not what I wanted". I assumed the OP has a list of names they want removed from one or more worksheets and they want to save time with Excel clearing them, rather than searching and deleting them all manually. If I'm wrong, it won't be a first. Best Regards, "Otto Moehrbach" <ottom@worldnet.att.net wrote in message news:u$3fEUtHCHA.2480@tkmsftngp11... Wilson I need to get smarter to figure out how this works. It appears that your code clears the contents of every cell whose name is in the names range. I don't see how your code deletes the names from the workbook. Thanks for your help. Otto "Wilson" <jwilson@wickes.com wrote in message news:uOBIb7rHCHA.1696@tkmsftngp09... With the names to get rid of llisted on another sheet Sub NamesCleaner() Dim Nam As Variant Dim NamRng As Range Dim cell As Range Set NamRng = Worksheets("Name Sheet").Range("A2:A5") For Each Nam In NamRng For Each cell In ActiveSheet.UsedRange If cell = Nam Then cell.ClearContents End If Next cell Next Nam End Sub HTH "B. Teele" <bteele@pranainvestments.com wrote in message news:1174701c21ebd$14d4d560$a4e62ecf@tkmsftngxa06... Is there a way get rid of numerous names in our worksheets (other than by deleting them one by one)? |
| 1067 | Attach the toolbar to the workbook from the Customize dialog. Note that if you make changes to the toolbar after attaching it, you will need to delete the old version and attach the new one. HTH, Greg "Liz" <liz@techie.com wrote in message news:1137801c21ec9$4108f100$9ee62ecf@tkmsftngxa05... I have a spreadsheet that has a custom toolbar and several buttons on it which are assigned to run macros for that particular spreadsheet. If I take the same spreadsheet and open it an a different machine, the toolbar is not in the list of available toolbars. I don't want to have re- create it each time. How can I get the toolbar to be recognized, even if I need to use this spreadsheet on several different machines? |
| 1076 | Joe Try this syntax Result = Mid(ActiveWorkbook.Names("switch").Value,2) ' To get rid of the equation sign If Result = 1 Then....... -- Best regards Leo Heuser MVP Excel "Joe" <joe.williams@verizon.com skrev i en meddelelse news:1361401c21ee2$57101870$9be62ecf@tkmsftngxa03... I've defined a name in a worksheet. It doesn't refer to any cell. Let's say the name is "switch". How do I use its value in VBA code? I tried: If switch=1 then ... but I got an error. |
| 1081 | Hi Wilson I interpreted the OPs question (a little unclear) differently to you. My interpretation is that there are names in the workbook that need to be deleted. Using your example of there being a list of names to be deleted on the current sheet, this would delete all names in the workbook within that list. But my interpretation may well be wrong. Dim nm As Name, r As Range, c As Range With ThisWorkbook Set r = Range("A1:A50") 'Amend as necessary For Each c In r Names(c.Text).Delete Next c End With -- Regards William willwest22@yahoo.com "Wilson" <jwilson@wickes.com wrote in message news:#iOD5jtHCHA.2480@tkmsftngp11... | Hi Otto, | I've read your postings and you don't have any need to "get smarter". Maybe | I did not understand what the OP wants? I really wish they would all post | back and say either "thanks, that works fine" or "hey, stupid, that's not | what I wanted". I assumed the OP has a list of names they want removed from | one or more worksheets and they want to save time with Excel clearing them, | rather than searching and deleting them all manually. If I'm wrong, it | won't be a first. | Best Regards, | "Otto Moehrbach" <ottom@worldnet.att.net wrote in message | news:u$3fEUtHCHA.2480@tkmsftngp11... | Wilson | I need to get smarter to figure out how this works. It appears that | your code clears the contents of every cell whose name is in the names | range. I don't see how your code deletes the names from the workbook. | Thanks for your help. Otto | "Wilson" <jwilson@wickes.com wrote in message | news:uOBIb7rHCHA.1696@tkmsftngp09... | With the names to get rid of llisted on another sheet | Sub NamesCleaner() | Dim Nam As Variant | Dim NamRng As Range | Dim cell As Range | Set NamRng = Worksheets("Name Sheet").Range("A2:A5") | For Each Nam In NamRng | For Each cell In ActiveSheet.UsedRange | If cell = Nam Then | cell.ClearContents | End If | Next cell | Next Nam | End Sub | HTH | "B. Teele" <bteele@pranainvestments.com wrote in message | news:1174701c21ebd$14d4d560$a4e62ecf@tkmsftngxa06... | Is there a way get rid of numerous names in our worksheets | (other than by deleting them one by one)? | | | | | | |
| 1084 | i had thought the this command would hide the toolbar "fixture schedule" when i switched to a different workbook, (without closing the current) but it does not Sub Workbook_WindowDeactivate() Application.CommandBars("Fixture Schedule").Visible = False End Sub suggestions? |
| 1085 | I don't think so in general. I think that the level of undo's can be tweaked in the registry, but then the user could still exceed that number. I think it would be easier to keep track of the fields that the user could change and then reset them--0's, empty, etc. Grisha Golberg wrote: Hi all, Is there a way to programmatically (via automation) to undo all changes in excel workbook since it was last opened? (Closing it and opening again is not good, since it's quite large). -- Dave Peterson ec35720@msn.com |
| 1087 | This will kill all of them. Dim myName As Name For Each myName In ActiveWorkbook.Names myName.Delete Next myName But be careful. There are a lot of built in names that excel uses. You may want to ask a question first. Dim myName As Name For Each myName In ActiveWorkbook.Names If MsgBox("delete: " & myName.Name & "--" & _ myName.RefersTo, vbYesNo) = vbYes Then myName.Delete End If Next myName "B. Teele" wrote: Is there a way get rid of numerous names in our worksheets (other than by deleting them one by one)? -- Dave Peterson ec35720@msn.com |
| 1091 | Hi All I have a workbook that contains similar worksheets of data. Is there an easy way of taking two worksheets, original and new and running a function that will compare the two, possibly giving the results in a new worksheet? I would like to have one result which will display new rows in the new sheet that do not appear in the original, and also another result that will show rows where data has changed. There must be an easier way than simply visually comparing each row one by one! I hope this explains what I want to do with these sheets. Any help is much appreciated. Many Thanks in Advance! Matt |
| 1092 | Hi Matt Myrna Larson & Bill Manville has a free addin at /excel/download.htm called "Compare". It should do what you want. This method assumes that the sheets are similar. Insert one extra row in the middle of one of them and they suddenly become very very different. HTH. Best wishes Harald "Matt" <matt@slippednospampleasedisk.fsnet.co.uk wrote in message news:afjoak$fag$1@news6.svr.pol.co.uk... Hi All I have a workbook that contains similar worksheets of data. Is there an easy way of taking two worksheets, original and new and running a function that will compare the two, possibly giving the results in a new worksheet? I would like to have one result which will display new rows in the new sheet that do not appear in the original, and also another result that will show rows where data has changed. There must be an easier way than simply visually comparing each row one by one! I hope this explains what I want to do with these sheets. Any help is much appreciated. Many Thanks in Advance! Matt |
| 1093 | That should be doable. Since you don't define what makes a row the same or different or how to compare between sheets or how your data is laid out, it would be difficult to provide any additional information. Matt <matt@slippednospampleasedisk.fsnet.co.uk wrote in message news:afjoak$fag$1@news6.svr.pol.co.uk... Hi All I have a workbook that contains similar worksheets of data. Is there an easy way of taking two worksheets, original and new and running a function that will compare the two, possibly giving the results in a new worksheet? I would like to have one result which will display new rows in the new sheet that do not appear in the original, and also another result that will show rows where data has changed. There must be an easier way than simply visually comparing each row one by one! I hope this explains what I want to do with these sheets. Any help is much appreciated. Many Thanks in Advance! Matt |
| 1094 | Hi Thanks for the replies!!! Well the spreadsheets are from my companies supplier: and are a list of products. From time to time the product prices change, new products appear and some obviously get discontinued. There are over 1500 different products on this speadsheet! The row consists of fields stating "product category", "product code", "description", "price ex VAT", "price Inc VAT", The product code is unique. When I am sent an updated spreadsheet, I want to be able to compare this with the previous, and easily see what products have changed, and what products are new! Hopefully you have the answer! I am looking at the Compare add-in stated by Harald. Each time a new spreadsheet is recieved, the total number of products is likely to be different! Many thanks again in advance Matt "Tom Ogilvy" <twogilvy@msn.com wrote in message news:uDxyeQ3HCHA.1712@tkmsftngp08... That should be doable. Since you don't define what makes a row the same or different or how to compare between sheets or how your data is laid out, it would be difficult to provide any additional information. Regards, Tom Ogilvy Matt <matt@slippednospampleasedisk.fsnet.co.uk wrote in message news:afjoak$fag$1@news6.svr.pol.co.uk... Hi All I have a workbook that contains similar worksheets of data. Is there an easy way of taking two worksheets, original and new and running a function that will compare the two, possibly giving the results in a new worksheet? I would like to have one result which will display new rows in the new sheet that do not appear in the original, and also another result that will show rows where data has changed. There must be an easier way than simply visually comparing each row one by one! I hope this explains what I want to do with these sheets. Any help is much appreciated. Many Thanks in Advance! Matt |
| 1095 | There's a shareware addin called OAK from operis which will do just this. You can get it here /oak.htm The full version is quite expensive though, so you might want to see if theres an addin which can align your sheets (ie see where to add new rows), and then use the compare addin. HTH Ian G "Matt" <matt@slippednospampleasedisk.fsnet.co.uk wrote in message news:afkfkr$a5j$1@news8.svr.pol.co.uk... Hi Thanks for the replies!!! Well the spreadsheets are from my companies supplier: and are a list of products. From time to time the product prices change, new products appear and some obviously get discontinued. There are over 1500 different products on this speadsheet! The row consists of fields stating "product category", "product code", "description", "price ex VAT", "price Inc VAT", The product code is unique. When I am sent an updated spreadsheet, I want to be able to compare this with the previous, and easily see what products have changed, and what products are new! Hopefully you have the answer! I am looking at the Compare add-in stated by Harald. Each time a new spreadsheet is recieved, the total number of products is likely to be different! Many thanks again in advance Matt "Tom Ogilvy" <twogilvy@msn.com wrote in message news:uDxyeQ3HCHA.1712@tkmsftngp08... That should be doable. Since you don't define what makes a row the same or different or how to compare between sheets or how your data is laid out, it would be difficult to provide any additional information. Regards, Tom Ogilvy Matt <matt@slippednospampleasedisk.fsnet.co.uk wrote in message news:afjoak$fag$1@news6.svr.pol.co.uk... Hi All I have a workbook that contains similar worksheets of data. Is there an easy way of taking two worksheets, original and new and running a function that will compare the two, possibly giving the results in a new worksheet? I would like to have one result which will display new rows in the new sheet that do not appear in the original, and also another result that will show rows where data has changed. There must be an easier way than simply visually comparing each row one by one! I hope this explains what I want to do with these sheets. Any help is much appreciated. Many Thanks in Advance! Matt |
| 1096 | The simplest solution might be to just hide the worksheet tabs. The worksheets will still be Visible, but I think most people won't know how to change sheets (ctrl-pageup/ctrl-pagedown will work!). Tools|options|view|sheet tabs will toggle the view. Another solution would be to use the worksheet_beforedoubleclick event. If you format the cell with a blue font and underline it, it might even pass for a hyperlink. Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim wks As Worksheet Dim myCell As Range If Target.Cells.Count 1 Then Exit Sub If Target.Value = "" Then Exit Sub If Target.Column < 5 Then Exit Sub Set wks = Nothing On Error Resume Next Set wks = Worksheets(Target.Value) On Error GoTo 0 If wks Is Nothing Then MsgBox "Worksheet for " & Target.Value & " doesn't exist!" Exit Sub End If wks.Visible = True Application.Goto wks.Range("a1"), scroll:=True End Sub If you like this idea, right click on the index sheet and select view code. Then paste this in. ======= I just tried this and it seemed to work ok. It maybe the simplest solution. It also goes into the worksheet module. (The parsing of the worksheet in this example means that the link has t point to the same workbook.) Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) On Error GoTo errHandler Worksheets(Left(Target.Parent, InStr(1, Target.Parent, "!") - 1)).Visible = True Application.EnableEvents = False Target.Follow errHandler: Application.EnableEvents = True End Sub Phil wrote: I want to hide some worksheets within a workbook and use an index page to hyperlink to the sheets. I have tried it and it works as long as the worksheets are not hidden. But, if I hide the worksheets then the hyperlink doesn't work. I am sure I have seen people do this before but can't figure it out. So, my question is how do you make the hyperlinks work when the worksheets are hidden? Thanks, Phil -- Dave Peterson ec35720@msn.com |
| 1101 | Hi Phil On your index sheet, I presume you have a list of the worksheet names. Place a hyperlink in each cell with a worksheet name in it (as I think you have done already) but reference the hyperlink to that same cell with the sheet name. Then place this code in the Index WorksheetModule. Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Sheets(ActiveCell.Text).Visible = True Sheets(ActiveCell.Text).Activate End Sub -- Regards William REMOVEwillwest22@yahoo.com Remove "REMOVE" when replying "Phil" <rowejunk2@mchsi.com wrote in message news:AgrT8.361757$cQ3.23521@sccrnsc01... | I want to hide some worksheets within a workbook and use an index page to | hyperlink to the sheets. I have tried it and it works as long as the | worksheets are not hidden. But, if I hide the worksheets then the hyperlink | doesn't work. I am sure I have seen people do this before but can't figure | it out. So, my question is how do you make the hyperlinks work when the | worksheets are hidden? | | Thanks, | Phil | | |
| 1102 | Hi Phil On your index sheet, I presume you have a list of the worksheet names. Place a hyperlink in each cell with a worksheet name in it (as I think you have done already) but reference the hyperlink to that same cell with the sheet name. Then place this code in the Index WorksheetModule. Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Sheets(ActiveCell.Text).Visible = True Sheets(ActiveCell.Text).Activate End Sub -- Regards William willwest22@yahoo.com "Phil" <rowejunk2@mchsi.com wrote in message news:AgrT8.361757$cQ3.23521@sccrnsc01... | I want to hide some worksheets within a workbook and use an index page to | hyperlink to the sheets. I have tried it and it works as long as the | worksheets are not hidden. But, if I hide the worksheets then the hyperlink | doesn't work. I am sure I have seen people do this before but can't figure | it out. So, my question is how do you make the hyperlinks work when the | worksheets are hidden? | | Thanks, | Phil | | |
| 1105 | Thanks for the suggestions everyone. I really appreciate them. Phil "William" <REMOVEwillwest22@yahoo.com wrote in message news:Oy8Ve8$HCHA.1744@tkmsftngp13... Hi Phil On your index sheet, I presume you have a list of the worksheet names. Place a hyperlink in each cell with a worksheet name in it (as I think you have done already) but reference the hyperlink to that same cell with the sheet name. Then place this code in the Index WorksheetModule. Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Sheets(ActiveCell.Text).Visible = True Sheets(ActiveCell.Text).Activate End Sub -- Regards William willwest22@yahoo.com "Phil" <rowejunk2@mchsi.com wrote in message news:AgrT8.361757$cQ3.23521@sccrnsc01... | I want to hide some worksheets within a workbook and use an index page to | hyperlink to the sheets. I have tried it and it works as long as the | worksheets are not hidden. But, if I hide the worksheets then the hyperlink | doesn't work. I am sure I have seen people do this before but can't figure | it out. So, my question is how do you make the hyperlinks work when the | worksheets are hidden? | | Thanks, | Phil | | |
| 1107 | Can code in one VBA module create code in another VBA module, as opposed to having a * human * type it in? We want to create a macro in one workbook which replaces the code in a module in another open workbook. |
| 1113 | Take a look at Chip Pearson's web page: /excel/vbe.htm Peter Stallone wrote: Can code in one VBA module create code in another VBA module, as opposed to having a * human * type it in? We want to create a macro in one workbook which replaces the code in a module in another open workbook. -- Dave Peterson ec35720@msn.com |
| 1125 | Wilson I still need to get smarter. Your interpretation of the post is the same as mine, namely that he has some names in his workbook that he wants to get rid of (deleted) and wants a macro to do it for him. My problem with your macro is that it appears that it deletes the contents of all the named cells rather than the names themselves. I've about convinced myself that your macro does it right, but I don't know how. That's where the "getting smarter" part comes in. Otto "Wilson" <jwilson@wickes.com wrote in message news:#iOD5jtHCHA.2480@tkmsftngp11... Hi Otto, I've read your postings and you don't have any need to "get smarter". Maybe I did not understand what the OP wants? I really wish they would all post back and say either "thanks, that works fine" or "hey, stupid, that's not what I wanted". I assumed the OP has a list of names they want removed from one or more worksheets and they want to save time with Excel clearing them, rather than searching and deleting them all manually. If I'm wrong, it won't be a first. Best Regards, "Otto Moehrbach" <ottom@worldnet.att.net wrote in message news:u$3fEUtHCHA.2480@tkmsftngp11... Wilson I need to get smarter to figure out how this works. It appears that your code clears the contents of every cell whose name is in the names range. I don't see how your code deletes the names from the workbook. Thanks for your help. Otto "Wilson" <jwilson@wickes.com wrote in message news:uOBIb7rHCHA.1696@tkmsftngp09... With the names to get rid of llisted on another sheet Sub NamesCleaner() Dim Nam As Variant Dim NamRng As Range Dim cell As Range Set NamRng = Worksheets("Name Sheet").Range("A2:A5") For Each Nam In NamRng For Each cell In ActiveSheet.UsedRange If cell = Nam Then cell.ClearContents End If Next cell Next Nam End Sub HTH "B. Teele" <bteele@pranainvestments.com wrote in message news:1174701c21ebd$14d4d560$a4e62ecf@tkmsftngxa06... Is there a way get rid of numerous names in our worksheets (other than by deleting them one by one)? |
| 1134 | Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1137 | Are these two sheets self contained with each other or are other sheets involved. I've done similar things in the past, copying a worksheet & graph together to make a new set. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:ufA$88FICHA.1772@tkmsftngp09... Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1138 | There's no built-in relative sheet addressing. You would have to accomplish this a VBA macro that copies the sheet, then changes the name definition and/or formulas. On Sun, 30 Jun 2002 18:56:24 +0100, "Robert Brown" <robert@nospam-southall-brown.co.uk wrote: Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1145 | I've spent most of the weekend working on a new workbook. It is mainly arithmetic and custom views at the moment, but I've had to use one of the custom cell formats as there is no option for parentheses round negative numbers. I have Excel 2000. At the office we have Excel 97. What are the major differences I'll notice when I use the new workbook in the office? Thanks Magnus |
| 1146 | Hi Magnus <<< no option for parentheses round negative numbers "FormatCellsNumberCustom" and then enter this format to give you brackets (and red font) for negative numbers. #,##0.00 ;[Red](#,##0.00) Should work in 97 and 2000 -- Regards William willwest22@yahoo.com "Magnus Moose" <magnus@moose-ville.fsnet.co.ku wrote in message news:MPG.17896d8f6982d70d989692@news.freeserve.com... | | I've spent most of the weekend working on | a new workbook. It is mainly arithmetic | and custom views at the moment, but I've had | to use one of the custom cell formats as there | is no option for parentheses round negative numbers. | I have Excel 2000. | | At the office we have Excel 97. | What are the major differences | I'll notice when I use the new workbook | in the office? | | Thanks | Magnus |
| 1154 | Anyway I can get this Macro to work, the first 2 lines work but not the next 2 Lines, TIA/Bob If ActiveCell.Offset(69, 7).Value < 0 Then ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End If End Sub "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:uxuE0e8HCHA.368@tkmsftngp13... and do the same things till 700 change that to 7000 for your 100 pages Sorry for the typo I go to bed. 1:50 it is time I think "Ron de Bruin" <rondebruin@kabelfoon.nl schreef in bericht news:#afHic8HCHA.2984@tkmsftngp13... This is the same Bob this example start in G70 to look if there a value. if it is do the resize z.Resize(64, 8).PrintPreview if you want to start in column a with printing you can use a offset in between like this If z.Value < 0 Then z.Offset(0, -6).Resize(64, 8).PrintPreview then he look in g140 g210 g280 and do the same things till 70 change that to 700 for your 100 pages Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflg98$imt$1@lust.ihug.co.nz... Really need to do it from a activecell as this month 100 sheets below then I copy them over and need to do the same to the next 100 sheets adjacent to the last month Regards Bob Vance " Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#j$5sL8HCHA.2620@tkmsftngp08... Sub trythis() Dim a As Long Dim z As Range For a = 70 To 700 Step 70 Set z = ActiveSheet.Cells(a, 7) If z < 0 Then z.Resize(64, 8).PrintPreview Next a End Sub try this bob works for G70 till G700 Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afldoo$g4k$1@lust.ihug.co.nz... Yes everything is 64 cells down Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#GZCr87HCHA.1060@tkmsftngp11... Can you tell this Is the cell that you want to check on every page on the sheet not on the same distance in rows?? in your example you say 69 and 133. is the certain cel on that page the only cel with a value in that column?? I mean that there are maximum 100 cells in that column with a value with you answers maybe I can help you Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflcrt$fht$1@lust.ihug.co.nz... Yes there is 100 pages on a worksheet, and I want to print the paged below that have number in a certain cell Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:eOm53y7HCHA.1124@tkmsftngp10... Hi Bob please explane more what you are looking for Are you talking about a workbook with one sheet with 100 pages. and you want to print those pages when a cell on that page < 0. Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflbmg$ekq$1@lust.ihug.co.nz... Just cant get this one to work, can you see what im am doing wrong, Trying to do the same task in each sheet below so would end up with about 100 different commands on the same macro If ActiveCell.Offset(69, 7).Value < 0 Then ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End If End Sub Regards Bob Vance "Bob Vance" <rjvance@ihug.co.nz wrote in message news:afl9ts$d70$1@lust.ihug.co.nz... Sub a() If ActiveCell.Offset(4, 3).Value < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).PrintPreview End If End Sub This gave me a compile error, expected end sub Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#HAm0L1HCHA.2680@tkmsftngp12... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" And Range("a4") < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).PrintPreview End If End Sub this is good also one step less "Ron de Bruin" <rondebruin@kabelfoon.nl schreef in bericht news:#e9cxB1HCHA.2060@tkmsftngp11... in vba : put this in the worksheetmodule Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" And Range("a4") < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).Select Selection.PrintPreview End If End Sub change PrintPreview for printout Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afjep5$ldm$1@lust.ihug.co.nz... Can anybody help me with this active cell macro formula. Say if I click in A1 and there is a number in A4 which would also have a formula in it then A3:D7 selection would be printed, Remembering A4 contains a Formula like (=B3) Thanx For Your Help |
| 1165 | Make it a single line if statement. If ActiveCell.Offset(69, 7).Value < 0 Then _ ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then _ ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End Sub Bob Vance <rjvance@ihug.co.nz wrote in message news:afnuoo$3bd$1@lust.ihug.co.nz... Anyway I can get this Macro to work, the first 2 lines work but not the next 2 Lines, TIA/Bob If ActiveCell.Offset(69, 7).Value < 0 Then ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End If End Sub "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:uxuE0e8HCHA.368@tkmsftngp13... and do the same things till 700 change that to 7000 for your 100 pages Sorry for the typo I go to bed. 1:50 it is time I think "Ron de Bruin" <rondebruin@kabelfoon.nl schreef in bericht news:#afHic8HCHA.2984@tkmsftngp13... This is the same Bob this example start in G70 to look if there a value. if it is do the resize z.Resize(64, 8).PrintPreview if you want to start in column a with printing you can use a offset in between like this If z.Value < 0 Then z.Offset(0, -6).Resize(64, 8).PrintPreview then he look in g140 g210 g280 and do the same things till 70 change that to 700 for your 100 pages Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflg98$imt$1@lust.ihug.co.nz... Really need to do it from a activecell as this month 100 sheets below then I copy them over and need to do the same to the next 100 sheets adjacent to the last month Regards Bob Vance " Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#j$5sL8HCHA.2620@tkmsftngp08... Sub trythis() Dim a As Long Dim z As Range For a = 70 To 700 Step 70 Set z = ActiveSheet.Cells(a, 7) If z < 0 Then z.Resize(64, 8).PrintPreview Next a End Sub try this bob works for G70 till G700 Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afldoo$g4k$1@lust.ihug.co.nz... Yes everything is 64 cells down Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#GZCr87HCHA.1060@tkmsftngp11... Can you tell this Is the cell that you want to check on every page on the sheet not on the same distance in rows?? in your example you say 69 and 133. is the certain cel on that page the only cel with a value in that column?? I mean that there are maximum 100 cells in that column with a value with you answers maybe I can help you Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflcrt$fht$1@lust.ihug.co.nz... Yes there is 100 pages on a worksheet, and I want to print the paged below that have number in a certain cell Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:eOm53y7HCHA.1124@tkmsftngp10... Hi Bob please explane more what you are looking for Are you talking about a workbook with one sheet with 100 pages. and you want to print those pages when a cell on that page < 0. Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflbmg$ekq$1@lust.ihug.co.nz... Just cant get this one to work, can you see what im am doing wrong, Trying to do the same task in each sheet below so would end up with about 100 different commands on the same macro If ActiveCell.Offset(69, 7).Value < 0 Then ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End If End Sub Regards Bob Vance "Bob Vance" <rjvance@ihug.co.nz wrote in message news:afl9ts$d70$1@lust.ihug.co.nz... Sub a() If ActiveCell.Offset(4, 3).Value < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).PrintPreview End If End Sub This gave me a compile error, expected end sub Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#HAm0L1HCHA.2680@tkmsftngp12... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" And Range("a4") < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).PrintPreview End If End Sub this is good also one step less "Ron de Bruin" <rondebruin@kabelfoon.nl schreef in bericht news:#e9cxB1HCHA.2060@tkmsftngp11... in vba : put this in the worksheetmodule Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" And Range("a4") < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).Select Selection.PrintPreview End If End Sub change PrintPreview for printout Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afjep5$ldm$1@lust.ihug.co.nz... Can anybody help me with this active cell macro formula. Say if I click in A1 and there is a number in A4 which would also have a formula in it then A3:D7 selection would be printed, Remembering A4 contains a Formula like (=B3) Thanx For Your Help |
| 1180 | Did you not get my macro to work Bob Works great on my machine. Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afnuoo$3bd$1@lust.ihug.co.nz... Anyway I can get this Macro to work, the first 2 lines work but not the next 2 Lines, TIA/Bob If ActiveCell.Offset(69, 7).Value < 0 Then ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End If End Sub "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:uxuE0e8HCHA.368@tkmsftngp13... and do the same things till 700 change that to 7000 for your 100 pages Sorry for the typo I go to bed. 1:50 it is time I think "Ron de Bruin" <rondebruin@kabelfoon.nl schreef in bericht news:#afHic8HCHA.2984@tkmsftngp13... This is the same Bob this example start in G70 to look if there a value. if it is do the resize z.Resize(64, 8).PrintPreview if you want to start in column a with printing you can use a offset in between like this If z.Value < 0 Then z.Offset(0, -6).Resize(64, 8).PrintPreview then he look in g140 g210 g280 and do the same things till 70 change that to 700 for your 100 pages Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflg98$imt$1@lust.ihug.co.nz... Really need to do it from a activecell as this month 100 sheets below then I copy them over and need to do the same to the next 100 sheets adjacent to the last month Regards Bob Vance " Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#j$5sL8HCHA.2620@tkmsftngp08... Sub trythis() Dim a As Long Dim z As Range For a = 70 To 700 Step 70 Set z = ActiveSheet.Cells(a, 7) If z < 0 Then z.Resize(64, 8).PrintPreview Next a End Sub try this bob works for G70 till G700 Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afldoo$g4k$1@lust.ihug.co.nz... Yes everything is 64 cells down Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#GZCr87HCHA.1060@tkmsftngp11... Can you tell this Is the cell that you want to check on every page on the sheet not on the same distance in rows?? in your example you say 69 and 133. is the certain cel on that page the only cel with a value in that column?? I mean that there are maximum 100 cells in that column with a value with you answers maybe I can help you Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflcrt$fht$1@lust.ihug.co.nz... Yes there is 100 pages on a worksheet, and I want to print the paged below that have number in a certain cell Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:eOm53y7HCHA.1124@tkmsftngp10... Hi Bob please explane more what you are looking for Are you talking about a workbook with one sheet with 100 pages. and you want to print those pages when a cell on that page < 0. Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflbmg$ekq$1@lust.ihug.co.nz... Just cant get this one to work, can you see what im am doing wrong, Trying to do the same task in each sheet below so would end up with about 100 different commands on the same macro If ActiveCell.Offset(69, 7).Value < 0 Then ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End If End Sub Regards Bob Vance "Bob Vance" <rjvance@ihug.co.nz wrote in message news:afl9ts$d70$1@lust.ihug.co.nz... Sub a() If ActiveCell.Offset(4, 3).Value < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).PrintPreview End If End Sub This gave me a compile error, expected end sub Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#HAm0L1HCHA.2680@tkmsftngp12... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" And Range("a4") < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).PrintPreview End If End Sub this is good also one step less "Ron de Bruin" <rondebruin@kabelfoon.nl schreef in bericht news:#e9cxB1HCHA.2060@tkmsftngp11... in vba : put this in the worksheetmodule Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" And Range("a4") < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).Select Selection.PrintPreview End If End Sub change PrintPreview for printout Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afjep5$ldm$1@lust.ihug.co.nz... Can anybody help me with this active cell macro formula. Say if I click in A1 and there is a number in A4 which would also have a formula in it then A3:D7 selection would be printed, Remembering A4 contains a Formula like (=B3) Thanx For Your Help |
| 1186 | Hi, Easy Peasy question. Using the Books "Excel 2002 Bible" and "Excel 2002 VBA" I wrote the following code. Worksheets(7).ActiveCell.Offset(0, 1).Copy Destination = Worksheets(2).ActiveCell.Offset(c, 0) Which when dedugged is ok but when runs states "Object doesn't support this property or method" C= "some vertical number" What would the correct syntax be to copy a value from one cell in a workbook sheet to another cell in a workbook sheet. William |
| 1188 | "William" <wapfu@xtra.co.nz wrote in message news:11d8001c220b3$49b8d540$36ef2ecf@tkmsftngxa12... | Hi, | Easy Peasy question. | Using the Books "Excel 2002 Bible" and "Excel 2002 VBA" | I wrote the following code. | Worksheets(7).ActiveCell.Offset(0, 1).Copy Destination = | Worksheets(2).ActiveCell.Offset(c, 0) | | Which when dedugged is ok but when runs states "Object | doesn't support this property or method" | C= "some vertical number" | | What would the correct syntax be to copy a value from one | cell in a workbook sheet to another cell in a workbook | sheet. | | William |
| 1189 | Sorry I didn't get it to work it went back to g column when I was at ce column Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:OeVcc6KICHA.1060@tkmsftngp11... Did you not get my macro to work Bob Works great on my machine. Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afnuoo$3bd$1@lust.ihug.co.nz... Anyway I can get this Macro to work, the first 2 lines work but not the next 2 Lines, TIA/Bob If ActiveCell.Offset(69, 7).Value < 0 Then ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End If End Sub "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:uxuE0e8HCHA.368@tkmsftngp13... and do the same things till 700 change that to 7000 for your 100 pages Sorry for the typo I go to bed. 1:50 it is time I think "Ron de Bruin" <rondebruin@kabelfoon.nl schreef in bericht news:#afHic8HCHA.2984@tkmsftngp13... This is the same Bob this example start in G70 to look if there a value. if it is do the resize z.Resize(64, 8).PrintPreview if you want to start in column a with printing you can use a offset in between like this If z.Value < 0 Then z.Offset(0, -6).Resize(64, 8).PrintPreview then he look in g140 g210 g280 and do the same things till 70 change that to 700 for your 100 pages Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflg98$imt$1@lust.ihug.co.nz... Really need to do it from a activecell as this month 100 sheets below then I copy them over and need to do the same to the next 100 sheets adjacent to the last month Regards Bob Vance " Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#j$5sL8HCHA.2620@tkmsftngp08... Sub trythis() Dim a As Long Dim z As Range For a = 70 To 700 Step 70 Set z = ActiveSheet.Cells(a, 7) If z < 0 Then z.Resize(64, 8).PrintPreview Next a End Sub try this bob works for G70 till G700 Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afldoo$g4k$1@lust.ihug.co.nz... Yes everything is 64 cells down Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#GZCr87HCHA.1060@tkmsftngp11... Can you tell this Is the cell that you want to check on every page on the sheet not on the same distance in rows?? in your example you say 69 and 133. is the certain cel on that page the only cel with a value in that column?? I mean that there are maximum 100 cells in that column with a value with you answers maybe I can help you Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflcrt$fht$1@lust.ihug.co.nz... Yes there is 100 pages on a worksheet, and I want to print the paged below that have number in a certain cell Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:eOm53y7HCHA.1124@tkmsftngp10... Hi Bob please explane more what you are looking for Are you talking about a workbook with one sheet with 100 pages. and you want to print those pages when a cell on that page < 0. Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflbmg$ekq$1@lust.ihug.co.nz... Just cant get this one to work, can you see what im am doing wrong, Trying to do the same task in each sheet below so would end up with about 100 different commands on the same macro If ActiveCell.Offset(69, 7).Value < 0 Then ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End If End Sub Regards Bob Vance "Bob Vance" <rjvance@ihug.co.nz wrote in message news:afl9ts$d70$1@lust.ihug.co.nz... Sub a() If ActiveCell.Offset(4, 3).Value < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).PrintPreview End If End Sub This gave me a compile error, expected end sub Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#HAm0L1HCHA.2680@tkmsftngp12... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" And Range("a4") < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).PrintPreview End If End Sub this is good also one step less "Ron de Bruin" <rondebruin@kabelfoon.nl schreef in bericht news:#e9cxB1HCHA.2060@tkmsftngp11... in vba : put this in the worksheetmodule Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" And Range("a4") < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).Select Selection.PrintPreview End If End Sub change PrintPreview for printout Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afjep5$ldm$1@lust.ihug.co.nz... Can anybody help me with this active cell macro formula. Say if I click in A1 and there is a number in A4 which would also have a formula in it then A3:D7 selection would be printed, Remembering A4 contains a Formula like (=B3) Thanx For Your Help |
| 1190 | Hi William Try: Worksheets(7).Select ActiveCell.Copy Destination:= _ Worksheets(2).Range(ActiveCell.Address) You cannot specify ActiveCell after referencing a Worksheet. OR better yet, use the sheets CodeName (name not in brackets in the Project Explorer). So the code might look something like: Sheet7.Select ActiveCell.Copy Destination:= _ Sheet2.Range(ActiveCell.Address) The CodeName of a Sheet is not altered by changing its position or renaming it. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "William" <wapfu@xtra.co.nz wrote in message news:11d8001c220b3$49b8d540$36ef2ecf@tkmsftngxa12... | Hi, | Easy Peasy question. | Using the Books "Excel 2002 Bible" and "Excel 2002 VBA" | I wrote the following code. | Worksheets(7).ActiveCell.Offset(0, 1).Copy Destination = | Worksheets(2).ActiveCell.Offset(c, 0) | | Which when dedugged is ok but when runs states "Object | doesn't support this property or method" | C= "some vertical number" | | What would the correct syntax be to copy a value from one | cell in a workbook sheet to another cell in a workbook | sheet. | | William |
| 1191 | Mr Ogilvy, That worked Brilliantly Thanks for the Help Regards Bob Vance "Tom Ogilvy" <twogilvy@msn.com wrote in message news:u5mvE3IICHA.1772@tkmsftngp09... Make it a single line if statement. If ActiveCell.Offset(69, 7).Value < 0 Then _ ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then _ ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End Sub Regards, Tom Ogilvy Bob Vance <rjvance@ihug.co.nz wrote in message news:afnuoo$3bd$1@lust.ihug.co.nz... Anyway I can get this Macro to work, the first 2 lines work but not the next 2 Lines, TIA/Bob If ActiveCell.Offset(69, 7).Value < 0 Then ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End If End Sub "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:uxuE0e8HCHA.368@tkmsftngp13... and do the same things till 700 change that to 7000 for your 100 pages Sorry for the typo I go to bed. 1:50 it is time I think "Ron de Bruin" <rondebruin@kabelfoon.nl schreef in bericht news:#afHic8HCHA.2984@tkmsftngp13... This is the same Bob this example start in G70 to look if there a value. if it is do the resize z.Resize(64, 8).PrintPreview if you want to start in column a with printing you can use a offset in between like this If z.Value < 0 Then z.Offset(0, -6).Resize(64, 8).PrintPreview then he look in g140 g210 g280 and do the same things till 70 change that to 700 for your 100 pages Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflg98$imt$1@lust.ihug.co.nz... Really need to do it from a activecell as this month 100 sheets below then I copy them over and need to do the same to the next 100 sheets adjacent to the last month Regards Bob Vance " Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#j$5sL8HCHA.2620@tkmsftngp08... Sub trythis() Dim a As Long Dim z As Range For a = 70 To 700 Step 70 Set z = ActiveSheet.Cells(a, 7) If z < 0 Then z.Resize(64, 8).PrintPreview Next a End Sub try this bob works for G70 till G700 Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afldoo$g4k$1@lust.ihug.co.nz... Yes everything is 64 cells down Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#GZCr87HCHA.1060@tkmsftngp11... Can you tell this Is the cell that you want to check on every page on the sheet not on the same distance in rows?? in your example you say 69 and 133. is the certain cel on that page the only cel with a value in that column?? I mean that there are maximum 100 cells in that column with a value with you answers maybe I can help you Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflcrt$fht$1@lust.ihug.co.nz... Yes there is 100 pages on a worksheet, and I want to print the paged below that have number in a certain cell Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:eOm53y7HCHA.1124@tkmsftngp10... Hi Bob please explane more what you are looking for Are you talking about a workbook with one sheet with 100 pages. and you want to print those pages when a cell on that page < 0. Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:aflbmg$ekq$1@lust.ihug.co.nz... Just cant get this one to work, can you see what im am doing wrong, Trying to do the same task in each sheet below so would end up with about 100 different commands on the same macro If ActiveCell.Offset(69, 7).Value < 0 Then ActiveCell.Offset(64, 0).Resize(64, 8).PrintPreview If ActiveCell.Offset(133, 7).Value < 0 Then ActiveCell.Offset(128, 0).Resize(64, 8).PrintPreview End If End Sub Regards Bob Vance "Bob Vance" <rjvance@ihug.co.nz wrote in message news:afl9ts$d70$1@lust.ihug.co.nz... Sub a() If ActiveCell.Offset(4, 3).Value < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).PrintPreview End If End Sub This gave me a compile error, expected end sub Regards Bob Vance "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:#HAm0L1HCHA.2680@tkmsftngp12... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" And Range("a4") < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).PrintPreview End If End Sub this is good also one step less "Ron de Bruin" <rondebruin@kabelfoon.nl schreef in bericht news:#e9cxB1HCHA.2060@tkmsftngp11... in vba : put this in the worksheetmodule Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$1" And Range("a4") < 0 Then ActiveCell.Offset(2, 0).Resize(5, 4).Select Selection.PrintPreview End If End Sub change PrintPreview for printout Regards Ron "Bob Vance" <rjvance@ihug.co.nz schreef in bericht news:afjep5$ldm$1@lust.ihug.co.nz... Can anybody help me with this active cell macro formula. Say if I click in A1 and there is a number in A4 which would also have a formula in it then A3:D7 selection would be printed, Remembering A4 contains a Formula like (=B3) Thanx For Your Help |
| 1195 | Hi, You can refer to the previous and next sheet using defined names with XL4 formula's: Define these names: ThisSheet =GET.CELL(32+0*now();indirect("rc";False)) AllSheets =GET.WORKBOOK(1+0*now()) PrevSheet =INDEX(AllSheets;MATCH(ThisSheet;AllSheets;0)-1+0*now()) NextSheet =INDEX(AllSheets;MATCH(ThisSheet;AllSheets;0)+1+0*now()) Now refer to cel B1 on the next sheet using: =INDIRECT("'"&NextSheet&"'!"&CELL("address";B1)) Warning: Copying a cell that contains one of these names to another worksheet will cause XL2000 to crash. Copying a sheet with that name in a cell did not cause trouble with my XL2000 though. Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)- 1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! . |
| 1200 | If you mean do they reference other sheets then the answer is that they are self contained and it sounds very similar to the task you've done previously with copying a graph, in fact, it is almost the same because a couple of buttons on the summary sheet have the ability to produce graphs from this sheet. But at the end of the day, yes, these two sheets could operate without any others existing. Robert "" <dmcritchie@msn.com wrote in message news:OTrQvXGICHA.2488@tkmsftngp09... Are these two sheets self contained with each other or are other sheets involved. I've done similar things in the past, copying a worksheet & graph together to make a new set. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:ufA$88FICHA.1772@tkmsftngp09... Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1202 | Myrna I had wondered about doing this, but to put it bluntly, when I think about it my brain hurts ! Thinking aloud, I would have to go into the detailed sheet and change each range definition so that it applied to the new sheet - doable but I need to specify that it is the range on this sheet ie not the workbook level name, what is the syntax for that if I wanted to change range name 'LastRecord' on sheet 'Air Quality Detail' to refer to the range A2:C10 (for simplicity's sake - I could work it out for the actual range) ? I could then presumably refer to the range 'Air Quality Detail'!LastRecord on the summary sheet ? Robert "Myrna Larson" <myrnalarson@chartermi.net wrote in message news:ajkuhu4ev3gddbjc4hvg2qget8tmem79fa@4ax.com... There's no built-in relative sheet addressing. You would have to accomplish this a VBA macro that copies the sheet, then changes the name definition and/or formulas. On Sun, 30 Jun 2002 18:56:24 +0100, "Robert Brown" <robert@nospam-southall-brown.co.uk wrote: Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1221 | To use a sheet-level name, you include the sheet name as part if the name and part of the reference: Name is 'Air Quality Detail'!LastRecord and the reference is 'Air Quality Detail'!$A$2:$C$10 Be careful with relative references. They are relative to the cell that's active at the time you write the formula. If the cell won't change, use absolute, as I did above. You perhaps could use a macro to update and or create the names. On Mon, 1 Jul 2002 09:17:27 +0100, "Robert Brown" <robert@nospam-southall-brown.co.uk wrote: Myrna I had wondered about doing this, but to put it bluntly, when I think about it my brain hurts ! Thinking aloud, I would have to go into the detailed sheet and change each range definition so that it applied to the new sheet - doable but I need to specify that it is the range on this sheet ie not the workbook level name, what is the syntax for that if I wanted to change range name 'LastRecord' on sheet 'Air Quality Detail' to refer to the range A2:C10 (for simplicity's sake - I could work it out for the actual range) ? I could then presumably refer to the range 'Air Quality Detail'!LastRecord on the summary sheet ? Robert "Myrna Larson" <myrnalarson@chartermi.net wrote in message news:ajkuhu4ev3gddbjc4hvg2qget8tmem79fa@4ax.com... There's no built-in relative sheet addressing. You would have to accomplish this a VBA macro that copies the sheet, then changes the name definition and/or formulas. On Sun, 30 Jun 2002 18:56:24 +0100, "Robert Brown" <robert@nospam-southall-brown.co.uk wrote: Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1226 | An annoyance, not critical, but puzzling. I've seen this on a PC running NT with Excel 97, Windows 2000 and Excel 97, and Windows 98 and Excel 2000. (First two at work after migration from NT to 2000, and the third is my config at home) On occasion, within a workbook, when opening it up I get a 'shadow' of the workbook that I opened. One shows the file name with a (1) and the other with a (2). Entries in one book will populate the second. If I attempt to close the second workbook they both close. The only way out is to copy the worksheet into a new workbook. Any ideas as to how this is happening and if there's an easier fix? Michael |
| 1228 | But you said the formatting was different in the 2 windows... were you wrong on that point? On Mon, 1 Jul 2002 12:38:58 -0500, "DRB" <drb@barkto.com wrote: Bingo! Overlooking the obvious, that's me... "Peo Sjoblom" <terre08@mvps.org wrote in message news:#jZXTwRICHA.2612@tkmsftngp08... If it wasn't for the different formatting I'd say that somebody opened two windows of the same file and saved it (windownew window), if that was the case you can open the file, press ctrl+F4 and save the file.. -- Regards, Peo Sjoblom "DRB" <drb@barkto.com wrote in message news:uzu$jmRICHA.1784@tkmsftngp12... I have a user with an Excel 97 workbook, and when we open it (in XL97 or XL2000) it opens 2 copies- but with different formatting. If I try to close on of the two copies, it closes both. I have macros turned off, so that wouldn't appear to be the answer. Any ideas? This is weird. David |