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 |
| 10 | Hi. I'm trying to add hours duration to a start time to get an end time. my worksheet has a column A with a start time (lets say 4:00 AM) , column B is hours worked (lets say 4.25 hours) I want column C to add 4.25 hours on to 4:00 AM. (in this case it would be 8:15 AM). This seems like it should be easy - but i have tried just about everything and have not been sucessful. Thanks for your help! |
| 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. |
| 17 | That's brilliant, fits perfectly with a little project I have. Thanks Andy "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D0A6AF7.4030905@contextures.com... You can do this on the worksheet: 1. In cell B4, enter the number 1 2. In cell C4, enter the following formula: =REPT("y",B4*100) 3. Format cell C4 as Monotype Sorts font, and add a border to the cell. 4. Adjust the width of cell C4 to fit the bar. 5. Now, you can enter a percentage in cell B4 and the bar in cell C4 will be adjusted. Note: for a smaller thermometer, use x, and for a larger one, use z Peter Kretzman wrote: Does anyone know a product or available control that will let me display a visual for percent complete? A simple horizontal thermometer, filled accordingly, is what I was thinking of. Thanks, PK -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 20 | Greetings Would appreciate advice on whether the following scenario is likely to work: I distribute a CD based web and I would like the user to be able to query a database contained on the cd. Because there is no server, I don't think its possible using client side web functionality. I propose to use the excel sql.request worksheet function to return data from an Access database. The user would enter a search criteria in a cell and the results would be returned. The excel spreadsheet would be saved as .htm (?) I will try this at work tomorrow but can't test it at home because I can't get the odbc add-in to install at home. Any opinions on my chance of success? thanks Dan |
| 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. |
| 24 | Jay, one way =A1+B1/24 format as hh:mm -- Regards, Peo Sjoblom "Jay Bonham" <jaybham62@earthlink.net wrote in message news:edPO8.115$6a.9@newsread1.prod.itd.earthlink.net... Hi. I'm trying to add hours duration to a start time to get an end time. my worksheet has a column A with a start time (lets say 4:00 AM) , column B is hours worked (lets say 4.25 hours) I want column C to add 4.25 hours on to 4:00 AM. (in this case it would be 8:15 AM). This seems like it should be easy - but i have tried just about everything and have not been sucessful. Thanks for your help! |
| 34 | Hi, I am having problems with hiding columns, something in my worksheet seems to be limiting the number of columns that I can hide. If I try to hide more than 115 columns, I get an error "cannot move objects off sheet". The number of columns that I can hide seem to be affected by the number of comments on the sheet, but it causes problems even with only a few comments. Can anyone help please, otherwise I'll have to try removing all the comments and resetting them after hiding the columns! |
| 36 | Hi David Removing the Comments first and replacing them after hiding might be the best bet. Just use Copy Paste special - Comments to a new sheet, save and try again -- **Possible solution has NOT been tested** FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm "David Terris" <TerrisD@obadiah.swinternet.co.uk wrote in message news:aekdl4$70l$1@news5.svr.pol.co.uk... | Hi, | | I am having problems with hiding columns, something in my worksheet seems to | be limiting the number of columns that I can hide. If I try to hide more | than 115 columns, I get an error "cannot move objects off sheet". The number | of columns that I can hide seem to be affected by the number of comments on | the sheet, but it causes problems even with only a few comments. | | Can anyone help please, otherwise I'll have to try removing all the comments | and resetting them after hiding the columns! | | | | |
| 37 | The following MSKB article describes the reason for the message, and suggests a workaround: XL2000: "Cannot Shift Objects Off Sheet" Error Hiding Columns /support/kb/articles/Q211/7/69.ASP David Terris wrote: Hi, I am having problems with hiding columns, something in my worksheet seems to be limiting the number of columns that I can hide. If I try to hide more than 115 columns, I get an error "cannot move objects off sheet". The number of columns that I can hide seem to be affected by the number of comments on the sheet, but it causes problems even with only a few comments. Can anyone help please, otherwise I'll have to try removing all the comments and resetting them after hiding the columns! -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 41 | You can use Advanced Filter to do this: Note: Your list needs a heading in the first cell for the following to work correctly 1) Select a cell in the list. 2) From the Data menu, choose Filter, Advanced Filter. 3) Choose 'Copy to another location'. 4) Excel should automatically detect the list range. If not, you can select the cells on the worksheet. 5) Select a starting cell for the copy 6) Add a check mark to the Unique records only box. 7) Click OK Haggis wrote: Good Morning I have a list of values which are the levels associated with products. Thus there are repeated values as more that one product can have the same level. I need to get a list of all the levels that exist without repeating them. Does anyone know a function I can use or how I would go about doing this? Thanks Haggis -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 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 . |
| 51 | Thanks again Harald, I did get back to the server with your help, but I have been unable to download much from the Misc and Worksheet groups (my two favorites).......is there trouble going on with those two or is it just my connection?...... Vaya con Dios, Chuck,, CABGx3 Harald Staff <harald.staff@eunet.no wrote in message news:#7XUm3PFCHA.2640@tkmsftngp04... Hi Chuck msnews.microsoft.com HTH. Best wishes Harald "CLR" <croberts@tampabay.rr.com wrote in message news:qrSO8.21075$WI.583990@twister.tampabay.rr.com... Hi All....... I'm just restored from a HD crash and am having to re-install everything.......would someone be so kind as to tell me the proper name of the Microsoft News Server, that I might tie back in to it directly, please? tks Chuck, CABGx3 |
| 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 |
| 59 | It's the server farm. Your results will depend on which server you connect to. In article <DgjP8.676$iL2.65451@twister.tampabay.rr.com, CLR <croberts@tampabay.rr.com wrote: Thanks again Harald, I did get back to the server with your help, but I have been unable to download much from the Misc and Worksheet groups (my two favorites).......is there trouble going on with those two or is it just my connection?...... Vaya con Dios, Chuck,, CABGx3 Harald Staff <harald.staff@eunet.no wrote in message news:#7XUm3PFCHA.2640@tkmsftngp04... Hi Chuck msnews.microsoft.com HTH. Best wishes Harald "CLR" <croberts@tampabay.rr.com wrote in message news:qrSO8.21075$WI.583990@twister.tampabay.rr.com... Hi All....... I'm just restored from a HD crash and am having to re-install everything.......would someone be so kind as to tell me the proper name of the Microsoft News Server, that I might tie back in to it directly, please? tks Chuck, CABGx3 |
| 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 |
| 75 | Brian, Thank you so very much for taking time to reply. You solved my problem perfectly! Again, thank you. Greg -----Original Message----- Greg, There are some things the macro recorder does not do well. The solution is the concatenation function. You can use this function in a macro or on the worksheet self. So the simple way is to enter this formula in cell C1: = A1 & ", " & B1 Copy this down for all the rows. Then you'll probably want to do a copy and paste special and choose values to convert the formula to values. HTH, Brian Greg Hight wrote: Kindly email me if you have a solution This should be a simple problem with a simple solution, but I'm stumped! I have several thousand rows and two columns. I want the contents of one column to appear in the other column next to the contents of the other column. Specifically, Column A contains last name Column B contains first name I want first name following last name, separated by comma and space. I've tried recording macros, but even when the relative function is applied, the macro only records exactly what I copied when creating the macro...it does not read the contents of the next row and then paste them to the new cell. Thanks very much. . |
| 78 | i need to be able to sort an excel list by A-Z, however when i am running my macro it copies and past values into the list to be sorted, but when it copies and past blank cells that have a formula in them it sorts those cells as actually having something in them, i don't want those cells to be included before my actual values. Is there a way to capture that range or anyrange in VB so that i could somewhere on a worksheet tally up the total cells with data using counta() and then import that number into VB to delete the remainder of cells (blank cells)? or is there just a simple way to exclude cells that contain the "" value from a formula after they have been copy and pasted with values only? |
| 90 | Try: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Target.Count 1 Then Exit Sub With Target.Interior MsgBox "ColorIndex = " & .ColorIndex & vbNewLine & _ "Red: " & .Color Mod 256 & vbNewLine & _ "Green: " & Int(.Color / 256) Mod 256 & vbNewLine & _ "Blue: " & Int(.Color / 256 ^ 2) End With End Sub In article <db6701c21615$504293e0$36ef2ecf@tkmsftngxa12, Gabriela Montero <gbm105@hotmail.com wrote: Hello, Is it possible to get the actual color (cell shading) of a cell from a selection change event? Thanks Gabriela |
| 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. . |
| 102 | Definitely trouble. I had ro reset absolutely everything to see this reply. Best wishes Harald "CLR" <croberts@tampabay.rr.com wrote in message news:DgjP8.676$iL2.65451@twister.tampabay.rr.com... Thanks again Harald, I did get back to the server with your help, but I have been unable to download much from the Misc and Worksheet groups (my two favorites).......is there trouble going on with those two or is it just my connection?...... |
| 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.) |
| 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 |
| 124 | To anwser your second question, In other words, what type of analysis would one be doing when a mixed reference cell would be needed? (E.g. Mortgage calculations? car payments? etc.) the type of analysis is irrelevant when deciding whether to use relative, mixed, or absolute references. It's dependent upon the layout of the data in your worksheets and how you go about constructing formulas and copying them to new locations. HTH Jason Atlanta, GA -----Original Message----- Can someone please give me an example of when it is most appropriate to use a mixed reference in a spreadsheet, as opposed to an absolute reference or a relative reference? In other words, what type of analysis would one be doing when a mixed reference cell would be needed? (E.g. Mortgage calculations? car payments? etc.) . |
| 129 | How do I link cells in seperate worksheets where if I change data in the cell in the first worksheet it will change in the cell in the second worksheet as well. Thank you. |
| 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 |
| 137 | The scrolling speed is controlled by the distance you've moved the pointer off the sheet. For example, if you select row 1, and drag down till your pointer is pointing at the sheet tab (just barely off the worksheet), the scrolling should be at a moderate speed. If you point at the Status Bar (at the bottom of the Excel window), the scrolling speeds up. If you move your pointer to the bottom of the monitor, you reach those lightning speeds. There are other ways to select rows, that you might find easier. 1. Select the first row, then release the mouse button 2. Use the scroll bar to scroll down (without selecting), till you can see the last row you want to select. 3. Hold the Shift key and click in the last row. Or, if you know exactly which rows you want to select: 1. Click in the Name Box (to the left of the Formula Bar) 2. Type the range you want to select, e.g. 1:100 3. Press the Enter key 4. If you want to change the end row after using this method, hold the Shift key and click on a different ending row. George Smirnoff wrote: Is there any way to adjust the speed at which the cursor will scroll down when you use the mouse? For instance if I highlight Row 1 and want to highlight Row 1-1000, when I move the cursor down off the page, it scrolls at about 500 rows a second. So obviously I end up highlighting Rows 1-10000. Can I adjust this? -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 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 |
| 160 | I have a set of about 500 worksheets from which I want to extract info in a summary sheet I am building. I have the names and locations of the workeets which are located all over our network and which are being constantly updated by people at other sites. I'm trying build a column of lookups that automatically changes whan I change the name of the file being looked into. I've created the first onebut because the name of the file is in brackets [*.wks], I can't get the value within the brackets to change as if I enter a cell refference to the file name string. Are there specical commands when dealing with bracketed data? What's a reasonable solution for this? Dan |
| 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 |
| 168 | I guess it corrupted. Search the google excel NG's under corrupted, you will likely find some hits for companies or sites that can open the fiel for a fee Dave -----Original Message----- 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 . |
| 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 |
| 173 | Hi George, Can't think of anything in particular. I'd check the last cell (ctrl+end) to see if that places you beyond your 2000 rows of data that you say you have. Do you have event macros in the worksheet, volatile user defined functions. Formulas dependent on data being changed which could trigger volatile functions. Are other sheets dependent on the data in the sheet being changed. You might check my page on Slow Response /dmcritchie/excel/slowresp.htm "George" <gsmirnoff@yahoo.com wrote in message news:dfc301c216dd$d5df9b60$a5e62ecf@tkmsftngxa07... As of a few months ago, whenever I do a search/replace in a spreadsheet for a particular column, it seems to take unusually long to get it done. I have a spreadsheet of 2000 rows, and when asking to search for a particular string to replace just for that row, the function takes 2- 3 minutes during which the highlighted column flashes as if stuck in an endless loop. This just started happening recently. Any ideas why? |
| 174 | I've done many compound graphs like you describe. The trick is to create separate charts embedded on a worksheet. By careful use of formatting, alignment by snapping to cell intersections and grouping, I manage to get the desired effect. In your oscilloscope analogy, you would have 3 differing Y axis and want to share the X or Time axis. In other words, only the bottom time scale shows. I often do this by scaling all X axes identically on all charts then overlapping/hiding the axis for the top graphs. You might think you can just delete the unwanted axis, but the problem you will continually do battle with is Excel's distressing tendency to resize the plot area every time you alter axis formatting. You will want everything *exactly* the same except the Y axis and perhaps, the series displayed. Hint - no auto scaling/formatting whatsoever. No auto *anything*. Basically the procedure is this: 1) Create the bottom chart exactly as you would like to see it. 2) Fix (freeze) the axes by turning autoscaling off. 3) Copy/paste this chart for as many charts as you are going to stack. 4) Alter the Y scaling on the stacked charts as necessary. 5) Lay them out and glue them together (group) With a little effort, the results can be perfect. Thomas Bartkus "Microsoft" <edward@biebel.net wrote in message news:u8btS6uFCHA.2324@tkmsftngp04... Hi all: I have a question that has got me stumped and which I believe cannot done in Excel but figured I'd ask to be sure. I've been asked to construct a chart that has three different scales on it. However, the problem is not the multiple scales but the layout that is being requested. Essentially the person requesting the chart wants the bottom third of the chart done in one scale, the middle third done in a second scale and the top third in third scale. The only analogy that I can think of to describe the look he would like if from my HS physics days where you have an oscilloscope with different meter bands each with its own waveform stacked on top of each other. Anyone ever tried this and does this even sound possible? It didn't possible to me but you known how it goes when you get the "this should be relatively simple" requests. Thanks, Ed |
| 183 | Put his code in the worksheet module Private Sub Worksheet_Change(ByVal Target As Excel.Range) Target.Value = Ucase(Target.Value) End Sub HTH "Splash" <splash@mosquitonet.com wrote in message news:ugv57gc1e48p7b@corp.supernews.com... Any way to make any input into a cell always "be" capitalized, as it's entered? That is, if I type "nm" in a cell, then enter, can it make it automatically "become" "NM"? |
| 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 |
| 187 | You might want to use formula instead of value Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False Target.Formula = UCase(Target.Formula) Application.EnableEvents = True End Sub "Wilson" <jwilson@wickes.com wrote ... Put his code in the worksheet module Private Sub Worksheet_Change(ByVal Target As Excel.Range) Target.Value = Ucase(Target.Value) End Sub HTH "Splash" <splash@mosquitonet.com wrote in message news:ugv57gc1e48p7b@corp.supernews.com... Any way to make any input into a cell always "be" capitalized, as it's entered? That is, if I type "nm" in a cell, then enter, can it make it automatically "become" "NM"? |
| 189 | Please explain why, David? TIA "" <dmcritchie@msn.com wrote in message news:O9FYSawFCHA.2604@tkmsftngp12... You might want to use formula instead of value Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False Target.Formula = UCase(Target.Formula) Application.EnableEvents = True End Sub "Wilson" <jwilson@wickes.com wrote ... Put his code in the worksheet module Private Sub Worksheet_Change(ByVal Target As Excel.Range) Target.Value = Ucase(Target.Value) End Sub HTH "Splash" <splash@mosquitonet.com wrote in message news:ugv57gc1e48p7b@corp.supernews.com... Any way to make any input into a cell always "be" capitalized, as it's entered? That is, if I type "nm" in a cell, then enter, can it make it automatically "become" "NM"? |
| 193 | When you print two non-contiguous ranges (even on the same worksheet), excel likes to print (at least) two pieces of paper. One way to print your ranges (as one sheet) is to copy the second to the bottom of the first and extend the print range to include the whole thing. You could also copy both ranges as pictures and paste them into another worksheet (still have to be adjacent, though). Then print that. If you copy the ranges as pictures, you don't have to worry about columnwidths being different and screwing up the layout of your data. One way to do this is: Select one of the ranges, then do a Shift-Edit, you'll see a different option--Copy Picture. Choose the one that you like best and paste that picture into a new worksheet. Do the same for the second range. Then print that new worksheet. ========= Another even nicer way is to select the first range and do a regular copy. Then go to the other sheet and do a Shift-Edit. You'll see a different option--Paste Picture Link. If you paste this one, then any changes you make to the original range will be reflected in the picture--kind of a dynamic picture. (The other way was static. It was a picture of the data when you did the copy.) Bruce wrote: I have modified a print macro script I found on Barasch to set my print are to this: ActiveSheet.Range(Cells(2, 1), Cells(BottomRw, LastCol)).Select and my final print command to this: Sheets("List Entry").PrintOut List Entry being the worksheet with the selected cell range. I am now wanting to append cells A1:G15 on a worksheet called "Report" to the bottom of the print out - is there any way of doing this? thanks -- Dave Peterson ec35720@msn.com |
| 196 | AFAIK you can't do this with a formula, because there isn't a character that Excel recognizes as a page break character. You could record or write a macro and run it before you print the worksheet, inserting a page break based on the content of cell A5. Joseph K wrote: Hi, I need to insert/delete a page break at a cell based on if another cell has a picture in it. So far i have a formula =IF(ISBLANK(A5),<delete page break,<insert page break) The question is - can this be done, and if so, what should I put in the <delete/insert page break parts of the formula? Thanks in advance, Joseph -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 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 |
| 214 | Create a column in for EACH list and call that column "sourcecode" assign a source code and record number: e.g. A00001 the A specifying that that record came from file "A" the next record would be A00002, and so on (use auto fill to do this quickly). Open a new worksheet - copy and past the records from all files into that sheet and sort by the field you are deduping, e.g. company name. You will then see the dupes next to each other, and based on the source code, you can manually delete the dupes. I would copy those dupes before deleting them onto another sheet just so you have a backup. Try it - allway copy your files and work with your copy files so if you screw it up you still have the untouched originals. -----Original Message----- when I have a "potential" customer list in excel, with companies name in column1, and street address in column 2, and city in column 3, and province in column 4, and postal code in column 5,and phone number in column 6, and fax number in column 7, and watts number in column 8, email, in 9, and web url in column 10. Now I would like to merge two different lists with the same layouts, but i am afraid that there will be some duplication (because some companies are listed under different categories in the yellow pages), and I want to know if there is a short cut to "pointing out", "eliminating" the doubles? . |
| 226 | Use the autofilter as necessary. Copy the selected records into another worksheet and use the advanced filter for "unique" records. This takes a bit of practice but it usally works quite well. Just make sure that the area for filtering has at least 1 blank row and 1 blank column around it. One can always export the data into Access and search for redundant records if you know how. Hope this helps! -----Original Message----- Good Morning I have a list of values which are the levels associated with products. Thus there are repeated values as more that one product can have the same level. I need to get a list of all the levels that exist without repeating them. Does anyone know a function I can use or how I would go about doing this? Thanks Haggis . |
| 230 | If you find it time consuming and difficult to spot the duplicates after sorting (e.g. xCompany versus xCompany, Inc.), try using Refinate for free to bring you to the duplicates quickly so you can review them (it is not necessary to sort first). It optionally leaves a comment tag on the cell so you can see all duplicates after you reach the end of the search. You can also make a print-out showing these comment tags. If you make changes to the duplicates, the change is logged in the comment tag. Delete any comment tag you do not need. --Brian Taylor Refinate, copyright 2001 www.adetaylor.com "lk" <lazark@precisemailing.com wrote in message news:e44301c21715$151d50b0$36ef2ecf@tkmsftngxa12... Create a column in for EACH list and call that column "sourcecode" assign a source code and record number: e.g. A00001 the A specifying that that record came from file "A" the next record would be A00002, and so on (use auto fill to do this quickly). Open a new worksheet - copy and past the records from all files into that sheet and sort by the field you are deduping, e.g. company name. You will then see the dupes next to each other, and based on the source code, you can manually delete the dupes. I would copy those dupes before deleting them onto another sheet just so you have a backup. Try it - allway copy your files and work with your copy files so if you screw it up you still have the untouched originals. -----Original Message----- when I have a "potential" customer list in excel, with companies name in column1, and street address in column 2, and city in column 3, and province in column 4, and postal code in column 5,and phone number in column 6, and fax number in column 7, and watts number in column 8, email, in 9, and web url in column 10. Now I would like to merge two different lists with the same layouts, but i am afraid that there will be some duplication (because some companies are listed under different categories in the yellow pages), and I want to know if there is a short cut to "pointing out", "eliminating" the doubles? . |
| 234 | Thanks, Wilson and Dave. Oops. I only wanted to change the values of cells in 2 columns on my worksheet -- not the entire book. (I shouldn't have said "all cell values" but "cell values -- sorry...) So, if I only want the values of two columns to become capitalized after entered in small letters, is that possible? Thanks, guys. "Wilson" <jwilson@wickes.com wrote in message news:erTxOUwFCHA.968@tkmsftngp04... Put his code in the worksheet module Private Sub Worksheet_Change(ByVal Target As Excel.Range) Target.Value = Ucase(Target.Value) End Sub HTH "Splash" <splash@mosquitonet.com wrote in message news:ugv57gc1e48p7b@corp.supernews.com... Any way to make any input into a cell always "be" capitalized, as it's entered? That is, if I type "nm" in a cell, then enter, can it make it automatically "become" "NM"? |
| 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 |
| 241 | Makes a lot more sense for only two columns. If target.column < 3 and target.column < 5 then exit sub "Splash" <splash@mosquitonet.com wrote in message news:ugvj3jlbb6cmd5@corp.supernews.com... Thanks, Wilson and Dave. Oops. I only wanted to change the values of cells in 2 columns on my worksheet -- not the entire book. (I shouldn't have said "all cell values" but "cell values -- sorry...) So, if I only want the values of two columns to become capitalized after entered in small letters, is that possible? Thanks, guys. "Wilson" <jwilson@wickes.com wrote in message news:erTxOUwFCHA.968@tkmsftngp04... Put his code in the worksheet module Private Sub Worksheet_Change(ByVal Target As Excel.Range) Target.Value = Ucase(Target.Value) End Sub HTH "Splash" <splash@mosquitonet.com wrote in message news:ugv57gc1e48p7b@corp.supernews.com... Any way to make any input into a cell always "be" capitalized, as it's entered? That is, if I type "nm" in a cell, then enter, can it make it automatically "become" "NM"? |
| 243 | Hi George, See NETWORKDAYS Worksheet Function in HELP NETWORKDAYS(start_date,end_date,holidays) HTH, datetime.htm has date and time relate info on my site. , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm "George" <gjbeaupre@archchemicals.com wrote in message How can I calculate the number of days between two dates taking into account workdays only (i.e. Monday-Friday). |
| 250 | Hi tomo BackgroundQuery . Used only with query tables based on the results of an SQL query. True to return control to the procedure as soon as a database connection is made and the the query is submitted (the query is updated in the background). False to return control to the procedure only after all data has been fetched to the worksheet. Post the entire code (from the With to the End With ) of that section. The error is caused by other sections of the code, not the .Refresh . Jon tomo wrote: i have recorded a macro but when i tried to run it again, i got an error "General ODBC Error" and the statement causing the error is ..Refresh BackgroundQuery:=xxxx (TRUE or FALSE) almost most of the time, FALSE doesn't work, and when i try to change it to TRUE, it work sometimes but it also causes error sometimes do anyone know what is the function of this statement? and under what circumstances i will get the error message? and what should i do to make my macro works? (delete that statement? or add some additional statements?) i'm a newbie and it is appreciated if the answer could be a little bit more detail. thanks in advance. |
| 259 | Hi, The option buttons are designed to choose between a number of items. I'm not sure, but I think you can only have one selected option button per worksheet (haven't tried though). To do what you want, use the checkbox. Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- I have one column, a1, a2, a3, a4 In each I have 2 (form) option buttons When I click on the option buttons in a4 the ones in a1 change to I want each cell to be seperate so that when I click on the option button in a1 one is on the other is off. But I do not want the option buttons to change in the other cells. Hope someone can help . |
| 263 | Hi As Jan has said Checkboxes might be better suited for you circumstance. They are designed to allow multiple choices. Option buttons are normally used to allow only one of a number of choices. Depending on the type being used, you can change the "GroupName" Property to group certain option buttons together. Or you can also do the same by placing each 'group' into a Frame control. -- "Jan Karel Pieterse" <pieterse_home@compuserve.com wrote in message news:101a801c21756$f6d58590$3aef2ecf@TKMSFTNGXA09... | Hi, | | The option buttons are designed to choose between a number | of items. I'm not sure, but I think you can only have one | selected option button per worksheet (haven't tried | though). | | To do what you want, use the checkbox. | | Regards, | | Jan Karel Pieterse | Excel TA/MVP | | -----Original Message----- | I have one column, a1, a2, a3, a4 In each I have 2 (form) | option buttons | When I click on the option buttons in a4 the ones in a1 | change to | I want each cell to be seperate so that when I click on | the option button in a1 one is on the other is off. But I | do not want the option buttons to change in the other | cells. | | Hope someone can help | . | |
| 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. . |
| 291 | Right click in a blank area of the menu bar and select customize from the dropdown. in the first tab, click on the worksheet menu bar and hit the reset button - or if you mean the whole worksheet menu bar is missing, click in the checkbox next to it. "Will Fleenor" <will@k2e.com wrote in message news:OxMOdl7FCHA.2544@tkmsftngp08... I messed up my main menus in excel by dragging them off the page. Now I have no "File" menu option. How do I fix this? Will Fleenor will_nospam_@k2e.com |
| 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 |
| 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 |
| 304 | I'm familiar with the auditing feature. But it would be nice to be able to generate a list (maybe a separate worksheet) that lists all of the cells which have precedents and their precedents. And/or another list that lists all cells that have dependents and their dependents. |
| 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 |
| 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 |
| 331 | 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 |
| 333 | I have a worksheet of product prices....in which I've hidden the column that contains my cost. I've protected the sheet with a password so the costs can't be revealed. However, if I copy my columns....including the hidden column....and paste into word.....presto...there is my hidden column with all my costs. Any way around this security glitch? |
| 335 | Hi, I wish to know the maximum number of columns which can be created in an excel worksheet. I am unable to create more tha 256 columns. I have a large volume of data which typically exceeds 256 columns. I will be happy to know any workaround for this problem. It would also be helpful to know if there is any setup change which i can make to increase the number of columns. Thank you Regards Balaji |
| 336 | Depends on what version of xl you're using. xl2k (IIRC) added an option under Data|Import External Data| Then Import Data Point at your file (*.txt ???) And you'll see the Text Import (like Text to Columns) wizard show up. Follow the wizard, but on the the last dialog, you'll be prompted to determine where to put the data. (There's a properties button there, too. You may want to click there and tell excell that you don't want it to keep track of this query. (xl won't try to refresh later.) (I'm using xl2002, so the menus might be slightly different--not sure.) I learned on xl97 and I still import to a new worksheet and just copy the data to the location that I want. Richard Nelson wrote: Is there a way to import text data to specific cells in a spreadsheet by stating the cell address and then the data value for that cell? -- Dave Peterson ec35720@msn.com |
| 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 |
| 340 | 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. |
| 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. |
| 349 | No -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Balaji" <fd96241@rediffmail.com wrote in message news:e73001c217e0$285334d0$9ee62ecf@tkmsftngxa05... Hi, I wish to know the maximum number of columns which can be created in an excel worksheet. I am unable to create more tha 256 columns. I have a large volume of data which typically exceeds 256 columns. I will be happy to know any workaround for this problem. It would also be helpful to know if there is any setup change which i can make to increase the number of columns. Thank you Regards Balaji |
| 357 | Hi Murray 65,536 per worksheet -- Regards William willwest22@yahoo.com "Murray" <Murray.hall@markelintl.com.au wrote in message news:1091d01c217ed$57362750$39ef2ecf@TKMSFTNGXA08... | Hi, how many rows does Excel 97 have. I thought read some | where that it was just over the 16000 mark. | Thanks Murray |
| 361 | I have MS Excel spreadsheets that have multiple worksheets copied to a disk and am unable to open these files in Microsoft Works Spreadsheet with all the worksheets. How do I get my files in their original format? |
| 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! |
| 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 |
| 387 | Your basic problem is that you're using a VBA word in your function. Change Name to myName and this problem should go away. What's happening is that your code is under a worksheet. Excel thinks unqualified references belong to the thing (the worksheet that the code is in) owns that. Kind of like activesheet and Range("A1") in a general module. Since you're using Name (and I bet it wasn't declared), excel sees that as belonging to the sheet that owns the code. So this line Name = Range("P10").Value is equivalent to: me.Name = Range("P10").Value (in a general module terms--not really accurate here: activesheet.Name = Range("P10").Value) Fix your variable name and then you'll be off to the next fire! Bruce wrote: I have this macro which replaces a portion of a column with a value whish is entered by the user. For some unknown reason, when a number is entered it changes not only the specified cells in the column, but the Worksheet name too! The macro also converts the entered number to a predfeined string via an HLOOKUP. I have a lot of other macros which rely on this worksheet name and do not want it to change at all. the code is below. Please help! ElseIf Target.Address = "$P$10" Then OptionCheck = IsNumeric(Range("P10").Value) y = (Range("B15").Value) If OptionCheck Then MyVar = MsgBox("This will replace all the options below. Do you wish to continue?", 1, "Option Change") If MyVar = 1 Then For x = 16 To (15 + CInt(y)) Range("O" & x).Value = (Range("P10").Value) Next x Name = Range("P10").Value Range("P10").Value = "=HLOOKUP(" & Name & ",OptionNames,2)" End If End If -- Dave Peterson ec35720@msn.com |
| 388 | You can do it in xl2002, but you'd have to write a macro in earlier versions. You can protect a worksheet so that your code can make changes. But it has to be reset each time you open the workbook: something like: Worksheets("sheet1").Protect Password:="pass", UserInterFaceOnly:=True could go into workbook_open or auto_open. then give the users some buttons assigned to your macros: ActiveCell.EntireRow.Insert You could even prompt them and ask how many and where. Sue wrote: Is there any way to protect individual cells in a worksheet with disabling the ability to insert rows into the same worksheet? As soon as I turn on the protection, I loose the ability to insert. -- Dave Peterson ec35720@msn.com |
| 391 | Sort of strange. Have you tried doing a search for that file on your computer? I'm sure deleting the file wouldn't get rid of the error, but it may provide a clue as to why you are getting that error. -----Original Message----- 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 . |
| 392 | Try this Go into Windows Explorer...then Tools-Folder Options. File types tab. Scroll down to xls. Select Open in the bottom window and hit edit. Mine shows this: "c:\Program Files\Microsoft Office\Office\EXCEL.EXE" /e and: Use DDE is checked and this shows: [open("%1")] Hope it works! -----Original Message----- 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 . |
| 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 |
| 394 | Just tried your suggestion and my settings are currently as you've listed below. Any other ideas? Thanks, Jeff "Eric" <eric.goodrich@paccoast.com wrote in message news:f06e01c218ad$a7a6f1e0$37ef2ecf@TKMSFTNGXA13... Try this Go into Windows Explorer...then Tools-Folder Options. File types tab. Scroll down to xls. Select Open in the bottom window and hit edit. Mine shows this: "c:\Program Files\Microsoft Office\Office\EXCEL.EXE" /e and: Use DDE is checked and this shows: [open("%1")] Hope it works! -----Original Message----- 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 . |
| 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 |
| 396 | Application.ScreenUpdating = False will freeze the screen - better is not to select and activate unless absolutely necessary. If you have stuff like Worksheets("Sheet1").Activate Range("A1").Select Selection.Value = 24 you could just do Worksheets("Sheet1").Range("A1").Value = 24 and if Sheet2 were the active sheet, you would see nothing happen, but the cell would be given the value of 24. This is also much faster and essential if you ever want to get beyond mimicking physical actions. Bert Visscher <bfv@xs4all.nl wrote in message news:aetf8d$j0f$1@news1.xs4all.nl... Hello everyone, Someone suggested that there was a possibility to have a specific screen showing, while a macro is running. Rather than showing how Excel races through the macro. If this is the case and someone knows how this can be done, then could you please tell me? TIA and regards, Bert Visscher. |
| 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 |
| 406 | Eric, Protection is really intended to prevent changes to data, not hiding it. Also, I've tried pasting some cells with a hidden column into Word in both XL97 and XP, and the hidden column remained hidden, so I can't reproduce your situation. Say more about how you're doing this. Some things that come to mind are: Move the costs column out of the range of cells that you'll be copying/pasting (Select the column, hold Shift as you drag it by the edge laterally -- the formulas will adjust as needed), or move it into another sheet altogether (Cut the column, and paste it into another sheet). Are you wanting to prevent the costs from showing when others do the copy/pasting (that is, make sure that no one working with the sheet can reveal the costs), or are you wanting to simply get it pasted into Word yourself, without the costs showing in the Word document? Regards from Virginia Beach, EarlK ------------------------------------------------------------- "Eric" <eric.goodrich@paccoast.com wrote in message news:101c901c217de$f526bb50$b1e62ecf@tkmsftngxa04... I have a worksheet of product prices....in which I've hidden the column that contains my cost. I've protected the sheet with a password so the costs can't be revealed. However, if I copy my columns....including the hidden column....and paste into word.....presto...there is my hidden column with all my costs. Any way around this security glitch? |
| 409 | Hi Jeff, Look in the insert menu for a defined name. Insertnamedefine there may be a link there. I thought Bill's findlink checked there. It may also be a hidden xl4 macro sheet. John "Jeff" <no_spam_jlamarca@fea.net wrote in message news:#4r58#KGCHA.1592@tkmsftngp10... Just tried your suggestion and my settings are currently as you've listed below. Any other ideas? Thanks, Jeff "Eric" <eric.goodrich@paccoast.com wrote in message news:f06e01c218ad$a7a6f1e0$37ef2ecf@TKMSFTNGXA13... Try this Go into Windows Explorer...then Tools-Folder Options. File types tab. Scroll down to xls. Select Open in the bottom window and hit edit. Mine shows this: "c:\Program Files\Microsoft Office\Office\EXCEL.EXE" /e and: Use DDE is checked and this shows: [open("%1")] Hope it works! -----Original Message----- 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 . |
| 422 | Hi John, Just checked Insertnamedefine and found nothing there (except for the data contained in the cell where the cursor is at the time I check). Any other ideas? This is just too weird. Thanks, Jeff "jaf" <johnf87@nohotmail.com wrote in message news:upMFAiMGCHA.2228@tkmsftngp08... Hi Jeff, Look in the insert menu for a defined name. Insertnamedefine there may be a link there. I thought Bill's findlink checked there. It may also be a hidden xl4 macro sheet. John "Jeff" <no_spam_jlamarca@fea.net wrote in message news:#4r58#KGCHA.1592@tkmsftngp10... Just tried your suggestion and my settings are currently as you've listed below. Any other ideas? Thanks, Jeff "Eric" <eric.goodrich@paccoast.com wrote in message news:f06e01c218ad$a7a6f1e0$37ef2ecf@TKMSFTNGXA13... Try this Go into Windows Explorer...then Tools-Folder Options. File types tab. Scroll down to xls. Select Open in the bottom window and hit edit. Mine shows this: "c:\Program Files\Microsoft Office\Office\EXCEL.EXE" /e and: Use DDE is checked and this shows: [open("%1")] Hope it works! -----Original Message----- 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 . |
| 425 | Hi Dave, I think you've hit on something! I just tried Excel in Safe Mode and it will print! It will not, however, when I'm in regular mode. When I enter VBE, the following are listed in the Project - Project VBA window - Start (OR Start.xla) TbRun97Project (TRRUN9.XLS) VBAPROJECT (FERPA.xls) The are sub headings under the last one, which is also the current workbook. The first two items are mysteries to me and, when I click on them, ask for a password. (Why? I haven't a clue.) I also just tried unchecking all of the addins in Tools/Options (there was only one checked) and tried to find the XLStart folder (I couldn't find it using Excel but believe it may be under Windows, Application Data, Microsoft, Excel. I removed the one file found there and rebooted Excel. Still can't print. This is VERY strange - apparently, you did find something as I can print the file in safe mode. Jeff "Dave Peterson" <ec35720@msn.com wrote in message news:3D127B06.5AB8B95D@msn.com... It sure sounds like a macro to me (but I've been wrong lots of times). Maybe there's an addin that's looking for print events?? Try running excel in safe mode. close excel Windows Start button|Run Excel /S It won't look the same, but it's a vanilla version of excel running. Try loading your workbook and printing then. If that works, then I (still) think it might be a macro. When you were in the VBE, did you notice the names of the workbooks that were open in the project explorer? Mine look like: atpvbaen.xls (atpvbaen.xla) funcres (funcres.xla) personal (personal.xla) vbaproject (book1) The first two are related to Excel's analysis toolpak (worksheet and VBA versions). The personal one is mine. The last one is the current workbook. Do you have more/different? (If you do and they look suspicious, you may want to experiment more. These addins usually reside in the XLStart folder (varies with versions of xl and versions of windows). The other spot is under Tools|Addins. Move all the files out of your xlstart, uncheck all the addins in Tools|Options and restart excel. Try your workbook. If it works then, you're on the way to finding the culprit. Start adding things back one at a time. Restart excel (each time) and try it out. If it breaks, you found it. If not, continue reloading stuff. === But I'm pretty much out of ideas after this. Good luck. ======= FWIW, I didn't think FindLink would work in this case, but don't throw it away. It'll come in handy someday! Jeff wrote: Hi Dave, Just tried the FINDLINK.ZIP program. Was able to install it just fine but it didn't find my "mystery" document. Any other ideas? Thanks, Jeff -- Dave Peterson ec35720@msn.com |
| 426 | Thnaks for your help! -----Original Message----- Your basic problem is that you're using a VBA word in your function. Change Name to myName and this problem should go away. What's happening is that your code is under a worksheet. Excel thinks unqualified references belong to the thing (the worksheet that the code is in) owns that. Kind of like activesheet and Range("A1") in a general module. Since you're using Name (and I bet it wasn't declared), excel sees that as belonging to the sheet that owns the code. So this line Name = Range("P10").Value is equivalent to: me.Name = Range("P10").Value (in a general module terms--not really accurate here: activesheet.Name = Range("P10").Value) Fix your variable name and then you'll be off to the next fire! Bruce wrote: I have this macro which replaces a portion of a column with a value whish is entered by the user. For some unknown reason, when a number is entered it changes not only the specified cells in the column, but the Worksheet name too! The macro also converts the entered number to a predfeined string via an HLOOKUP. I have a lot of other macros which rely on this worksheet name and do not want it to change at all. the code is below. Please help! ElseIf Target.Address = "$P$10" Then OptionCheck = IsNumeric(Range("P10").Value) y = (Range("B15").Value) If OptionCheck Then MyVar = MsgBox("This will replace all the options below. Do you wish to continue?", 1, "Option Change") If MyVar = 1 Then For x = 16 To (15 + CInt(y)) Range("O" & x).Value = (Range("P10").Value) Next x Name = Range("P10").Value Range("P10").Value = "=HLOOKUP(" & Name & ",OptionNames,2)" End If End If -- Dave Peterson ec35720@msn.com . |
| 429 | Are there any restrictions on the amount of data you can put into one cell in a worksheet? If so are there any ways to increase the amount of data you can insert? Tried all the usual ways but no luck! Also when converting a word table to a spreadsheet is there a global way to stop excel converting certain figures into dates or will we have to amend every affected cell? Grateful for any enlightenment! Jenny |
| 431 | Jenny, Not sure about your second question, but in regard to the first question, look in Help under Specifications for questions like this. For Excel 97 and 2000 you should find a 32,000 character limit for each cell: Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar. HTH, Brian Jenny wrote: Are there any restrictions on the amount of data you can put into one cell in a worksheet? If so are there any ways to increase the amount of data you can insert? Tried all the usual ways but no luck! Also when converting a word table to a spreadsheet is there a global way to stop excel converting certain figures into dates or will we have to amend every affected cell? Grateful for any enlightenment! Jenny |
| 433 | Hi AussieDave Right click on the sheet name tab, select "View Code" and paste in this: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Address = "$A$1" And IsEmpty(Target) Then Select Case Target.Interior.ColorIndex Case 3 Target.Interior.ColorIndex = 46 Case 46 Target.Interior.ColorIndex = 10 Case Else Target.Interior.ColorIndex = 3 End Select End If End Sub -- **Possible solution has NOT been tested** FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm "AussieDave" <dave@optushome.com.au wrote in message news:qcBQ8.57670$_j6.3018857@bin3.nnrp.aus1.giganews.com... | When clicking on a specific empty cell, I need to color it red after the | first click, orange after the second and (you guessed it!!) green after the | third. | I haven't got a clue, except I guess it's tied up with Click Events. Any | help much appreciated. xl97 by the way. | -- | Dave from Down Under | -- | | |
| 436 | A B C D M N O 1 8/1/02 9/1/02 10/1/02 2 Account 1 3 Close Month 7/1/2002 4 License Fee $20,000 5 Annual Maint $24,000 2,000 2,000 2,000 6 Installation $23,000 Formula in Row 5, column E and beyond =IF (M$1$D3,$D5/12,"") In the sample shown above, the value of $2,000 is placed in cells M5, N5, etc. since the date in M1 is greater than the date in D3. If the date in D3 is changed to 8/1/02, the 2,000 entries are shifted right one month. This is exactly what I want but if no date is entered in D3, I want the nothing or zero entered in row 5 but the formula shown above doesn't give me that result. (It puts 2,000) in every cell in Row 5) I suspect that I need nested IF statements using the ISBLANK function but I'm not having any success with this. For what it's worth, this is a cash forecasting worksheet that lists all pending sales and their forecasted close dates and revenue projections. Changing the Close Date helps us construct different cash positions. Entering no date in the Close Month cell would eliminate that particular account from the forecast. Any help would be appreciated. Scott C. |
| 437 | Hi, There is no click event for the worksheet, but there is a before doubleclick event and a before rightclick event. You can use either of those and then use the code Dave Hawley gave you. If you don't want the cell to go into edit mode when it is double clicked, include "Cancel=True" as the final command of the event sub. Same goes for avoiding the context menu to appear when you would use the beforerightclick event. Example: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Cells.Count 1 Then Exit Sub If Target.Address = "$A$1" And IsEmpty(Target) Then Select Case Target.Interior.ColorIndex Case 3 Target.Interior.ColorIndex = 46 Case 46 Target.Interior.ColorIndex = 10 Case Else Target.Interior.ColorIndex = 3 End Select Cancel=True End If End Sub Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- When clicking on a specific empty cell, I need to color it red after the first click, orange after the second and (you guessed it!!) green after the third. I haven't got a clue, except I guess it's tied up with Click Events. Any help much appreciated. xl97 by the way. -- Dave from Down Under -- . |
| 438 | Hi Scott try: =IF(AND(ISNUMBER($D3),$D31),IF(M$1$D3,$D5/12,""),"") -- "Scott Cardais" <Scott@Cardais.com wrote in message news:112b001c21903$0dc8b990$3bef2ecf@TKMSFTNGXA10... | A B C D M N O | 1 8/1/02 9/1/02 10/1/02 | 2 Account 1 | 3 Close Month 7/1/2002 | 4 License Fee $20,000 | 5 Annual Maint $24,000 2,000 2,000 2,000 | 6 Installation $23,000 | | Formula in Row 5, column E and beyond =IF | (M$1$D3,$D5/12,"") | | In the sample shown above, the value of $2,000 is placed | in cells M5, N5, etc. since the date in M1 is greater | than the date in D3. If the date in D3 is changed to | 8/1/02, the 2,000 entries are shifted right one month. | | This is exactly what I want but if no date is entered in | D3, I want the nothing or zero entered in row 5 but the | formula shown above doesn't give me that result. (It puts | 2,000) in every cell in Row 5) | | I suspect that I need nested IF statements using the | ISBLANK function but I'm not having any success with this. | | For what it's worth, this is a cash forecasting worksheet | that lists all pending sales and their forecasted close | dates and revenue projections. Changing the Close Date | helps us construct different cash positions. Entering no | date in the Close Month cell would eliminate that | particular account from the forecast. | | Any help would be appreciated. | | Scott C. |
| 439 | Hi Scott One method would be =IF($D3="","",I |