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
| 797 | I do not understand why the tendency is for suggestions to convert character 160 to nothing. CHR(160) is the non breaking space character ( ) in HTML and should be converted to a normal space. FWIW the CLEAN Worksheet Function is also sometimes suggested and would only affect char(0) through Char(31), Char(129), Char(141), char(143) and Char(144). To fix up unwanted extra spaces afterwards there is TRIM to remove spaces left and right; and replace can be used to replace 2 spaces with 1 space depending on your data. There is a difference between TRIM in Excel and in VBA. Excel will reduce internal spaces, VBA will not. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "George" <george39672002@yahoo.com wrote in message news:1146201c21ab4 I tried the character 160 and wasn't getting any results, so I thought it was my computer and will troubleshoot that over next little while. It is useful info to know of the character 160. The transfer of data into the excel sheet and the sort function works well. Thanks to both of you. |
| 805 | I posted this through google, but it seemed to crash. So I thought I'd use CDO: I _think_ I understand: I dropped a button from the forms toolbar onto a worksheet. I have 3 examples of the .onaction stuff. Comment out two of them. Option Explicit Sub testme() Dim cb As Button Set cb = ActiveSheet.Buttons(1) Dim c As Range Set c = Range("A1") c.Value = "rangeA1value" Dim i As Long i = 8 With cb .OnAction = ThisWorkbook.Name _ & "!'testme1 " & Chr(34) & c.Value & Chr(34) & "'" .OnAction = ThisWorkbook.Name _ & "!'testme2 ""Hello"", 3'" .OnAction = Workbooks("book2.xls").Name _ & "!'testme3 " & Chr(34) _ & "different wkbk" & Chr(34) & "'" End With End Sub Sub testme1(myval As Variant) MsgBox "From Testme1: " & myval End Sub Sub testme2(mystr As String, myval As Long) MsgBox "From Testme2: " & mystr & " " & myval * 10 End Sub and from Book2.xls Option Explicit Sub testme3(mystr As String) MsgBox "From " & ThisWorkbook.Name & ": " & mystr End Sub I've never used the CDO before. Watch out for linewrap problems. -----Original Message----- Thanks Dave, However my problem is not calling it from within a VBA macro but I want an Excel developer who may know nothing about VBA to pass parameters to a VBA macro contained in a loaded Add-in from a button he has added to the spreadsheet. The syntax I used ('MyAddin.xla'!'Foo "param1"') from within the Assign macro dialog of the excel button control seemed to be ignoring the 'MyAddin.xla' prefix and assuming it was in the workbook containing the button (Book1.xls). Steve What I was hoping for was "Dave Peterson" <ec35720@msn.com wrote in message news:3D18E8DF.4D4DEB54@msn.com... When I tried to pass a string to your Msg sub, I could do it like this: Application.Run "'book1.xla'!msg", "hi there!" I could also do it like this, if I did a Tools|references and set a reference to that other project. msg "hi there2" But I'm not sure if this was the answer to your question.... Steve Legg wrote: Hi, I am able to pass parameters to a VBA macro from the 'Assign macro' dialog by enclosing in single quotes. e.g. Book1!'Msg "Hello"' to execute the macro in the open workbook 'Book1': Public Sub Msg(m As String) MsgBox m End Sub However I am unable to do the same for a macro in an open Add-in. If I try 'MyAddin.xla'!'Foo "param1"' I am getting an error saying The macro 'Book1'!'Foo "param1"' cannot be found. I can invoke macros with no parameters in the Add-in. Anybody know why this is not working, and if there is a way to make it work? Thanks in advance. -- Dave Peterson ec35720@msn.com . |
| 810 | Hi Reg, Would help if you indicated how unwanted rows are to be distinguished. You would probably want to use a macro. Some non programming techniques that might be useful are numbering an unused column with constant numbers using the fill handle, and sorting and deleting. Not knowing what you really want to delete the following works without a loop but the cells in Column A must be empty (no spaces, no formulas). [Dana DeLouis] Sub DelRowsWithEmptyColumnA() On Error Resume Next ' In case there are no blanks Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete ActiveSheet.UsedRange 'Resets UsedRange for Excel 97 End Sub Some more examples of macros can be found in "Deleting Rows" about halfway down Delete Cells/Rows in Range, based on empty cells /dmcritchie/excel/delempty.htm#rows HTH, Assistance with macros on my getstarted.htm page. , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Reg Sinha" <reginald.sinha@opbu.xerox.com wrote in message news:3D19CD32.CD80B40B@opbu.xerox.com... Dear All, I have a Excel file converted from a Crystal Report and it has unwanted rows which I wish to delete. Any ideas would be appreciated. Thanks Reg |
| 812 | Thanks David. I will visit your website. The rows to be deleted are blank rows without any formulas or text. Is this useful? wrote: Hi Reg, Would help if you indicated how unwanted rows are to be distinguished. You would probably want to use a macro. Some non programming techniques that might be useful are numbering an unused column with constant numbers using the fill handle, and sorting and deleting. Not knowing what you really want to delete the following works without a loop but the cells in Column A must be empty (no spaces, no formulas). [Dana DeLouis] Sub DelRowsWithEmptyColumnA() On Error Resume Next ' In case there are no blanks Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete ActiveSheet.UsedRange 'Resets UsedRange for Excel 97 End Sub Some more examples of macros can be found in "Deleting Rows" about halfway down Delete Cells/Rows in Range, based on empty cells /dmcritchie/excel/delempty.htm#rows HTH, Assistance with macros on my getstarted.htm page. , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Reg Sinha" <reginald.sinha@opbu.xerox.com wrote in message news:3D19CD32.CD80B40B@opbu.xerox.com... Dear All, I have a Excel file converted from a Crystal Report and it has unwanted rows which I wish to delete. Any ideas would be appreciated. Thanks Reg |
| 830 | This would have best been tacked onto to your previous incomplete question. Which now leaves both questions receiving answers to which you acknowledged in this question that both worked for what you wanted. Splitting up a single question to rephrase it makes it more difficult for people to find answers in the newsgroup archives /advanced_group_search I see now that the question about removing empty rows based on Column A or a selection was really what you were looking for and you got answers to that for both postings; however, since you did ask about removing alternating rows here is answer to the second question. (For the benefit of those who look in the archives). Again question is ambiguous anyway since there are even rows or odd rows that could be removed so strict interpretation means the even rows are to be removed. Delete Even rows up to row 2 from the bottom. Sub DelEvenRows() ' 2002-03-11 mod 2002-06-26 'Delete Even numbered rows from the bottom Application.ScreenUpdating = False Dim ix As Long ix = Cells.SpecialCells(xlLastCell).Row - _ Cells.SpecialCells(xlLastCell).Row Mod 2 For ix = ix To 2 Step -2 Rows(ix).Delete Next ix Application.ScreenUpdating = True End Sub HTH, Thanks for anwering that you got your real answer previously. , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Reg Sinha" <reginald.sinha@opbu.xerox.com wrote in message Please advise as to how I can go about deleting empty rows between alternate rows of data for a file created by a Crystal Report. |
| 831 | We have a user with low vision. He can read yellow on dark blue pretty well. MS Word has an option to display all text as white on blue background, which is pretty good also. I changed his default font to yellow and his system (Windows) background to dark blue, but of course when he sends a workbook to someone else they see yellow fonts on their white background, and when it gets printed on a laser printer it's very faint gray on white. So obviously I need the color of the data in the displayed cells for this user to be different than the actual color(s) of the underlying fonts. Short of writing some macros, which I can do, is there a system setting that can help? He's using Office 2000 on Windows XP. Thanks. David Walker |
| 832 | Hi Guys I'v got a xls and it just hangs with 100% cpu usage. It looks like its looping forever. I tried to disable all macros but it complains saying this workbook contains excel 4 macros which cannot be disabled. I even tried pressing shift when opening the xls but no luck. Please help cheers Ted |
| 835 | Hi Gianfranco, (posted with email copy) Add-ins, Private Subs, and User Defined Functions to not appear in the macro list. Was that the only problem, or are they also not working. Email copy sent, if you wish to continue this question please keep conversation in this thread within the newsgroup. You are connected directly to the newsgroups using Microsoft CDO there have been recent problems but they are fixed on Microsoft's side. I would suggest that Outlook Express is a better vehicle for reading newsgroups than anything web based. microsoft.public.excel no longer exists on microsoft servers, the correct newsgroup is microsoft.public.excel.misc HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Gianfranco Pellacani" <gianfranco_pellacani@NOSPAMcomergroup.com wrote in message news:ec6901c21c3b$41462740$95e62ecf@tkmsftngxs02... Hi, Itried creating an add-in tohave some macros and functions of my own available on any workbook, but Ican't seem to be able access the macros and funs on the add-in I create after I install it with the add-in manager. I can access the code with the VB Editor, but can't find neither macros' name on the "run macro" menu nor functions' name in the insert "function menu" Guess there's something wrong with the code itself, but I can't figure out what exactly. Please mail me an answer directly, since I got problem accessing newsgroups. In case you do, please remove "NOSPAM" from address. Thanks a lot |
| 836 | Hi Dave, I think I may have made it unclear what I am trying achieve. I would like any old Excel developer to call an existing macro in an excel add-in I have written, passing parameters to it without the need to create a macro themselves. The way I perceived it would work is by the developer dragging a button from the 'Forms' toolbar (which I wrongly called a control) and then right-button mouse clicking on the button and selecting the 'Assign macro...' dialog, and from there just typing in something like: 'MyAddin.xla'!'Mymacro "param1","param2"' without worrying about any VBA code. Sorry I confused things with the example I gave.. Thanks again, Steve "Dave Peterson" <ec35720@msn.com wrote in message news:106f001c21d1d$b90eb0c0$9ee62ecf@tkmsftngxa05... I posted this through google, but it seemed to crash. So I thought I'd use CDO: I _think_ I understand: I dropped a button from the forms toolbar onto a worksheet. I have 3 examples of the .onaction stuff. Comment out two of them. Option Explicit Sub testme() Dim cb As Button Set cb = ActiveSheet.Buttons(1) Dim c As Range Set c = Range("A1") c.Value = "rangeA1value" Dim i As Long i = 8 With cb .OnAction = ThisWorkbook.Name _ & "!'testme1 " & Chr(34) & c.Value & Chr(34) & "'" .OnAction = ThisWorkbook.Name _ & "!'testme2 ""Hello"", 3'" .OnAction = Workbooks("book2.xls").Name _ & "!'testme3 " & Chr(34) _ & "different wkbk" & Chr(34) & "'" End With End Sub Sub testme1(myval As Variant) MsgBox "From Testme1: " & myval End Sub Sub testme2(mystr As String, myval As Long) MsgBox "From Testme2: " & mystr & " " & myval * 10 End Sub and from Book2.xls Option Explicit Sub testme3(mystr As String) MsgBox "From " & ThisWorkbook.Name & ": " & mystr End Sub I've never used the CDO before. Watch out for linewrap problems. -----Original Message----- Thanks Dave, However my problem is not calling it from within a VBA macro but I want an Excel developer who may know nothing about VBA to pass parameters to a VBA macro contained in a loaded Add-in from a button he has added to the spreadsheet. The syntax I used ('MyAddin.xla'!'Foo "param1"') from within the Assign macro dialog of the excel button control seemed to be ignoring the 'MyAddin.xla' prefix and assuming it was in the workbook containing the button (Book1.xls). Steve What I was hoping for was "Dave Peterson" <ec35720@msn.com wrote in message news:3D18E8DF.4D4DEB54@msn.com... When I tried to pass a string to your Msg sub, I could do it like this: Application.Run "'book1.xla'!msg", "hi there!" I could also do it like this, if I did a Tools|references and set a reference to that other project. msg "hi there2" But I'm not sure if this was the answer to your question.... Steve Legg wrote: Hi, I am able to pass parameters to a VBA macro from the 'Assign macro' dialog by enclosing in single quotes. e.g. Book1!'Msg "Hello"' to execute the macro in the open workbook 'Book1': Public Sub Msg(m As String) MsgBox m End Sub However I am unable to do the same for a macro in an open Add-in. If I try 'MyAddin.xla'!'Foo "param1"' I am getting an error saying The macro 'Book1'!'Foo "param1"' cannot be found. I can invoke macros with no parameters in the Add-in. Anybody know why this is not working, and if there is a way to make it work? Thanks in advance. -- Dave Peterson ec35720@msn.com . |
| 837 | My sincere apologies as I was not sure whether the first request got posted being a newbie to the group. One thing I have learned is that this is a wonderful group of people who have a genuine interest in helping people with Excel issues. Thanks ever so gratefully. wrote: This would have best been tacked onto to your previous incomplete question. Which now leaves both questions receiving answers to which you acknowledged in this question that both worked for what you wanted. Splitting up a single question to rephrase it makes it more difficult for people to find answers in the newsgroup archives /advanced_group_search I see now that the question about removing empty rows based on Column A or a selection was really what you were looking for and you got answers to that for both postings; however, since you did ask about removing alternating rows here is answer to the second question. (For the benefit of those who look in the archives). Again question is ambiguous anyway since there are even rows or odd rows that could be removed so strict interpretation means the even rows are to be removed. Delete Even rows up to row 2 from the bottom. Sub DelEvenRows() ' 2002-03-11 mod 2002-06-26 'Delete Even numbered rows from the bottom Application.ScreenUpdating = False Dim ix As Long ix = Cells.SpecialCells(xlLastCell).Row - _ Cells.SpecialCells(xlLastCell).Row Mod 2 For ix = ix To 2 Step -2 Rows(ix).Delete Next ix Application.ScreenUpdating = True End Sub HTH, Thanks for anwering that you got your real answer previously. , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Reg Sinha" <reginald.sinha@opbu.xerox.com wrote in message Please advise as to how I can go about deleting empty rows between alternate rows of data for a file created by a Crystal Report. |
| 909 | Assign these macros to the buttons Sub First() Range("C7") = Range("N3") End Sub Sub Last() Range("C7") = Range("N3") + 15 End Sub HTH "Greg Rivet" <gregrivet@hotmail.com wrote in message news:#n9AcRfHCHA.2496@tkmsftngp08... I have an option button from the forms toolbar on my sheet. There are 2 options, First and Last. If First is chosen, I would like cell C7 to reflect the first date of the month, which is indicated in Cell N3. If Last is chosen, I would like the 16th of the month in Cell C7. How do I accomplish this feat. TIA Greg |
| 914 | Wilson, your macros work perfectly. Now how do I improve on them and fill C8 to C??? for the balance of the pay period. In other words your macros fill the First for the first day of the month and Last, the 16th. Now I want to fill column C to the 15th for the First and 16th to end of month for Last. TIA Greg "Wilson" <jwilson@wickes.com wrote in message news:uSwOcZfHCHA.2644@tkmsftngp10... Assign these macros to the buttons Sub First() Range("C7") = Range("N3") End Sub Sub Last() Range("C7") = Range("N3") + 15 End Sub HTH "Greg Rivet" <gregrivet@hotmail.com wrote in message news:#n9AcRfHCHA.2496@tkmsftngp08... I have an option button from the forms toolbar on my sheet. There are 2 options, First and Last. If First is chosen, I would like cell C7 to reflect the first date of the month, which is indicated in Cell N3. If Last is chosen, I would like the 16th of the month in Cell C7. How do I accomplish this feat. TIA Greg |
| 961 | I have an excel spreadsheet where I have written 22 macros and each macro sorts the worksheet on a different column depending on the control and keystroke you use. For example, control A sorts column A, control B sorts column B, etc. The macros work MOST of the time. But sometimes row 1, e.g., doesn't sort whereas rows 2-17 of that column do. Or sometimes you have to hit the control and keystroke more than once to get it to work, which it does. It does seem that if I initially sort column A, then subsequent columns will sort as requested. I am concerned with the audience who will be using the worksheet and may not have experience in playing around with these issues. Any insights? |
| 967 | Here you go, Greg Sub First() Dim r As Integer r = 7 Range(Cells(6, 3), Cells(25, 3)).ClearContents Cells(r, 3) = Cells(3, 14) For r = 8 To 21 Cells(r, 3) = Cells(r - 1, 3) + 1 Next r End Sub Sub Last() Dim r As Integer Dim e r = 7 Range(Cells(6, 3), Cells(25, 3)).ClearContents Cells(r, 3) = Cells(3, 14) + 15 Cells(r - 1, 3).FormulaR1C1 = "=DAY(DATE(YEAR(R[-3]C[11]),MONTH(R[-3]C[11])+1,0))" e = Cells(r - 1, 3) For r = 8 To e - 9 Cells(r, 3) = Cells(r - 1, 3) + 1 Next r Cells(6, 3).ClearContents End Sub HTH "Greg Rivet" <gregrivet@hotmail.com wrote in message news:ecoKAgfHCHA.2240@tkmsftngp08... Wilson, your macros work perfectly. Now how do I improve on them and fill C8 to C??? for the balance of the pay period. In other words your macros fill the First for the first day of the month and Last, the 16th. Now I want to fill column C to the 15th for the First and 16th to end of month for Last. TIA Greg "Wilson" <jwilson@wickes.com wrote in message news:uSwOcZfHCHA.2644@tkmsftngp10... Assign these macros to the buttons Sub First() Range("C7") = Range("N3") End Sub Sub Last() Range("C7") = Range("N3") + 15 End Sub HTH "Greg Rivet" <gregrivet@hotmail.com wrote in message news:#n9AcRfHCHA.2496@tkmsftngp08... I have an option button from the forms toolbar on my sheet. There are 2 options, First and Last. If First is chosen, I would like cell C7 to reflect the first date of the month, which is indicated in Cell N3. If Last is chosen, I would like the 16th of the month in Cell C7. How do I accomplish this feat. TIA Greg |
| 982 | Awesome! That is exactly what I was looking for! Thanks much! Sue. -----Original Message----- Susan, you need to add the qualifier if the statement is false. In other words adjust your formula accordingly: =IF(B13 <"", NOW(),"") HTH Greg "Susan Clause" <claussu@em.agr.ca wrote in message news:1227d01c21c7b$5fe00ff0$9be62ecf@tkmsftngxa03... Thanks John! It worked perfectly (following some minor modifications of course). Maybe you can help with this little problem -- I have an SS and I am testing a date cell for content so my formula goes =IF(B13 <"", NOW()) It works perfectly, except that it puts "FALSE" in the cell if B13 is empty. Any suggestions? Sue. -----Original Message----- Maybe something like this... -walk.com/ss/excel/usertips/tip035.htm John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Susan Clause" <claussu@em.agr.ca wrote in message news:1259f01c21c5c$5ae08df0$39ef2ecf@TKMSFTNGXA08... Does anyone know if it possible to have an autocounter field in an XL worksheet? What I have is a sheet that lists various items, and I want each item number as it is added, and if someone was to insert another item, the following items would be re-numbered accordingly. Thanks for your help! . . |
| 1007 | Wilson, both of the cells N3 and C7 are formatted as a date does this make a difference and in your Last Sub, I have a rap problem. Maybe you could help with these suggestions. TIA Greg "Wilson" <jwilson@wickes.com wrote in message news:eDda3UpHCHA.2464@tkmsftngp10... Can't be of any help. Works fine on my Excel 2000 Windows 2K PRO ????? "Greg Rivet" <gregrivet@hotmail.com wrote in message news:#yqd5UiHCHA.1740@tkmsftngp10... Wilson, Sub first works great, Sub Last, I get a "Type Mismatch" error. Greg "Wilson" <jwilson@wickes.com wrote in message news:eAL5yuhHCHA.2272@tkmsftngp09... Here you go, Greg Sub First() Dim r As Integer r = 7 Range(Cells(6, 3), Cells(25, 3)).ClearContents Cells(r, 3) = Cells(3, 14) For r = 8 To 21 Cells(r, 3) = Cells(r - 1, 3) + 1 Next r End Sub Sub Last() Dim r As Integer Dim e r = 7 Range(Cells(6, 3), Cells(25, 3)).ClearContents Cells(r, 3) = Cells(3, 14) + 15 Cells(r - 1, 3).FormulaR1C1 = "=DAY(DATE(YEAR(R[-3]C[11]),MONTH(R[-3]C[11])+1,0))" e = Cells(r - 1, 3) For r = 8 To e - 9 Cells(r, 3) = Cells(r - 1, 3) + 1 Next r Cells(6, 3).ClearContents End Sub HTH "Greg Rivet" <gregrivet@hotmail.com wrote in message news:ecoKAgfHCHA.2240@tkmsftngp08... Wilson, your macros work perfectly. Now how do I improve on them and fill C8 to C??? for the balance of the pay period. In other words your macros fill the First for the first day of the month and Last, the 16th. Now I want to fill column C to the 15th for the First and 16th to end of month for Last. TIA Greg "Wilson" <jwilson@wickes.com wrote in message news:uSwOcZfHCHA.2644@tkmsftngp10... Assign these macros to the buttons Sub First() Range("C7") = Range("N3") End Sub Sub Last() Range("C7") = Range("N3") + 15 End Sub HTH "Greg Rivet" <gregrivet@hotmail.com wrote in message news:#n9AcRfHCHA.2496@tkmsftngp08... I have an option button from the forms toolbar on my sheet. There are 2 options, First and Last. If First is chosen, I would like cell C7 to reflect the first date of the month, which is indicated in Cell N3. If Last is chosen, I would like the 16th of the month in Cell C7. How do I accomplish this feat. TIA Greg |
| 1008 | Wilson, ignore my last post. It was a rap problem. Works like a dream.............Thanks Greg "Wilson" <jwilson@wickes.com wrote in message news:eDda3UpHCHA.2464@tkmsftngp10... Can't be of any help. Works fine on my Excel 2000 Windows 2K PRO ????? "Greg Rivet" <gregrivet@hotmail.com wrote in message news:#yqd5UiHCHA.1740@tkmsftngp10... Wilson, Sub first works great, Sub Last, I get a "Type Mismatch" error. Greg "Wilson" <jwilson@wickes.com wrote in message news:eAL5yuhHCHA.2272@tkmsftngp09... Here you go, Greg Sub First() Dim r As Integer r = 7 Range(Cells(6, 3), Cells(25, 3)).ClearContents Cells(r, 3) = Cells(3, 14) For r = 8 To 21 Cells(r, 3) = Cells(r - 1, 3) + 1 Next r End Sub Sub Last() Dim r As Integer Dim e r = 7 Range(Cells(6, 3), Cells(25, 3)).ClearContents Cells(r, 3) = Cells(3, 14) + 15 Cells(r - 1, 3).FormulaR1C1 = "=DAY(DATE(YEAR(R[-3]C[11]),MONTH(R[-3]C[11])+1,0))" e = Cells(r - 1, 3) For r = 8 To e - 9 Cells(r, 3) = Cells(r - 1, 3) + 1 Next r Cells(6, 3).ClearContents End Sub HTH "Greg Rivet" <gregrivet@hotmail.com wrote in message news:ecoKAgfHCHA.2240@tkmsftngp08... Wilson, your macros work perfectly. Now how do I improve on them and fill C8 to C??? for the balance of the pay period. In other words your macros fill the First for the first day of the month and Last, the 16th. Now I want to fill column C to the 15th for the First and 16th to end of month for Last. TIA Greg "Wilson" <jwilson@wickes.com wrote in message news:uSwOcZfHCHA.2644@tkmsftngp10... Assign these macros to the buttons Sub First() Range("C7") = Range("N3") End Sub Sub Last() Range("C7") = Range("N3") + 15 End Sub HTH "Greg Rivet" <gregrivet@hotmail.com wrote in message news:#n9AcRfHCHA.2496@tkmsftngp08... I have an option button from the forms toolbar on my sheet. There are 2 options, First and Last. If First is chosen, I would like cell C7 to reflect the first date of the month, which is indicated in Cell N3. If Last is chosen, I would like the 16th of the month in Cell C7. How do I accomplish this feat. TIA Greg |
| 1012 | In the userform's code module, assuming that your text box is named textBox1, add the following code: Private Sub TextBox1_Change() 'call the validate routine and pass to it the textbox object Validate_Number_Entry Me.TextBox1 End Sub In a regular module, put the following: Sub Validate_Number_Entry(oBox) Dim tempS As String 'store the text in a variable for later use tempS = oBox.Text 'if user has removed all entries, just exit If tempS = "" Then Exit Sub 'if the entry is numeric, then exit If IsNumeric(tempS) Then Exit Sub 'if the entry is not numeric, remove the last entry oBox.Text = Mid(tempS, 1, Len(tempS) - 1) End Sub Bob Flanagan macro Systems -ins.com Productivity add-ins and downloadable books on VB macros for Excel "phil" <pperry@acsoft.co.uk wrote in message news:1166b01c21ebd$49fc29f0$36ef2ecf@tkmsftngxa12... Hi, how can i allow only numbers to be entered into a textbox on a user form? thanks phil |
| 1015 | excellent, thanks phil -----Original Message----- In the userform's code module, assuming that your text box is named textBox1, add the following code: Private Sub TextBox1_Change() 'call the validate routine and pass to it the textbox object Validate_Number_Entry Me.TextBox1 End Sub In a regular module, put the following: Sub Validate_Number_Entry(oBox) Dim tempS As String 'store the text in a variable for later use tempS = oBox.Text 'if user has removed all entries, just exit If tempS = "" Then Exit Sub 'if the entry is numeric, then exit If IsNumeric(tempS) Then Exit Sub 'if the entry is not numeric, remove the last entry oBox.Text = Mid(tempS, 1, Len(tempS) - 1) End Sub Bob Flanagan macro Systems -ins.com Productivity add-ins and downloadable books on VB macros for Excel "phil" <pperry@acsoft.co.uk wrote in message news:1166b01c21ebd$49fc29f0$36ef2ecf@tkmsftngxa12... Hi, how can i allow only numbers to be entered into a textbox on a user form? thanks phil . |
| 1018 | There could be any number of explanations for that. If you describe what happens when you attempt to move or access things, that would help to isolate the problem. John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Earl" <burkhardtef@sbcglobal.net wrote in message news:113c501c21ec2$68093dc0$a5e62ecf@tkmsftngxa07... I've been keeping an expense spreadsheet, all in one document, for about 18 months now. As of today I can't move or access anything in the spreadsheet. Does excel have size limitations? Has anyone seen this before? I'd appreciate any response/help you could give me. I guess in the meantime I'll just start a new sheet. Thanks in advance... Earl |
| 1020 | That's good news, Greg. Enjoy! "Greg Rivet" <gregrivet@hotmail.com wrote in message news:eg9JpwrHCHA.2468@tkmsftngp13... Wilson, ignore my last post. It was a rap problem. Works like a dream.............Thanks Greg "Wilson" <jwilson@wickes.com wrote in message news:eDda3UpHCHA.2464@tkmsftngp10... Can't be of any help. Works fine on my Excel 2000 Windows 2K PRO ????? "Greg Rivet" <gregrivet@hotmail.com wrote in message news:#yqd5UiHCHA.1740@tkmsftngp10... Wilson, Sub first works great, Sub Last, I get a "Type Mismatch" error. Greg "Wilson" <jwilson@wickes.com wrote in message news:eAL5yuhHCHA.2272@tkmsftngp09... Here you go, Greg Sub First() Dim r As Integer r = 7 Range(Cells(6, 3), Cells(25, 3)).ClearContents Cells(r, 3) = Cells(3, 14) For r = 8 To 21 Cells(r, 3) = Cells(r - 1, 3) + 1 Next r End Sub Sub Last() Dim r As Integer Dim e r = 7 Range(Cells(6, 3), Cells(25, 3)).ClearContents Cells(r, 3) = Cells(3, 14) + 15 Cells(r - 1, 3).FormulaR1C1 = "=DAY(DATE(YEAR(R[-3]C[11]),MONTH(R[-3]C[11])+1,0))" e = Cells(r - 1, 3) For r = 8 To e - 9 Cells(r, 3) = Cells(r - 1, 3) + 1 Next r Cells(6, 3).ClearContents End Sub HTH "Greg Rivet" <gregrivet@hotmail.com wrote in message news:ecoKAgfHCHA.2240@tkmsftngp08... Wilson, your macros work perfectly. Now how do I improve on them and fill C8 to C??? for the balance of the pay period. In other words your macros fill the First for the first day of the month and Last, the 16th. Now I want to fill column C to the 15th for the First and 16th to end of month for Last. TIA Greg "Wilson" <jwilson@wickes.com wrote in message news:uSwOcZfHCHA.2644@tkmsftngp10... Assign these macros to the buttons Sub First() Range("C7") = Range("N3") End Sub Sub Last() Range("C7") = Range("N3") + 15 End Sub HTH "Greg Rivet" <gregrivet@hotmail.com wrote in message news:#n9AcRfHCHA.2496@tkmsftngp08... I have an option button from the forms toolbar on my sheet. There are 2 options, First and Last. If First is chosen, I would like cell C7 to reflect the first date of the month, which is indicated in Cell N3. If Last is chosen, I would like the 16th of the month in Cell C7. How do I accomplish this feat. TIA Greg |
| 1026 | I have a spreadsheet that has a custom toolbar and several buttons on it which are assigned to run macros for that particular spreadsheet. If I take the same spreadsheet and open it an a different machine, the toolbar is not in the list of available toolbars. I don't want to have re- create it each time. How can I get the toolbar to be recognized, even if I need to use this spreadsheet on several different machines? |
| 1066 | I have created two macros. I have created to Command Buttons. But, I can not assign the macros to the buttons. I checked the help menu and it said to right click on the object and select assign macro on the shortcut menu. But, there is no option for "assign macro" on the menu that comes up. Does any one know how to do this? Thank you so much for the help, in advance. |
| 1067 | Attach the toolbar to the workbook from the Customize dialog. Note that if you make changes to the toolbar after attaching it, you will need to delete the old version and attach the new one. HTH, Greg "Liz" <liz@techie.com wrote in message news:1137801c21ec9$4108f100$9ee62ecf@tkmsftngxa05... I have a spreadsheet that has a custom toolbar and several buttons on it which are assigned to run macros for that particular spreadsheet. If I take the same spreadsheet and open it an a different machine, the toolbar is not in the list of available toolbars. I don't want to have re- create it each time. How can I get the toolbar to be recognized, even if I need to use this spreadsheet on several different machines? |
| 1068 | My guess is that your CommandButtons came from the Control Toolbox toolbar. You can't assign a macro to those -- they always execute a particularly-named macro (e.g., CommandButton1_Click). Usually, it's much simpler to use the Button controls from the Forms toolbar. You can assign any macro to these buttons. If you're interested, here's a link to some background reading that describes the differences between these two types of controls: -walk.com/ss/excel/tips/tip84.htm John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Brady McGee" <bmcgee@storageexpress.com wrote in message news:1347a01c21ee0$b9ecb2c0$b1e62ecf@tkmsftngxa04... I have created two macros. I have created to Command Buttons. But, I can not assign the macros to the buttons. I checked the help menu and it said to right click on the object and select assign macro on the shortcut menu. But, there is no option for "assign macro" on the menu that comes up. Does any one know how to do this? Thank you so much for the help, in advance. |
| 1071 | If you use the Forms toolbar rather than the Control Toolbox, that option will appear when you right click the command button HTH "Brady McGee" <bmcgee@storageexpress.com wrote in message news:1347a01c21ee0$b9ecb2c0$b1e62ecf@tkmsftngxa04... I have created two macros. I have created to Command Buttons. But, I can not assign the macros to the buttons. I checked the help menu and it said to right click on the object and select assign macro on the shortcut menu. But, there is no option for "assign macro" on the menu that comes up. Does any one know how to do this? Thank you so much for the help, in advance. |
| 1121 | Hi Deb, I don't see any use for ABS in your time sheet . If the later time looks like an earlier time you add one day to the later time. Time is stored as fractions of a day so adding 24 hours is same as adding 1 with a logical test (D10E10) =(E10-B10)-(D10-C10) 'what a convoluted formula change to =E10-D10+(D10E10) + C10 - B10 + (B10 C10) If you want to convert an Excel time to hours for calculation with an hourly rate of pay then multiply the Excel time by 24. more information in and may even match your data Date & Time /dmcritchie/excel/datetime.htm --- HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Deb" <clifford@git.com.au wrote in message news:u$Cn2aDICHA.2404@tkmsftngp08... Robert Thanks for your help but I am still having problems (I am still learning excel : ( Currently I have the following: Hours Carried Forward: 01:00 Day Start Stop (lunch) Resume Finish Days Total (hrs) "Hours up or down (flexi-time)" Monday 07:30 12:00 12:30 15:30 07:30 01:00 =(E10-B10)-(D10-C10) =F10-E5+C8 (E5 being a cell with 07:30) At what point do I put the ABS ??? Thanks Deb "RobBlue" <robblue@wanadoo.nl wrote in message news:l%wT8.556$LX5.3809@castor.casema.net... "Deb" <clifford@git.com.au wrote in message news:edOOuD#HCHA.2456@tkmsftngp08... One other quick question if you don't mind ??? If I was to add another column on for number of hours up (flexi-time) how would I do that ? eg. start lunch resume end total hours worked Hours up 07:30 12:00 12:30 15:30 07:30 Total hours - standard day = hours up ??? standard day is 07:30hrs Thanks for your help Debbie Be aware that negative times cannot be displayed as such. Say that a standard days has 7:30 hours and total hours worked would be 7:15 on a given day. That means -0:15 in the hours up column. In Excel it will be displayed as #### So, use the ABS function to get rid of the negative sign (-- ABS(-5) = 5) =ABS(hours-standard day) and an IF function in a next column to show a plus or minus sign =IF((hours-standard day)<0;"-";"+") Use the Paste Function button to be sure to get the seperators (semicolon in my example) right for your Excel version. Hope this helps, Robert |
| 1123 | If you have strictly positive numbers: [9999999.99]##\,##\,##\,##0.00;[99999.99]"##\,##\,##0.00;"##,##0.00 (This format separates groups for India/Thailand, format valid for positive numbers up to 99,99,99,999.99 or 999,999,999.99) in Excel XP see function BhatText as in Thai Bhat currency. This is the HELP as seen in Excel 2002 [I do not have 2002 on my computer] /assistance/offhelp/offxp/excel/xlmain10/html/Bahttext.htm HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Niek Otten" <nicolaus@xs4all.nl wrote in message news:eiW8i1DICHA.2060@tkmsftngp11... Enclose the commas in (double) quotes, like: _-$* ##","##","##","##0.00_-;-$* #,##0.00_-;_-$* "-"??_-;_-@_- -- Regards, Niek Otten Microsoft MVP - Excel "CD" <soxplato@yahoo.com wrote in message news:118c001c2203b$e94ea580$9ee62ecf@tkmsftngxa05... Hi I created a custom format and positioned the commas to where I wanted them. But the comma positions keep getting reset to the thousand's place. This _-$* ##,##,##,##0.00_-;-$* #,##0.00_-;_-$* "-"??_-;_-@_- gets automatically changed to _-$* ###,###,##0.00_-;-$* #,##0.00_-;_-$* "-"??_-;_-@_- Any help would be appreciated. Thanks. |
| 1131 | illeagle operation as in legal beagle ? Since a zip drive is much more reliable than a floppy you probably aren't putting your data at as much risk as using a floppy, but your hard disk would be better. If you are going to remove the zip drive then you must use the hard drive. Note workaround to close files before taking removable media offline. ---- XL97: Ejecting Removable Media Causes Excel Page Fault (Q165205) /default.aspx?scid=kb;en-us;Q165205 When you eject the disk for removable media while a file from Microsoft Excel 97 is open; you may receive the following error message: Excel caused an invalid page fault in module EXCEL.EXE at 0137:3010E242 Note: The file does not have to be opened from the disk ejected. WORKAROUND To work around this problem, close the file in Microsoft Excel before ejecting the disk from the removable media. ---- XL2000: Files Corrupted on Save: How to Troubleshoot Source of Problem (Q213951) /default.aspx?scid=kb;EN-US;q213951 Microsoft Excel uses a technique called "back seeking" when reading and writing files. "Back seeking" means that when Microsoft Excel opens a file and reads from it, it does not necessarily read or write in a sequential manner. It may start at the first record in the file, jump to the fifteenth record, go back to the third record, and so on. This "jumping back" part may be what is causing the intermittent corruption of files especially when you are saving to a floppy disk drive. Microsoft Excel is one of a few programs that uses this technique when reading and writing files. Often these disk problems appear to exist only in Microsoft Excel. Having the floppy disk drive heads realigned should correct this problem. --- Excel opens the file on your zip drive and stores the old copy in some nonsense 8 letter filename without a file extension. So now you have two copies of your file out there. With a floppy besides being unreliable they are smaller and may run out of space. When your data is saved the old copy is removed afterwards. If you do not save then the old copy remains. I may be incorrect as which file gets the nonsense name and which file is renamed, but the effect is about the same either way. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "spookn" <spook@attnospam.net wrote in message news:O5hWUQFICHA.2496@tkmsftngp08... Hi, I realize this is a little generic of a question, but... I am volunteering at a library and came across this problem. I am not sure this question belongs here, but I wasn't sure of where it should go. While on the one PC, if you are using Excel (quite often) and remove a zip disk, it will shut off excel (without saveing of course) and give a illeagle opperation window, with the excel icon at the top-indicating that excel is the culprit I guess. If I remember correctly, you get a illeagle operation when two programs are trying for the same resourse? I know that is not much info to go on. But it happens every time, be using excel, with a zip disk in the dr, remove the disk and excel goes off and the illeagle op window comes up. I am hopeing someone has an idea for me to try. -- Peace be with you |
| 1133 | Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1134 | Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1137 | Are these two sheets self contained with each other or are other sheets involved. I've done similar things in the past, copying a worksheet & graph together to make a new set. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:ufA$88FICHA.1772@tkmsftngp09... Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1138 | There's no built-in relative sheet addressing. You would have to accomplish this a VBA macro that copies the sheet, then changes the name definition and/or formulas. On Sun, 30 Jun 2002 18:56:24 +0100, "Robert Brown" <robert@nospam-southall-brown.co.uk wrote: Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1153 | I am having several problems in Excel 2002 under Win XP Pro which I never had in earlier versions of these programs. I will submit them one at a time here looking and hoping for help. The first: I have a file of autoshapes filled with text that I use either in macros or to cut and paste as "stick-ons" or "labels" which are placed on billing statements - usually over old text which does not need to be presently read. Worked fine for years with black text (or color) on white opaque background. Somewhere along the line these have all become transparent "labels" so that all underlying text, lines, etc. show "through", making the stuff an unreadable mess. I have looked at all properties menus I can find to set these as not transparent with various foregrounds/backgrounds and moving focus back, forward, etc. I have recreated them in new sheets, I have reset properties in book and book1.xlt and personal.xls and whatever I can think of - but I cannot any longer paste an autoshape in XL2002 that is not transparent. Text boxes work fine but I need to have variable shapes. Any ideas on either cause or solution. WHat's worse, I think, but am not sure, that when I first set the XP computer up and transferred these spreadsheets, they worked fine. |
| 1155 | 1. Double-click the AutoShape 2. On the Colors and Lines tab, set the Transparency slider to 0% wbbirner@rtconline.com wrote: I am having several problems in Excel 2002 under Win XP Pro which I never had in earlier versions of these programs. I will submit them one at a time here looking and hoping for help. The first: I have a file of autoshapes filled with text that I use either in macros or to cut and paste as "stick-ons" or "labels" which are placed on billing statements - usually over old text which does not need to be presently read. Worked fine for years with black text (or color) on white opaque background. Somewhere along the line these have all become transparent "labels" so that all underlying text, lines, etc. show "through", making the stuff an unreadable mess. I have looked at all properties menus I can find to set these as not transparent with various foregrounds/backgrounds and moving focus back, forward, etc. I have recreated them in new sheets, I have reset properties in book and book1.xlt and personal.xls and whatever I can think of - but I cannot any longer paste an autoshape in XL2002 that is not transparent. Text boxes work fine but I need to have variable shapes. Any ideas on either cause or solution. WHat's worse, I think, but am not sure, that when I first set the XP computer up and transferred these spreadsheets, they worked fine. -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 1159 | That was the first thing I checked. Already there. (I only look not too bright :-) ) On Sun, 30 Jun 2002 17:56:13 -0400, Debra Dalgleish <dsd@contextures.com wrote: 1. Double-click the AutoShape 2. On the Colors and Lines tab, set the Transparency slider to 0% wbbirner@rtconline.com wrote: I am having several problems in Excel 2002 under Win XP Pro which I never had in earlier versions of these programs. I will submit them one at a time here looking and hoping for help. The first: I have a file of autoshapes filled with text that I use either in macros or to cut and paste as "stick-ons" or "labels" which are placed on billing statements - usually over old text which does not need to be presently read. Worked fine for years with black text (or color) on white opaque background. Somewhere along the line these have all become transparent "labels" so that all underlying text, lines, etc. show "through", making the stuff an unreadable mess. I have looked at all properties menus I can find to set these as not transparent with various foregrounds/backgrounds and moving focus back, forward, etc. I have recreated them in new sheets, I have reset properties in book and book1.xlt and personal.xls and whatever I can think of - but I cannot any longer paste an autoshape in XL2002 that is not transparent. Text boxes work fine but I need to have variable shapes. Any ideas on either cause or solution. WHat's worse, I think, but am not sure, that when I first set the XP computer up and transferred these spreadsheets, they worked fine. -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 1166 | Since TextBoxes work properly, what happens if you paste a textbox, then choose DrawChange AutoShape, and select a different shape? rambeaux@rtconline.com wrote: That was the first thing I checked. Already there. (I only look not too bright :-) ) On Sun, 30 Jun 2002 17:56:13 -0400, Debra Dalgleish <dsd@contextures.com wrote: 1. Double-click the AutoShape 2. On the Colors and Lines tab, set the Transparency slider to 0% wbbirner@rtconline.com wrote: I am having several problems in Excel 2002 under Win XP Pro which I never had in earlier versions of these programs. I will submit them one at a time here looking and hoping for help. The first: I have a file of autoshapes filled with text that I use either in macros or to cut and paste as "stick-ons" or "labels" which are placed on billing statements - usually over old text which does not need to be presently read. Worked fine for years with black text (or color) on white opaque background. Somewhere along the line these have all become transparent "labels" so that all underlying text, lines, etc. show "through", making the stuff an unreadable mess. I have looked at all properties menus I can find to set these as not transparent with various foregrounds/backgrounds and moving focus back, forward, etc. I have recreated them in new sheets, I have reset properties in book and book1.xlt and personal.xls and whatever I can think of - but I cannot any longer paste an autoshape in XL2002 that is not transparent. Text boxes work fine but I need to have variable shapes. Any ideas on either cause or solution. WHat's worse, I think, but am not sure, that when I first set the XP computer up and transferred these spreadsheets, they worked fine. -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 1195 | Hi, You can refer to the previous and next sheet using defined names with XL4 formula's: Define these names: ThisSheet =GET.CELL(32+0*now();indirect("rc";False)) AllSheets =GET.WORKBOOK(1+0*now()) PrevSheet =INDEX(AllSheets;MATCH(ThisSheet;AllSheets;0)-1+0*now()) NextSheet =INDEX(AllSheets;MATCH(ThisSheet;AllSheets;0)+1+0*now()) Now refer to cel B1 on the next sheet using: =INDIRECT("'"&NextSheet&"'!"&CELL("address";B1)) Warning: Copying a cell that contains one of these names to another worksheet will cause XL2000 to crash. Copying a sheet with that name in a cell did not cause trouble with my XL2000 though. Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)- 1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! . |
| 1200 | If you mean do they reference other sheets then the answer is that they are self contained and it sounds very similar to the task you've done previously with copying a graph, in fact, it is almost the same because a couple of buttons on the summary sheet have the ability to produce graphs from this sheet. But at the end of the day, yes, these two sheets could operate without any others existing. Robert "" <dmcritchie@msn.com wrote in message news:OTrQvXGICHA.2488@tkmsftngp09... Are these two sheets self contained with each other or are other sheets involved. I've done similar things in the past, copying a worksheet & graph together to make a new set. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:ufA$88FICHA.1772@tkmsftngp09... Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1202 | Myrna I had wondered about doing this, but to put it bluntly, when I think about it my brain hurts ! Thinking aloud, I would have to go into the detailed sheet and change each range definition so that it applied to the new sheet - doable but I need to specify that it is the range on this sheet ie not the workbook level name, what is the syntax for that if I wanted to change range name 'LastRecord' on sheet 'Air Quality Detail' to refer to the range A2:C10 (for simplicity's sake - I could work it out for the actual range) ? I could then presumably refer to the range 'Air Quality Detail'!LastRecord on the summary sheet ? Robert "Myrna Larson" <myrnalarson@chartermi.net wrote in message news:ajkuhu4ev3gddbjc4hvg2qget8tmem79fa@4ax.com... There's no built-in relative sheet addressing. You would have to accomplish this a VBA macro that copies the sheet, then changes the name definition and/or formulas. On Sun, 30 Jun 2002 18:56:24 +0100, "Robert Brown" <robert@nospam-southall-brown.co.uk wrote: Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1213 | I had Office 2000 SBE installed and had made many custom templates. I installed Office 2000 PRO so I could get Access. Now I can't get VB to work? If I go into templates and try to run Calendar wizard I get "Errors occurred during load" then "Word could not fire the event". Trying to work with macros in VB (templates in Word) gives me"Could not open macro storage" KB had me unregister vbe6.dll file and rename and recreate. This didn't help. What should I do? I have uninstalled ALL office and reinstalled just the PRO. NO Good. Same with the SBE. ???? thanks. |
| 1214 | Rob Installing SBE tends to put lots of lovely add-ins and templates in the system, which are a pain when changing versions. If you no longer need them, load the new version of office and look for any strange .xla files or .dot files. These will be called things like sbfm.xla. (Small business financial manager), etc. If you still have these, start the process of removing them, by opening apps, Excel for example, going to ToolsAdd-ins and unchecking them so they don't load. This is probably safer than attempting to delete the add-ins, templates, etc. -- HTH Nick Hodge Southampton, England Microsoft MVP - Excel nick_hodge@btinternet.com Excel XP\WinXP "rob" <peterson@owc.net wrote in message news:#REsieSICHA.1600@tkmsftngp13... I had Office 2000 SBE installed and had made many custom templates. I installed Office 2000 PRO so I could get Access. Now I can't get VB to work? If I go into templates and try to run Calendar wizard I get "Errors occurred during load" then "Word could not fire the event". Trying to work with macros in VB (templates in Word) gives me"Could not open macro storage" KB had me unregister vbe6.dll file and rename and recreate. This didn't help. What should I do? I have uninstalled ALL office and reinstalled just the PRO. NO Good. Same with the SBE. ???? thanks. |
| 1221 | To use a sheet-level name, you include the sheet name as part if the name and part of the reference: Name is 'Air Quality Detail'!LastRecord and the reference is 'Air Quality Detail'!$A$2:$C$10 Be careful with relative references. They are relative to the cell that's active at the time you write the formula. If the cell won't change, use absolute, as I did above. You perhaps could use a macro to update and or create the names. On Mon, 1 Jul 2002 09:17:27 +0100, "Robert Brown" <robert@nospam-southall-brown.co.uk wrote: Myrna I had wondered about doing this, but to put it bluntly, when I think about it my brain hurts ! Thinking aloud, I would have to go into the detailed sheet and change each range definition so that it applied to the new sheet - doable but I need to specify that it is the range on this sheet ie not the workbook level name, what is the syntax for that if I wanted to change range name 'LastRecord' on sheet 'Air Quality Detail' to refer to the range A2:C10 (for simplicity's sake - I could work it out for the actual range) ? I could then presumably refer to the range 'Air Quality Detail'!LastRecord on the summary sheet ? Robert "Myrna Larson" <myrnalarson@chartermi.net wrote in message news:ajkuhu4ev3gddbjc4hvg2qget8tmem79fa@4ax.com... There's no built-in relative sheet addressing. You would have to accomplish this a VBA macro that copies the sheet, then changes the name definition and/or formulas. On Sun, 30 Jun 2002 18:56:24 +0100, "Robert Brown" <robert@nospam-southall-brown.co.uk wrote: Thanks David, however, the new summary sheet, literally named 'Summary(2)' is still referring to the data on the original 'Detailed History' and not 'Detailed History(2)'. I presume that this is due to the original range being a workbook level name (as it was the first one created) and the second range is a worksheet level name. What I am wondering is if I can change the range definition so that instead of reading 'Detailed History'!$B$1 it says something like Sheets(-1)!$B$1 (as the Detailed History sheet is always the one before the Summary sheet - structure is protected and code acts as a backup) Any thoughts ? Robert "" <dmcritchie@msn.com wrote in message news:ecy7V0FICHA.2240@tkmsftngp08... Hi Robert, Group the two sheets. Select one sheet, Hold Ctrl key and select the second. Copy your sheets (move or copy sheets) as before. DO NOT FORGET TO UNGROUP YOUR SHEETS. Failure to ungroup sheets will result in serious damage as any change made to a cell in one will change the other sheets as well as if also entered into the same cell. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Robert Brown" <robert@nospam-southall-brown.co.uk wrote in message news:e8AH5mFICHA.2464@tkmsftngp12... I have two sheets, sheet A is detailed information, sheet B is a summary sheet with the last 12 pieces of info from sheet A. This is effected by using dynamic ranges to pick up the last 12 rows ie the range address for the last row, called surprisingly 'LastRecord', = 'OFFSET('Detailed History'!$B$1,COUNTA('Detailed History'!$B:$B)-1,0,1,COUNTA('Detailed History'!$1:$1))' This is then picked up in the summary sheet by using the formula =IF(ISERR(LastRecordMinus1),0,LastRecord). This works fine when I have these two base sheets. How can I alter this so that when I copy the two sheets the summary refers to the correct 'Detailed History' ? Robert PS Sorry this was misposted in programming as well ! |
| 1228 | But you said the formatting was different in the 2 windows... were you wrong on that point? On Mon, 1 Jul 2002 12:38:58 -0500, "DRB" <drb@barkto.com wrote: Bingo! Overlooking the obvious, that's me... "Peo Sjoblom" <terre08@mvps.org wrote in message news:#jZXTwRICHA.2612@tkmsftngp08... If it wasn't for the different formatting I'd say that somebody opened two windows of the same file and saved it (windownew window), if that was the case you can open the file, press ctrl+F4 and save the file.. -- Regards, Peo Sjoblom "DRB" <drb@barkto.com wrote in message news:uzu$jmRICHA.1784@tkmsftngp12... I have a user with an Excel 97 workbook, and when we open it (in XL97 or XL2000) it opens 2 copies- but with different formatting. If I try to close on of the two copies, it closes both. I have macros turned off, so that wouldn't appear to be the answer. Any ideas? This is weird. David |
| 1245 | Just a guess, but maybe the second window had gridlines displayed. Maybe that was enough to make the OP think that the formats were different. (But that comment was enough for me not to guess (too?)) Myrna Larson wrote: But you said the formatting was different in the 2 windows... were you wrong on that point? On Mon, 1 Jul 2002 12:38:58 -0500, "DRB" <drb@barkto.com wrote: Bingo! Overlooking the obvious, that's me... "Peo Sjoblom" <terre08@mvps.org wrote in message news:#jZXTwRICHA.2612@tkmsftngp08... If it wasn't for the different formatting I'd say that somebody opened two windows of the same file and saved it (windownew window), if that was the case you can open the file, press ctrl+F4 and save the file.. -- Regards, Peo Sjoblom "DRB" <drb@barkto.com wrote in message news:uzu$jmRICHA.1784@tkmsftngp12... I have a user with an Excel 97 workbook, and when we open it (in XL97 or XL2000) it opens 2 copies- but with different formatting. If I try to close on of the two copies, it closes both. I have macros turned off, so that wouldn't appear to be the answer. Any ideas? This is weird. David -- Dave Peterson ec35720@msn.com |
| 1253 | I am having a problem with Excel 2000 When I receive a spreadsheet from a Excel 200 colleague I cannot access any buttons in the workbook. As I pass the pointer over the command button object, the cross turns to a arrow as if it should be selectable, but clicking does nothing. My security is set to low level. As a further test, I created a new worksheet and tried to create a button from the controls toolbox. I received an error message stating "Cannot insert object" I received the same message trying option button, radio buttons, etc. However, I am able to insert and attach macros to buttons added from the forms toolbox. I am confused, any ideas? I have tried reinstalling excel to see if it helps, but to no avail. It seems to be related to ActiveX controls only. I would look for any help anyone can offer. I am running windows 2000 pro on my laptop. |
| 1258 | Hi Ted You can use a sheets Index number or CodeName instead of it's Tab name. In regards to the Relative Recording the macro will be relative to you Activecell or selection when you start recording, so you can start with Absolute, select a specific cell then switch to Relative. Quite frankly though Ted, if you are having problems with recording macros, automating Pivot Tables might become a nightmare for you. They are really jumping in the VBA deep end. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Ted Chee" <tchee@jamcracker.com wrote in message news:1202a01c2216f$7342f690$a5e62ecf@tkmsftngxa07... | I want to record a macro to make pivot tables from .csv | files, then send this macro to people who have probably | never seen a pivot table so they will have an excellent | way to analyze data without knowing much about Excel. I'm | just using the "Record new macro" and "Stop recording" | feature. There are 2 problems I'm having. | | 1. The macro only works on spreadsheets with the same name. | | 2. The macro uses an absolute range for the pivot table. | I tried clicking on the Relative Reference icon when | making the macro, but the macro still uses absolute values. |
| 1315 | You can use the Call Function e.g Call McrName wtihin the Command button -----Original Message----- I have created two macros. I have created to Command Buttons. But, I can not assign the macros to the buttons. I checked the help menu and it said to right click on the object and select assign macro on the shortcut menu. But, there is no option for "assign macro" on the menu that comes up. Does any one know how to do this? Thank you so much for the help, in advance. . |
| 1327 | When I open a file with macros, the msg box asking if I want to enable the macros appears. If I say no to enable them, can I enable them at any point If so how. If I enable them and want to disable them, can I disable them at any pint and if so how. Thank you |
| 1338 | Set oBk = Workbooks.Open("/~rdbruin/ron.xls") This you can do if the server is running frontpage extensions you can edit the workbook and save it back to the server. Only they need the username and password Regards Ron "Steve Mathews" <Steve.mathews@knowledgenet.com schreef in bericht news:1472e01c2221c$7b3959e0$b1e62ecf@tkmsftngxa04... I need to be able to put a worksheet on the web that can be updated by remote users and brought back into Excel. It would be more or less one column of static text and one column that could be edited. So far all I can think of doing is writing a macro that exports a html page that is essentially a large form, then using php or pearl, recreating the worksheet. Does anyone know of another direction that I could go? I have moderate experience in VB and macros, and some experience in server scripting and am always willing to try something new so any ideas you have would be helpful. Thanks, Steve Mathews |
| 1341 | Whilst this looks as if it would provide the solution, unfortunately, I work for the National Health Service and I'll find it difficult to persuade my manager to pay for it! Thanks anyway. Any other suggestions from elswhere????? -----Original Message----- Airlie, take a look at the Data Loader on my site. Bob Flanagan macro Systems -ins.com Productivity add-ins and downloadable books on VB macros for Excel "B Airlie" <bernard.airlie@laht.scot.nhs.uk wrote in message news:1231301c221e0$8b661830$a5e62ecf@tkmsftngxa07... I am desperate! All I am trying to do is merge two different spreadsheets, where the common field is Order Number. Where the order nubmer exists on both sheets, I would like to transfer the Cost Centre from the 2nd sheet onto the first sheet and show it against the line on that sheet where the order number matches. Hopefully, the result would be as shown on the green area of the attached file. I've used the Excel Help facility and dabbled with Lookup, but with (very!) limited success. Any help would be greatly appreciated. Thanks. . |
| 1347 | Thanks for that. I got hold of a cleaner for 97 and that seems to have done the trick for now. Cheers -----Original Message----- It sounds like your file(s) got corrupted. Excel 2000 has a detect and repair feature, but I don't think Excel 97 has one. I've run into similar problems many times. I always fixed it by copying the data to a new spreadsheet and deleting the corrupted one. It can be a little work to format the new spreadsheet to be exactly like the previous one, but at least Excel won't crash and you won't lose any data. One word of warning before using the "Detect and Repair" feature in any MS program is to make a backup of your file before running it. Sometimes MS programs fix problems by deleting data. I've seen this happen numerous times when using Outlook's Detect and Repair. -----Original Message----- Hi Can anyone help with the aforementioned error message? The body of the error message states: The instruction at "0x302d75f8" referenced memory at "0x00000000". The memory could not be "read". This is from Excel 97, running Service pack 2b on Win NT4.0 SP6a. Its one of our users who keeps getting it and all she is doing is copying a Worksheet from one file into another. She can copy this sheet into a blank workbook, but always gets the same message when trying to put it into one in particular. She also gets the same types of messages while working with different spreadsheets and its usually around twice a day. She doesn't use macros, only VLookups. Any help would be greatly appreciated. Martin . . |
| 1391 | Nice! :) Thanks for the macro - Only problem is I can't position it to where I want it - unfortunately I don't understand much about macros,... could you tell me what variables I have to change to change the cell where it starts?! Thx Teaky |
| 1393 | It's here, Teaky Cells(Day(d) + 2, 1).Value = d Cells(Day(d) + 2, 2).Value = Format(d, "dddd") The first day (d) is 1, so (d) + 2 = 3 and Cells(3,1) means row 3 column 1 or A3 And Cells(3,2) means row 3 column 2 or B3 HTH "Teaky" <detoner.it@gmx.de wrote in message news:#7Q71XqICHA.2848@tkmsftngp12... Nice! :) Thanks for the macro - Only problem is I can't position it to where I want it - unfortunately I don't understand much about macros,... could you tell me what variables I have to change to change the cell where it starts?! Thx Teaky |
| 1408 | I did a search and took care of them. I uninstalled and did a completely new Off Pro install. I still get "COULD NOT OPEN macro STORAGE" when I go into macro, Visual Basic when I have a template loaded. Any other ideas???? thanks. "Nick Hodge" <nick_hodge@btinternet.com wrote in message news:uRl83sUICHA.2052@tkmsftngp08... Rob Installing SBE tends to put lots of lovely add-ins and templates in the system, which are a pain when changing versions. If you no longer need them, load the new version of office and look for any strange .xla files or .dot files. These will be called things like sbfm.xla. (Small business financial manager), etc. If you still have these, start the process of removing them, by opening apps, Excel for example, going to ToolsAdd-ins and unchecking them so they don't load. This is probably safer than attempting to delete the add-ins, templates, etc. -- HTH Nick Hodge Southampton, England Microsoft MVP - Excel nick_hodge@btinternet.com Excel XP\WinXP "rob" <peterson@owc.net wrote in message news:#REsieSICHA.1600@tkmsftngp13... I had Office 2000 SBE installed and had made many custom templates. I installed Office 2000 PRO so I could get Access. Now I can't get VB to work? If I go into templates and try to run Calendar wizard I get "Errors occurred during load" then "Word could not fire the event". Trying to work with macros in VB (templates in Word) gives me"Could not open macro storage" KB had me unregister vbe6.dll file and rename and recreate. This didn't help. What should I do? I have uninstalled ALL office and reinstalled just the PRO. NO Good. Same with the SBE. ???? thanks. |
| 1410 | Hello Ron, I am also interested in posting a functioning worksheet on my website for others to use. I clicked on your link below and the ron.xls can be changed, however, when I click save or close, my computer wants to save the changed spreadsheet to my computer, rather than the website. Quiestion(s) 1. What were the steps you took to add the spreadsheet to your website (my host has Front Page extensions) 2. How do I set it up so that specific users have their own spreadsheets which can only be opend with their username and password? TIA for your help. Emily Steed Carpe-Diem@gmx.co.uk On Wed, 3 Jul 2002 09:43:06 +0200, "Ron de Bruin" <rondebruin@kabelfoon.nl wrote: Set oBk = Workbooks.Open("/~rdbruin/ron.xls") This you can do if the server is running frontpage extensions you can edit the workbook and save it back to the server. Only they need the username and password Regards Ron "Steve Mathews" <Steve.mathews@knowledgenet.com schreef in bericht news:1472e01c2221c$7b3959e0$b1e62ecf@tkmsftngxa04... I need to be able to put a worksheet on the web that can be updated by remote users and brought back into Excel. It would be more or less one column of static text and one column that could be edited. So far all I can think of doing is writing a macro that exports a html page that is essentially a large form, then using php or pearl, recreating the worksheet. Does anyone know of another direction that I could go? I have moderate experience in VB and macros, and some experience in server scripting and am always willing to try something new so any ideas you have would be helpful. Thanks, Steve Mathews Emily Steed remove NOSPAM NOSPAMCarpe-Diem@gmx.co.uk |
| 1413 | the reason is that the example I posted is on a site with no frontpage extensions try this one Public Sub Openfile() Set oBk = Workbooks.Open("/files/book1.xls") End Sub you can't get in because you don't have the password it will open read only if you cancel it. If you have the password and username the file wil be open normal and you can save it back to the server if you change things. Try it on your site. Regards Ron "Emily Steed" <Carpe-Diem@gmx.co.uk schreef in bericht news:3d2331c7.3521413@NEWS.CIS.DFN.DE... Hello Ron, I am also interested in posting a functioning worksheet on my website for others to use. I clicked on your link below and the ron.xls can be changed, however, when I click save or close, my computer wants to save the changed spreadsheet to my computer, rather than the website. Quiestion(s) 1. What were the steps you took to add the spreadsheet to your website (my host has Front Page extensions) 2. How do I set it up so that specific users have their own spreadsheets which can only be opend with their username and password? TIA for your help. Emily Steed Carpe-Diem@gmx.co.uk On Wed, 3 Jul 2002 09:43:06 +0200, "Ron de Bruin" <rondebruin@kabelfoon.nl wrote: Set oBk = Workbooks.Open("/~rdbruin/ron.xls") This you can do if the server is running frontpage extensions you can edit the workbook and save it back to the server. Only they need the username and password Regards Ron "Steve Mathews" <Steve.mathews@knowledgenet.com schreef in bericht news:1472e01c2221c$7b3959e0$b1e62ecf@tkmsftngxa04... I need to be able to put a worksheet on the web that can be updated by remote users and brought back into Excel. It would be more or less one column of static text and one column that could be edited. So far all I can think of doing is writing a macro that exports a html page that is essentially a large form, then using php or pearl, recreating the worksheet. Does anyone know of another direction that I could go? I have moderate experience in VB and macros, and some experience in server scripting and am always willing to try something new so any ideas you have would be helpful. Thanks, Steve Mathews Emily Steed remove NOSPAM NOSPAMCarpe-Diem@gmx.co.uk |
| 1418 | I have code that is written for Excel95 that does not reliably run in Excel97 for higher and have not found why. I have an application where I need to know if a change has been made on a worksheet so that the macros will know to update the other parts of the worksheet (cell formulas will not accomplish what I need to do). I know how to do this in Excel97 but not in Excel95. |
| 1421 | Hi Bosley, Only by restarting the printing to print another page changing the options in between. Look for some Myrna Larson links in my pathname.htm if really interested. You will find other links somewhat more suited in the archives, but this will certainly give you an idea of what is involved. You would have to force page breaks if size of pages would change when you change your options. If you just want the first page to be different you can use the first rows of the spreadsheet for the the header on first page then perhaps rows 48:49 are to be repeated for rest of pages. page setup, rows to repeat at top $48:$49 select row 48, insert page Not sure of full extent of Excel 2002 improvements for headers and footers, but think you will only see improvements in printing graphics in actual headers and footers and obtaining the full pathname without using a macro. /assistance/offhelp/offxp/excel/xlmain10/html/InsertAGraphicInAHeaderOrFooter.htm /assistance/offhelp/offxp/excel/xlmain10/html/InsertAFilenameInAHeaderOrFooter.htm HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Bosley" <presstools@clara.net wrote in message news:1025695903.5242.0@iapetus.uk.clara.net... Is it possible on a single worksheet with several pages to select the header/footer to only appear on selected pages. Regards, Bosley. |
| 1435 | Hi Tom, In Excel 95, if you wanted to run a macro when a worksheet cell changed, you needed to assign that macro to the Application.OnAction property. You would typically do this in the Auto_Open procedure. In the macro you would use the Application.Caller property to determine the cell that was changed. Here's an example: Sub Auto_Open() Application.OnEntry = "CallMe" End Sub Sub CallMe() Dim rngChanged As Range Set rngChanged = Application.Caller MsgBox "Sheet - " & rngChanged.Parent.Name & _ vbLf & "Range - " & rngChanged.Address End Sub Note that the OnEntry macro will fire when any cell in any worksheet is changed. -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals / * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "Tom" <tom.w.degenhart@appliance.invensys.com wrote in message news:14fee01c222bb$80312b90$3aef2ecf@TKMSFTNGXA09... I have code that is written for Excel95 that does not reliably run in Excel97 for higher and have not found why. I have an application where I need to know if a change has been made on a worksheet so that the macros will know to update the other parts of the worksheet (cell formulas will not accomplish what I need to do). I know how to do this in Excel97 but not in Excel95. |
| 1457 | Thanks for the great tip, Mike. It had never occured to me that a cell comment can display a graphic image. John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "cbs" <cbs@vianet.net.au wrote in message news:01c22373$4edcce60$454ea5ca@laptop... Hello, Recently, a question was posed in this NG (I think) about inserting a graphic into an Excel cell comment. I have lost track of the original thread. This is how it can be done (apologies if this has already been covered): 1. Select a cell, right click, select Insert a Comment. 2. Hover your cursor over the cell border until it turns into a four-headed arrow. Right click, select Format Comment. 3. Select the Colors and Line tab. 4. Click the Fill Color drop down menu and select Fill Effects. 5. Select the Picture tab. 6. Click Select Picture. 7. Go to the directory that holds your graphics/pictures. Select the graphic/picture that you want displayed. Click OK, OK, OK. Resize the Comment box to fit the graphic. You can also insert text that overlays the graphic. Select the commented cell, right click, Edit Text, start typing. Have fun with changing the standard shape of a Comment box (View Menu / Toolbars /Drawing/ Draw/ Change AutoShape). The above procedure can be followed to insert a graphic into an Excel Text Box or Auto Shape. Copy an Excel chart to PowerPoint, resize if required, then Save As to a GIF or JPEG format. Insert the Chart into a Comment. Regards, Mike Perth, Australia |
| 1459 | Take a look at the downloads at my web site: -walk.com/ss/excel/files/general.htm Some of these may be appropriate. John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Marcia" <plumcreek@wayxcable.com wrote in message news:1549701c2237f$23a26820$19ef2ecf@tkmsftngxa01... Not sure if anyone can help but here goes. In 2 weeks I'm be teaching some classes in excel and powerpoint to kids between 10 and 14. Any websites that may have some projects geared toward this age group? Thanks in advance. Marcia |
| 1509 | Well, now you've seen a UserDefinedFunction in action and where to put the code. But if you want to read more about macros and UDFs, then you can read 's notes at: /dmcritchie/excel/getstarted.htm Lennart wrote: OK, after some google-ing I figured out how to use your tip, it works perfectly, thanks! you were the only one out of a LOT of ppl who did know how this works, thanks a lot! :) Lennart -----Original Message----- ok thanks for this tip, but I have to admit I don't know what a UDF is, and where i have to input it... So if you could tell me where I can find info regarding UDF or explain how it works, that would be very handy for me! Lennart -----Original Message----- You could use a UDF like this: Option Explicit Function PreviousSheet(myAddress As String) As Variant Application.Volatile PreviousSheet = Application.Caller.Parent.Previous.Range(myAddress).Value End Function . -- Dave Peterson ec35720@msn.com |
| 1511 | Save your workbook in HTML format. This will create a new directory that contains all of the drawn objects from your worksheet. They will be GIF files. John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "g.lier" <ghjl@mailcity.com wrote in message news:sN1V8.194712$3g4.16601510@zwoll1.home.nl... can anybody tell me if I can put a drawing in exel on my homepage? I can via frontpage or similar programs but how to do it in HTML |
| 1572 | UDF's are user defined functions. Excel supports built in functions (=sum(), =average(), =left()), and it also supports procedures/macros that can do a lot of repetitive stuff. (deleting rows that have column A = 17.5). These types of procedures are called macros. But it also supports user written functions. (That's what this is.) Hit alt-f11 to get to the VBE (visual basic editor/environment). Hit ctrl-R to see the Project explorer (usually along the left hand side) locate your project. It'll usually look like VBAProject (Yourfilenamehere.xls). There'll be a box with a +/- in it in front of it. This works just like windows explorer--expand and contract the things that are underneath it. Expand everything associated with it (just for curiosity's sake) You'll see: Microsoft Excel Objects sheet1 (sheet1) sheet2 (sheet2) ... sheetx (sheetx) ThisWorkbook Each one of these can contain code that act on just that thing. You want to make sure that any input that the user does in Column A of sheet1 is upper case. You can add event code that makes anything they type upper case. (It'll be almost invisible to the user!) You want to add some footers to the printed output? You can add it to the ThisWorkbook module. Chip Pearson has some nice notes at: /excel/events.htm that describe this kind of stuff in detail. ------- If you right click anywhere within that project, you'll get a popup with Insert... as an option. Click on that and then click on Module. (there are other things there, too.) Now paste that code in for the =previoussheet() stuff. Now hit alt-f11 to swap back to excel. One the second sheet (or the first if you want to see an error), type: =previoussheet("a1") I should have added to put something in A1 of the previoussheet! Pretty neat, huh? You use it just like a built-in formula, too. ---------- Why use UDFs at all. Well, if you've ever spent time in the worksheet.functions newgroup, there are a lot of questions about "how do I do this?" Some of the answers are longer than the Gettysburg address! I don't know how those people figure it out. I know that most of them are beyond me. But some could be rewritten into VBA and made understandable by me. They may be long and complex, but at least each part makes sense <vbg. Also, there are somethings that just can't be done (well, by me) using formulas. You want the color of the font in a cell? Chip Pearson has code that allows you to return a number that represents that color using a UDF. /excel/colors.htm Reprinted here without the express written consent of Chip or major league baseball: Function CellColorIndex(InRange As Range, Optional _ OfText As Boolean = False) As Integer ' ' This function returns the ColorIndex value of a the Interior ' (background) of a cell, or, if OfText is true, of the Font in the cell. ' Application.Volatile True If OfText = True Then CellColorIndex = InRange.Font.ColorIndex Else CellColorIndex = InRange.Interior.ColorIndex End If End Function Go to Chip's site and read all the caveats about this if you're going to use it. ========= And to read more about macros and code in general, you might want to head over to 's site: /dmcritchie/excel/getstarted.htm Ben wrote: Hi Dave, I actually have the same question as Lennart, but unlike Lennart, I don't know what a UDF is. Is it possible for me to simply cut and paste the code that you wrote below into my workbook, and if so, where exactly do I paste it. I'm new to this, if you can't tell :) Thanks in advance for your help. -Ben -----Original Message----- You could use a UDF like this: Option Explicit Function PreviousSheet(myAddress As String) As Variant Application.Volatile PreviousSheet = Application.Caller.Parent.Previous.Range(myAddress) .Value End Function There is no error checking. You may want to add some checks to see if the previous sheet actually exists, is visible, etc. You'd use it in your worksheet like: =PreviousSheet("a1") (I didn't want to use a range (like: =PreviousSheet(A1), because I wanted to be able to use this formula in cell A1. And putting the cell in the formula resulted in a circular reference.) Lennart wrote: Is it possible to link a cell on one worksheet to a cell on the previous one, without using the name of that sheet? so if I have 'sheet1' and 'sheet2', I don't want to use something for cell A5 on 'sheet2' like: ='sheet1'!A5 what I'm looking for is something like: =Previous_Sheet!A5 so that it doesn't matter what the name of that sheet is. BTW by Previous sheet, I mean the sheet to the left of the one I'm using at that moment. can anyone tell me how this is done? thanks in advance, Lennart -- Dave Peterson ec35720@msn.com . -- Dave Peterson ec35720@msn.com |
| 1704 | Hi Aileen, I hope you are opening fresh copies of the file as you experiment in recovering the file. What is the file extension and what do you see? Do you see spreadsheet gridlines and all data in column A, is your data lined up in columns with square bullets signifying where a line should break within a cell. Are you seeing this in Excel or in notepad or something else. If you see a common character between where each cell should be you can use Text to Columns if you are in Excel. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Aileen Cruz" <ailncrz@cox-internet.com wrote in message news:165ad01c2268f$4a426800$39ef2ecf@TKMSFTNGXA08... I had a worksheet with rows and columns that I created with genealogical information (dates, names, etc.) on Excel 97. Somehow these files were lost and I retrieved them back. But then I could not open them. They did not look the same. I lost all formats and the information is there but I cannot read it, it is all scrambled with symbols. Then I switched from Windows 98 to Windows XP and still I cannot open my file and make it look legible. Any suggestions on how I can accomplish this? I need help with this. Thanks for any help someone might give. |
| 1733 | I've tested the code using first VBA's object.CopyPicture method for a chart and second by opening a jpeg in Paint (Window's defauls pic viewer), pressing Alt+A & Alt+C and pasting it then with the code I've presented earlier. Both works. I don't know exactly what's wrong there with you computer. You could try the following: 1) copy any picture in the clipboard 2) turn Excel's macro Recorder on (Tools | macro | Record New macro...) 3) select Edit | Paste Special... 4) put thumbs up and wish that there's something listed that resembles a picture format... If so, select it and click Ok. If not, shut down the computer and go have fun elsewhere :-) 5) stop macro Recorder (click the little blue square) 6) go to Tools | macro | macros... and search for the code you just recorded. Probably Activesheet.PasteSpecial Format:=(something here)... is different from what I posted due to your computer's settings. It'd be fun to know, whether that worked out. Post back if you've got further questions; however, Europe's going to bed now and I won't be back until tomorrow morning. Perhaps there's someone else who can help you if needed. Take care, Jouni Finland "Mark A. Bystry" <mbystry@ziggity.com wrote in message news:#t8Dm#rJCHA.2688@tkmsftngp11... ahh! i must be retarded or something. i cannot get it to work. it errors out on the activesheet line. i've tried several things and still cannot get it to work. your code is exactly what i am looking for though... |
| 1766 | J, if you don't want to write the macro yourself, take a look at the Directory List at -ins.com/directory_lister.htm. I think it will do what you want, and more. Bob Flanagan macro Systems Productivity add-ins and downloadable books on VB macros for Excel I would like to import a Windows directory into an Excel spreadsheet, so I could arrange and print subdirectories and track progress on a set of over 330 files. Has anyone ever tried doing this? I've used SnagIt! to do screen captures, very successfully, but would like to have the data in columns, not just as pixels. |