Maximize and Protect Worksheet

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel Programming
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - autosort left to right autosort left to right leaving blanks where they are excel 2007 Tag: Maximize and Protect Worksheet Tag: 998051
  - 2
    - How to program a macro that moves my chart as I scroll through my I have a very large spreadsheet. In this spreadsheet, I have a series of indicators. I have a small macro that bases the chart values off of my current selection. What I would like to do is program my chart to move as I scroll. So as I scroll down the spreadsheet, the chart moves with my current view. Does anyone know how to do this? Thanks Tag: Maximize and Protect Worksheet Tag: 998050
  - 3
    - Change all label controltiptexts in userform Hi guys, This is driving me insane!!! I'm trying to have the text of all of the controltiptext properties on a userform to be the same as the caption property of the label (there's loads of labels). It seemed like it should have been easy, but after much trial and error I've managed to come up with the following (which I don't quite follow) and feels so close, but I'm stumped as to get the caption in there: Sub Change_Labels_UserForm() Dim oVBProj, oVBComp As Object Dim ctl As Control Set oVBProj = ThisWorkbook.VBProject On Error Resume Next For Each oVBComp In oVBProj.VBComponents If oVBComp.Type = 3 Then For Each ctl In oVBComp.Designer.Controls If Left(ctl.Name, 5) = "Label" and ctl.ControlTipText = "" Then ctl.ControlTipText = 'caption name Next End If Next End Sub Any ideas? Cheers, JF Tag: Maximize and Protect Worksheet Tag: 998045
  - 4
    - Insert Row After Series I think this is possible but I'm not certain of the way the code would look. I have an extract that comes to me in Excel format and I have a Macro that runs through the Excel file reformatting it. The extract will have several hundred rows. Column I has a State description (I believe there are a couple dozen descriptions) and what I need to do is add a blank row after each state. Example ... Pending Pending Pending In Process In Process Active Active Active would become Pending Pending Pending In Process In Process Active Active Active I guess what I would like would be to add code to the existing Macro that basically says everytime the value in column I changes add a row. Any ideas. I hope this makes sense. Tag: Maximize and Protect Worksheet Tag: 998041
  - 5
    - Check for NumberFormat = DateFormat Hi all, is there a simple way to check if a Range.NumberFormat returns a Date format ? To be sure I am not misunderstood: the Cell-Values might be empty but the cells are formatted to a date format. by default it might be dd/mm/yy or similar but could also return "[$-409]d/m/yy h:mm AM/PM;@" or sth. what I am searching for is a simple way to check if the range.numberformat fits to any possible dateformat. best case would be "If Range.NumberFormat = IsDateFormat then..." but I cannot seem to find something like that. Is there an easy solution for that ? thanks for any kind of hints. Joe. Tag: Maximize and Protect Worksheet Tag: 998039
  - 6
    - Input the filename into Excel Sheet Dear Experts, I am asked to input the fillename as text into the Excel sheet. These files are PDF formated. Like 19347-Autoline-6001004.pdf,it is thousands files. Is anybody help me to provide me a solution to "read" these file names automatically into my worksheets like: A B C 1 19347 Autoline 6001004 2. 19348 autoline 5001045 .... Eager to know the solution! Li Jianyong Tag: Maximize and Protect Worksheet Tag: 998035
  - 7
    - find Is there any way to program a workbook so that I am able to click on any individual cell in a workbook, which contains multiple sheets, that would then serach the entire workbook and automatically locate/find any other rows in the entire workbook that contains information identical to that cell that I originally selected? Tag: Maximize and Protect Worksheet Tag: 998032
  - 8
    - Referencing Checkbox Name I was wondering how I could reference the name of a checkbox and assign it to the value of a cell. To give an example of what I am looking for: I have 5 checkboxes on a sheet and the names of these checkboxes will eventually change. I would like call A1 to return the name of check box 1, cell A2 to return the name of check box 2, and so on. Any help would be greatly appreciated. Thanks! Chad Tag: Maximize and Protect Worksheet Tag: 998029
  - 9
    - #NAME? in cell when opening a Workbook I have some Excel VBA code that I saved as a .XLA Add-In file. I then have this XLA set as an Add-In inside of Excel. If I enter my UDF into a cell, it works correctly and returns the values that I expect. Every time I re-open a Workbook that contains calls to my UDF in the Add-In, all cells that call the UDF have " #NAME? " in the cell. Excel goes through and recalculates these cells and all is correct and #NAME? is replaced with the output of the UDF for the cell. Why does Excel have to resolve the reference to this Add-In each time it opens? Tag: Maximize and Protect Worksheet Tag: 998025
  - 10
    - importing another file Hello every one, I have to run a report every monday to check on project status. the report (file1)has two blank rows in between each project. I have a marco that deletes blank rows. I also have a file that has my calculations in columns ac thru columns ao. In the past i would just copy all the columns in File 1 in to File 2. is there a way I could automate this? Thanks Tag: Maximize and Protect Worksheet Tag: 998019
  - 11
    - Set Pivot-Table Visible Items With ActiveSheet.PivotTables("AssetPT").PivotFields("Cost ctr") .PivotItems("12476").Visible = True .PivotItems(DataArray(X, 1)).Visible = True end with Why would the line with "12476" work and the line immediately below, where DataARray(x,1) equals "12476" give me this error: Error (1004) Unable to get teh pivotItems property of the pivotfield class????? Tag: Maximize and Protect Worksheet Tag: 998015
  - 12
    - Create range names with names in column heading Hi, Need some help. I have a sheet that has column headings along row 1. Would like to create range names with each column heading as the name. The number of rows will increase. Any suggestions? Tag: Maximize and Protect Worksheet Tag: 998009
  - 13
    - use only numbers and '/' I have a textbox in a userform that I want to only accept numbers and '/' to enter dates. How do I do that? Thanks Tag: Maximize and Protect Worksheet Tag: 998008
  - 14
    - Find entries in one column in another Hi, I have been trying to figure out a solution to this problem for days now and it is really bugging me... I have two columns with descriptions in. One column is part of a 'catalogue list' and one column is part of a 'things that have been ordered list'. For each item that is ordered the buyer needs to decide whether the item which they are ordering is on the catalogue or not. This is however not always done! This is where I come in. It is my job to search the list of items that have been ordered and using the description of the item (sometimes 10 words long) decide whether it is already on the cataolgue. This can be very time consuming and I was wondering if there was a VBA code which could speed up my manual process by comparing the two descriptions columns and matching up similar results. An example could look like... 'Things that have been ordered list' description **msc Breaded Fish Fillet Squares 70g 1x36pk 'Catalogue list' description Fish fillets 1x36pk, 70g Any help would be much appreciated...I am losing sleep over this!! Tag: Maximize and Protect Worksheet Tag: 998002
  - 15
    - Andy Pope's button image editor; pasting icons to sheet then toolbar- why does image quality degrade? I used Andy Pope's add-in to save my custom button icons as .bmp files. To use them in my add-in, instead of trying to load them from the .bmp files directly, I used insert/image to put each .bmp on a worksheet in my add-in. I then use code to paste them to my new toolbar. The problem is that I seem to have quality problems in (at least) two places; when I insert a .bmp and enlarge it for analysis, the picture is similar to the original, but blurry (I'd expect it to just show a large 16x16 block of colors). Then when I paste it to the toolbar- then copy that image back into Andy Pope's editor, the end result shows that the image had further degraded. I'd like a clean (and transparent) .bmp on my final toolbar, but many google searches have turned up nothing. Is there a preferred approach to ensure that the button image quality remains stable/clean all the way through to the final toolbar? Thanks! Keith Tag: Maximize and Protect Worksheet Tag: 997989
  - 16
    - Formula bar is hidden, how do I unhide it? Somehow I have hidden the forumla bar and cannot make it visible again. If you know how to "unhide" it, please respond. Tag: Maximize and Protect Worksheet Tag: 997988
  - 17
    - Textbox value I have a textbox on a userform to input dates. How do I format the textbox to only accept dates in the form of mm/dd/yy? Thanks Tag: Maximize and Protect Worksheet Tag: 997986
  - 18
    - Median of all combinations Hi, I have 5 numbers (can be with decimals) in column A (A1:A5). For these numbers, I want all combinations as well as the median of these combinations. I'm pretty far already. However, the median seems to be calculated wrong. Who can help me? Thanks, Andreas Sub test() ListCombos Range("A1:A5"), 3, "C:\Q1_3er.csv" End Sub Sub ListCombos(r As Range, ByVal m As Long, sFile As String) ' lists the combinations of r choose m to file sFile ' r is a single-column or single-row range Dim ai() As Long Dim i As Long Dim n As Long Dim sOut As String Dim sOutMedian As String Dim iFF As Integer If r Is Nothing Then Exit Sub If r.Rows.Count <> 1 And r.Columns.Count <> 1 Then Exit Sub n = r.Count If m < 1 Then Exit Sub If m > n Then m = n iFF = FreeFile Open sFile For Output As #iFF ReDim ai(1 To m) ai(1) = 0 For i = 2 To m ai(i) = i Next i Do For i = 1 To m - 1 If ai(i) + 1 < ai(i + 1) Then ai(i) = ai(i) + 1 Exit For Else ai(i) = i End If Next i If i = m Then If ai(m) < n Then ai(m) = ai(m) + 1 Else Exit Do End If End If ' catenate and write to file sOut = vbNullString Call Sort(ai) sOutMedian = median(ai) For i = 1 To m sOut = sOut & r(ai(i)).Text & "," Next i Write #iFF, Left(sOut, Len(sOut) - 1), sOutMedian Loop Close #iFF End Sub Sub Sort(Arr() As Long) Dim Temp As Double Dim i As Long Dim j As Long For j = 2 To UBound(Arr) Temp = Arr(j) For i = j - 1 To 1 Step -1 If (Arr(i) <= Temp) Then GoTo 10 Arr(i + 1) = Arr(i) Next i i = 0 10 Arr(i + 1) = Temp Next j End Sub Function median(Arr() As Long) Call Sort(Arr) If UBound(Arr) Mod 2 = 1 Then median = Arr(Int(UBound(Arr) / 2) + 1) Else median = (Arr(UBound(Arr) / 2) + Arr(Int(UBound(Arr) / 2) + 1)) / 2 End If End Function Tag: Maximize and Protect Worksheet Tag: 997985
  - 19
    - compare oldvalue and new value of a cell Hi All, how to write a code to compare the value of a cell before it's being updated by user.If the new value is different with the new value,I want the cell background colour become different.But since I have many sheet in my workbook,I want the code become public and I try to keep the sheet clean from any vba code. Thank's Shiro Tag: Maximize and Protect Worksheet Tag: 997979
  - 20
    - Get reference to a file opened in another instance of XL Using Office 2003; Generic: I need to loop thru all open MS-Excel files in another instance of XL with the object in mind of getting a reference to a particular file opened as a template. I can positively identify the template file by the contents of certain cells in the template. Specific: The user will be opening an Oracle Financials, Fixed Asset (CPR) ADI spreadsheet which always opens in a new instance of XL. I need to get a reference to this spreadsheet and populate it with data from an array. I can do all of this just fine, but it's the reference to the other instance that is a challenge. Any help would be appreciated. Tag: Maximize and Protect Worksheet Tag: 997977
  - 21
    - Slow procedure I have modified a procedure I downloaded from Debra Dalgleish's site and show the detail as below. Basically it looks at a range of values then creates a named worksheet for each of these values if that worksheet does not already exist. If it does exist it just clears some ranges and copies in some filtered data. I would be grateful if someone could have a quick look through to see if I have put in anything in such a way that it would really slow the operation of the procedure. Don't get me wrong, the procedure does exactly what it is ecpected to do , it just seems to take a bit of time and I just wonder if there is anything slowing it. I am sorry I have notes above each operation as I am not the sharpest pencil in the box when it come to programming and I need to keep track of what I am trying to do. Don't spend a lot of time on it, as I say it works and is liveable with. Sub ExtractFields() Dim ws1 As Worksheet Dim wsNew As Worksheet Dim rng As Range Dim r As Integer Dim c As Range Set ws1 = Sheets("FieldMaster") Set rng = Range("DatabaseList") Application.ScreenUpdating = False r = Sheets("Entries").Cells(Rows.Count, "A").End(xlUp).row For Each c In Sheets("Entries").Range("A12:A" & r) ' check if sheet exists If WksExists(c.Value) Then 'Clear existing sheet areas if sheet already exists Sheets(c.Value).Range("B12:E15").ClearContents Sheets(c.Value).Range("B23:E28").ClearContents Sheets(c.Value).Range("J12:N15").ClearContents Sheets(c.Value).Range("J23:N28").ClearContents ' run advanced filter to get organic applications rng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets(c.Value).Range("Q1:Q2"), _ CopyToRange:=Sheets(c.Value).Range("C11:E11"), _ Unique:=False Else 'If sheet does not exist add it Set wsNew = Sheets.Add ' enter after last sheet wsNew.Move After:=Worksheets(Worksheets.Count) ' name the sheet wsNew.Name = c.Value ' copy template to new sheet Sheets("FieldBase").Cells.Copy Destination:=wsNew.Range("A1").Cells ' Enter field name into FieldMaster for soils copy Sheets("FieldMaster").Range("AA2").Value = wsNew.Name ' Copy base soil to Soils sheet Range("SoilBase").Copy Sheets("Soils").Cells(Rows.Count, 1).End(xlUp)(2) ' enter sheet name in reference cell wsNew.Range("B2").Value = wsNew.Name ' put field name into filter criteria to allow for alphanumerics wsNew.Range("Q2").Formula = "=""=" & wsNew.Range("B2").Value & """" ' run advanced filter to get organic applications rng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=wsNew.Range("Q1:Q2"), _ CopyToRange:=wsNew.Range("C11:E11"), _ Unique:=False End If Next ws1.Select End Sub Function WksExists(wksName As String) As Boolean On Error Resume Next WksExists = CBool(Len(Worksheets(wksName).Name) > 0) End Function Tag: Maximize and Protect Worksheet Tag: 997974
  - 22
    - OldCell colour With below code,I want turn the OldCell colour to another colour if the cell's value was updated by user, if it's not updated the colour is xlColorIndexNone,how to modify the code? Thank's in advance. Option Explicit Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) Static OldCell As Range If Not OldCell Is Nothing Then OldCell.Interior.ColorIndex = xlColorIndexNone End If Target.Interior.ColorIndex = 6 Set OldCell = Target End Sub Tag: Maximize and Protect Worksheet Tag: 997970
  - 23
    - Problem setting each parameter in an Array = Nothing I want to reset each parameter in an Array = 0 or " ". Why can I not make myDes = Nothing. I am getting an error below. If I set myDes = 0 everything works fine, but it will not work when myDes = Nothing Sub ResetVariables() Dim myBOM_Des As Variant Dim myDes As Variant ' row location of part on "Parts List" myBOM_Des = Array(AlumFace_Row, Texture_Row, Primer_Row, Paint1_Row, Paint2_Row, Paint3_Row, Paint4_Row, Vinyl1_Row, _ Vinyl2_Row, Vinyl3_Row, Vinyl4_Row, ClearVinyl_Row, Ink_Row, Plastic_Row) ' sets row locations to Nothing For Each myDes In myBOM_Des ERROR => myDes = Nothing Next myDes. End Sub -- Cheers, Ryan Tag: Maximize and Protect Worksheet Tag: 997968
  - 24
    - Mutually Exclusive Option Boxes I need to set up 3 option boxes that are mutually exclusive. When I click on one option box the other two should be unchecked etc.etc. How do I go about doing this - I had a look through the forum but I can't seem to find a solution. Thanks Tag: Maximize and Protect Worksheet Tag: 997963
  - 25
    - Why doesn't this work? Hi All, The following code snippet is invoked from my personal.xls workbook. What l am attempting to do is select any excel file in any directory and then create a .xls file for each worksheet in the selected workbook into a 'TEMP' directrory The code is failing at the 'sh.Copy' line. Can anybody tell me what is wrong please? FileNameOnly & DirOnly are simply functions that l have used many times to extract the relevant information from the string returned by GetOpenFilename Additionally l need to add some code to check if the 'TEMP' directory has already exists, any ideas, example code would be gratefully appreciated. Sub CreateXLFiles() Dim afile As String 'Source workbook to be rebuilt Dim adir As String 'Directory of sheet files Dim sh As Worksheet afile = Application.GetOpenFilename(, , "Select the source file", , False) Application.ScreenUpdating = False adir = (DirOnly(afile) & "\" & FileNameOnly(afile) & "-TEMP") MkDir adir Workbooks.Open afile, UpdateLinks:=False For Each sh In Workbooks(FileNameOnly(afile)).Worksheets sh.Copy <<<<<<<<<<<<<<<ERROR HERE ActiveWorkbook.SaveAs adir & ActiveSheet.Name & ".xls" ActiveWorkbook.Close SaveChanges:=False Next Workbooks(FileNameOnly(afile)).Close SaveChanges:=False End Sub Regards Michael Tag: Maximize and Protect Worksheet Tag: 997950
- Next
  - 1
    - Printing style I write the C# module that open the workbook and send it to printer. Is there any posibility to detect that the worksheet was designed to print in Landscape or Portrait.? Tag: Maximize and Protect Worksheet Tag: 997940
  - 2
    - PLEASE HELP WITH AN SPECIAL AUTOFILL A good day ! I have in a sheet in entyre column BD , 1423 cells with array formulas ; the cells with formulas are from range BD91 to BD65536 , in every 46 row : BD91 , BD137 , BD183 , BD229 , BD275 , BD321, BD367 , BD413 , BD459 BD505, BD551 , BD597 , BD643, BD689 , BD735 , BD781 , BD827 , BD873 BD919 , BD965 , BD1011 .................etc...............BD65319 , BD65365 ,BD65411 BD65457 , BD65503 With usual macro I have 1423 lines in macro to do this autofill : Range("BD91").AutoFill Destination:=Range("BD91:BD136"), Type:=xlFillDefault Range("BD137").AutoFill Destination:=Range("BD137:BD182"), Type:=xlFillDefault Range("BD183").AutoFill Destination:=Range("BD183:BD228"), Type:=xlFillDefault Range("BD229").AutoFill Destination:=Range("BD229:BD274"), Type:=xlFillDefault Range("BD275").AutoFill Destination:=Range("BD275:BD320"), Type:=xlFillDefault Range("BD321").AutoFill Destination:=Range("BD321:BD366"), Type:=xlFillDefault Range("BD367").AutoFill Destination:=Range("BD367:BD412"), Type:=xlFillDefault Range("BD413").AutoFill Destination:=Range("BD413:BD458"), Type:=xlFillDefault ........ .........................................etc.............................................................................. Range("BD65411").AutoFill Destination:=Range("BD65411:BD65456"), Type:=xlFillDefault Range("BD65457").AutoFill Destination:=Range("BD65457:BD65502"), Type:=xlFillDefault Range("BD65503").AutoFill Destination:=Range("BD65503:BD65536"), Type:=xlFillDefault My goal is to get *THE BEST SPEED* for this autofill in this entyre column BD ; Please very much to provide me this kind of macro , which shall give me the MAXIMUM SPEED to autofilling the 1423 cells in entyre column ;(I use excel 2003 ). Thanks very much for your time Tag: Maximize and Protect Worksheet Tag: 997932
  - 3
    - can same data in different cells be replaced by any formula Tag: Maximize and Protect Worksheet Tag: 997927
  - 4
    - Copy From HTML prgramme figures I have copied a table (values) from a HTML program but when it come to add only it is adding half of it the rest when trying to add that is not adding, i have seperatly try to add those are not adding it show #VALUE! -- VJC Tag: Maximize and Protect Worksheet Tag: 997926
  - 5
    - Detecting the change of a certain cell Dear All: Is it possible that someone can kindly tell me how to detect the change of a certain cell? environment is VSTO + VS 2008 + EXCEL 2007. the Globals.sheet1.Calculate += is successful but the Globals.sheet1.Change doesn't work. Could anyone provide a sample? FYI the code is written within a window Form. Best Regards, Fan Tag: Maximize and Protect Worksheet Tag: 997923
  - 6
    - If one range changes, update the other Hi, With some help from user OssieMac, I was able to get the code to this point, but I think I may need some help to get to the next step. For the sake of this test, I have two named ranges on one worksheet: Range("Sh1BWTest") and Range("Sh2BWTest") When I insert a row into Range("Sh1BWTest"), I need the row range in Range("Sh2BWTest") to also adjust and update.. The code is almost there, I just can not get that last part. Any help would be appreciated. Thank you ML Private Sub Worksheet_Change(ByVal Target As Range) '*** This part works well Dim isect As Object Set isect = Intersect(Target, Range("Sh1BWTest")) If Not isect Is Nothing Then Application.EnableEvents = False 'Clear data from Sh2bWTest in case the Sh1BWTest range is smaller Range("Sh2BWTest").ClearContents MsgBox "Cleared" 'Confirm the clear was sucessful Range("Sh2BWTest").Value = Range("Sh1BWTest").Value 'Select and rename range Range("Sh2BWTest").Select ActiveWorkbook.Names.Add Name:="Sh2BWTest", _ RefersToR1C1:=Selection End If Application.EnableEvents = True 'Range("A1").Activate End Sub Tag: Maximize and Protect Worksheet Tag: 997920
  - 7
    - Excel crashes Hi, I am using Excel 2007 on Windows XP. On the sheet I have a button (form button) that executes the macro below. The macro runs fine, the new workbook (myTemplate1.xls) opens up OK. The problem occurs when I close the workbook (myTemplate1.xls) Excel crashes. What am I doing wrong? By the way I have been using myTemplate (opening it manually) for years without a problem. It is only when I open it using the macro below that I have this problem. Thanks for any help Fred Sub Button432_Click() Dim PartNo As String PartNo = Trim(Sheet1.Range("C8").Value) If PartNo <> "" Then Workbooks.Add Template:="\\myServer\myDrive\myTemplate.xlt" End If End Sub Tag: Maximize and Protect Worksheet Tag: 997919
  - 8
    - Email with Notes I am trying to setup to email a spreadsheet thru notes, I have everything worked out EXCEPT for the send to I have tried the following emailsendto=emailadd emailadd is a range in a spreadsheet (email b2) the format is "username@company.com,","username2@company.com,","username3@company.com" but it is only sending to the first name Now I could set the range to B2:b11, but not sure of the syntax any help would be appreciated Tag: Maximize and Protect Worksheet Tag: 997918
  - 9
    - Compile Error: Procedure too large I am using the: Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) routine to run a series of macro's, but not i get the 'Too large error'. I was thinking if i break up the code and place it into various Modules, and using a 'Call" procedure to run the macro with this procedure instead. However when i tried this it does not run. Can i remove the 'Private' and use a 'Public' to get it to work? Tag: Maximize and Protect Worksheet Tag: 997917
  - 10
    - If, then, for a long time I've asked several questions lately, and I'm sorry about that. I have a series of formulas that I could probably put into a very large macro, but I'm sure there's a way in VBA, but I can't figure it out. I have a very large spreadsheet to work with, but the formulas on most of them are either the same of similar. In one section I have a table that is renewed monthly. The table looks like this: Term Value 1 13905 2 12920 3 12015 4 11455 5 11095 6 11060 7 11200 8 11010 9 10580 10 10160 11 9705 12 9210 13 8665 14 7985 15 7375 16 6990 17 6730 18 6690 19 6745 20 6585 21 6280 22 5980 23 5660 24 5320 In addition there is a row of numbers that relate to this table that also changes monthly (it's used to keep track of the market value of a vehicle). The rows of data look like this: Aug-07 $18,848 Sep-07 $18,320 Oct-07 $17,792 Nov-07 $17,264 Dec-07 $16,736 1 Jan-08 $16,208 2 Feb-08 $15,680 3 Mar-08 $15,152 4 Apr-08 $14,623 5 May-08 $14,095 6 Jun-08 $13,567 7 Jul-08 $13,039 8 Aug-08 $13,905 9 Sep-08 $12,920 10 Oct-08 $12,015 11 Nov-08 $11,455 12 Dec-08 $11,095 13 Jan-09 $11,060 14 Feb-09 $11,200 15 Mar-09 $11,010 16 Apr-09 $10,580 17 May-09 $10,160 18 Jun-09 $9,705 19 Jul-09 $9,210 20 Aug-09 $8,665 21 Sep-09 $7,985 22 Oct-09 $7,375 23 Nov-09 $6,990 24 Dec-09 $6,730 25 Jan-10 $6,690 26 Feb-10 $6,745 27 Mar-10 $6,585 28 Apr-10 $6,280 29 May-10 $5,980 30 Jun-10 $5,660 31 Jul-10 $5,320 The August 07 amount is when it was put in service. We didn't really start keeping track until a few months later, which is next to the number 1. There is another cell that I put a date for the end of the month in, and I have several other cells that use that date in a vlookup table using the dates you see above (I've had to turn those formulas into VBA code, but that's another issue). Here's the deal. When it's July 2008 a formula is in place to move make various calculations, and the August 2008 cell should equal the first number in the first table above (in this case 13,905). In August 2008 I need the formula to carry down from July, and for September to then equal the first number in the table, October to equal the second number, etc. Is this something I can set up in VBA to do? I appreciate the help. -- Message posted via http://www.officekb.com Tag: Maximize and Protect Worksheet Tag: 997907
  - 11
    - Sheet sub to fire after DV selection is changed I'm using the sheet sub below to auto-scroll horizontally In C3 is a formula: =ADDRESS(5,MATCH(C2,G5:HJ5,0)+6,4) In C2 is a DV which selection feeds into C3 for the resulting cell address Despite calculations set to auto mode, I find that after I select the DV, I still have to click inside C3, then press ENTER before the auto-scroll sub will work. How could the sub be amended to work straight off after the DV selection is changed? Thanks for insights ---------- Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Handler If Target.Address = Range("C3").Address Then Application.Goto Range(Target.Value), True End If Exit Sub Handler: Dim msg As String Select Case Err.Number Case 1004 msg = "Probably invalid address" Case Else msg = "Unknown error'" End Select MsgBox msg End Sub -------- Tag: Maximize and Protect Worksheet Tag: 997889
  - 12
    - Enquiries about calculation formula Is there a formula for the the following calculation (a to d)? I have tried the following way but not successful. =if(a2>0.01,"1","0",if(a2>100.01,"2","0",if(a2>200.01,"3","0".....continued to d) a) If the amount is greater than 0.01, than the rate is $1.00 b) If the amount is greater than 100.01, than the rate is $2.00 c) If the amount is greater than 200.01, than the rate is $3.00 d) If the amount is greater than 300.01, than the rate is $4.50 e) If the amount is greater than 400.01, than the rate is $5.50 Thank you very much for your response. Tag: Maximize and Protect Worksheet Tag: 997881
  - 13
    - Adding Label Numerical Values correctly I am trying to ADD the values of 3 label Caption IF they are a Numerical Value. I have used the LONG approach below, but it only works on the provision that the user starts at the label63 then to the Label74 and thirdly to the Label87. But if the user starts at label74 or Label87, the below code does not add the values and place the result into Textbox64. How can i better code this to allow for ALL not matter what Labels are numerical, and total the values in TextBox64, without having to add every possible combinatiopn of the current code i have ? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If IsNumeric(Label63.Caption) = True And IsNumeric(Label74.Caption) = False And IsNumeric(Label87.Caption) = False Then TextBox64.Value = Val(Label63.Caption) If IsNumeric(Label63.Caption) = True And IsNumeric(Label74.Caption) = True And IsNumeric(Label87.Caption) = False Then TextBox64.Value = Val((Label63.Caption)) + Val((Label74.Caption)) If IsNumeric(Label63.Caption) = True And IsNumeric(Label74.Caption) = True And IsNumeric(Label87.Caption) = True Then TextBox64.Value = Val((Label63.Caption)) + Val((Label74.Caption)) + Val((Label87.Caption)) TextBox64 = Format(TextBox64, "0.00") ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ CtM. Tag: Maximize and Protect Worksheet Tag: 997872
  - 14
    - Subscript out of range error Hello, I have been trying to fix this error but I have no clue why it's not working. Any help would be greatly appreciated! I am getting "Subscript out of range error" when I execute the coding given below, only when I HAVE AN EXCEL INSTANCE OPEN. If I close all the excel instance and run the code again, it works just fine. Here is the coding: ------------------------------ Dim xlsApp As Object 'I included this ExcelIsOpen function to see if an excel instance is already open 'and if so, use that...but that doesn't seem to fix the issue If ExcelIsOpen Then Set xlsApp = GetObject(, "Excel.Application") xlsApp.Visible = True Else Set xlsApp = CreateObject("Excel.Application") xlsApp.Visible = True End If xlsApp.Workbooks.Open Filename:="C:\USERNAME\Import_UnmappedMoves.xls", Password:="password", WriteResPassword:="password" 'am getting the error when the next line executes Workbooks("Import_unmappedmoves.xls").worksheets("Moves").Activate ------------------------ Do you know where I am going wrong? Thanks in advance for your help! Tag: Maximize and Protect Worksheet Tag: 997868
  - 15
    - Frame without background color (invisible)? I'm not sure if this is even possible but it is -- awsome! =) Is anyone aware of how to create a frame without any background color? The reason I ask is I'm trying to lay a frame over a photo / flash animation object and I can't seem to get it to work correctly. I know that you can do this effect with textboxes, pictures, etc... but not sure about frames... You guys rock! Thanks for helping us out all these times. Tag: Maximize and Protect Worksheet Tag: 997867
  - 16
    - I hope to get many responses on this. I am currently in pain over the Sarbanes-Oxley rules and what they are requiring. I am not sure if I am so upset over the requirements or what appears to be manangements and Sarbanes auditors lack of knowledge of how to use excel. The Sarbanes people want to throw more people at every issue and my feeling is the real issue is their lack of knowledge of excel. Here is a general idea of my proposal. I feel it would probably take care of half of what they call deficiencies. My question is how secure it this: I have to send a pre formatted excel file "File01" to 10 subsidiaries so they can input thier month end financial data. The file has 12 sheets. I passworded each sheet and only made unprotected cells where I want input. I also passworded the workbook. I also passworded the file. Now to open the file I also send each user a second file "File02", it's sole purpose is to open the first file with code which the VBA is also passworded. What I am telling the Sarbanes-Oxley people is the file is secure and I will know if I get the correct File01 back becuase the first process when I open the file is to see if the file has a password. If they do not send me the correct file then either the file will not open because it has the wrong password or they used another file with no password which I will catch when I ask if the file has a password. I want to use this method because when I get the 10 files back I can do basically anything I want with the data using macros to automate the combing of data anyway that I want. Question: How secure is this method in that the user will have to use File02 as the opening file to force use of File01 that has file, workbook and worksheet protection. How easy would it be for someone to get past all that security? Note: I am thinking if they get past the file password it will not do them any good because they will not be able to get past the Workbook and Worksheet security. I will also be testing each files passwords for Workbook and Worksheet. Thank you for your comments. Steven Tag: Maximize and Protect Worksheet Tag: 997859
  - 17
    - Comparing worksheets with non-identical rows of information Hi, I am a newcomer when it comes to functions and programming in Excel which makes this task all the more difficult. I have a worksheet that is updated continuously with new/added information and information that is deleted. This is an inventory worksheet with retail items listed in rows and aspects of its description located in adjacent cells. Basically, I cannot depend on an item to be located in the same row each time the worksheet is updated since things are added and deleted. What I would like to do is have the original (non-updated) worksheet be compared to the updated worksheet by: 1. Using the info starting from C3 down (the UPC number for the product) in the original worksheet and search for this number in the same column (C) in the updated worksheet. 2. Highlight any differences in columns G, H, I, for the row in question where the identical UPC numbers are found in both worksheets. (Highlighted in the original worksheet) This is a very involved task I would assume, so if an answer cannot be provided, I would certainly appreciate any suggestions regarding reference material. Thank you for your time Glenn Yamada Tag: Maximize and Protect Worksheet Tag: 997856
  - 18
    - SQL ORDER BY issue I am creating a SQL SELECT statement in VBA that extracts data from a MS-SQL DB. All was going well, until I decide to put and "ORDER BY" statement at the end of the SQL string I was creating. Here is my code snippet: strTablein = "dbo.hpsc_application" strFieldin = "Planned_Obs_Date, " strFieldin = strFieldin & "Actual_Obs_Date, " strFieldin = strFieldin & "PlannedRetFYQtr, " strFieldin = strFieldin & "IT_Owner_L2, " strFieldin = strFieldin & "IT_Owner_L3, " strFieldin = strFieldin & "IT_Owner_L4, " strFieldin = strFieldin & "date_of_last_record_update" strWhere = "IT_Owner_L2 = 'my owner name field (data)'" strSQL = "SELECT " & strFieldin & " FROM " & strTablein & " WHERE " & _ strWhere & " ORDER BY Actual_Obs_Date DESCENDING" Debug.Print strSQL Set rs = con.Execute(strSQL, , 1) I am getting a sql error on the execute, stating there is an error "near DESCENDING". I cannot see what that would be. The output of the debug of strSQL is: SELECT Planned_Obs_Date, Actual_Obs_Date, PlannedRetFYQtr, IT_Owner_L2, IT_Owner_L3, IT_Owner_L4, date_of_last_record_update FROM dbo.hpsc_application WHERE IT_Owner_L2 = 'my owner name field (data)' ORDER BY Actual_Obs_Date DESCENDING In the WHERE clause, I am wondering if it is something with the single quote, or some such. Note, if I remove the "...ORDER BY ....." all the way to the end of the statement, it works completely fine (just not in the order I want). Was hoping another set of eyes might see an obvious error. Thanks! PatK Tag: Maximize and Protect Worksheet Tag: 997855
  - 19
    - Moving Columns Macro Hi, there. I am attempting to set a macro to move columns in one spreadsheet to match the column heading order in a different spreadsheet. If the secondary spreadsheet always came in the same order, I could just move all of the columns once and record the macro. However, the secondary spreadsheet column headings are in no standard order once it is received, but must be in the same order as the primary spreadsheet when finished. Is there a way I can set up a macro to search for the column heading and then move it to the appropriate place to be in the same order as the primary spreadsheet? Tag: Maximize and Protect Worksheet Tag: 997849
  - 20
    - Data sorting to a new sheet I'm not even sure this can be done. What am trying to do is sort data from Sheet 1 and place the sorted data on sheet 2 & 3. Here is what the data would look like: 1 BLOCKNAME ADDRESS1 STROBE_CIRCUIT_INFO SPEAKER_CIRCUIT_INFO 2 INIT_SD 5.5.001 3 NAC_110 V2-01 A4-03 4 INIT_SD 5.5.021 5 INIT_SD 5.6.001 6 NAC_110 V3-01 A1-23 7 NAC_110 V1-01 A1-01 What I need is all Rows with something in Address1 column goes to sheet1 (in order) and all Rows with something in column Strobe_circuit_info goes to sheet 3(in order). I also need them to be grouped on the other sheet (2 & 3). ex. 3 NAC_110 V1-01 A1-01 30 NAC_110 V2-01 A4-03 57 NAC_110 V3-01 A1-23 so if the if there is a device V1-02 it would go in row 4 and so on, same for sheet 2 Is this possible or am i really reaching Thanks for your input Tag: Maximize and Protect Worksheet Tag: 997847
  - 21
    - Macro not ending? I've been having a lot of problems and it seems to be that the macro works perfect the first time; however, when I run it again, the user forms are shifted around, the combobox value can't be set to show blank, etc. It also does not do the initialize code in the form after it has been run once. It does all this after the first time unless I step through the macro and stop it. Then it seems to reset everything. Can you tell what I am doing wrong? Please help! Thanks, Pam Tag: Maximize and Protect Worksheet Tag: 997846
  - 22
    - delete alternate column How can I delete every other column (B, D, F.....) in Excel? Tag: Maximize and Protect Worksheet Tag: 997843
  - 23
    - Control button display issue I am adding buttons with the following code. They are active and function but do not display. If I set them manually to "Text Only (Always)" via the customize menu screen, they display fine. I'm using XL2003. I am at my wit's end trying to do this programmatically. What am I missing? Thanks. .Controls.Add Type:=msoControlButton, ID:=2949, Before:=2 With .Controls(1) .Visible = True .Caption = "ReadFile" .OnAction = "thisworkbook.readfile" End With Tag: Maximize and Protect Worksheet Tag: 997840
  - 24
    - AutoIncrementing a Column like a database in Excel 2007 Hi, We're going to use an SQLite database but we'd like to do all our data entry in excel. Then we'd like to save the sheets as .csv files and import the data into an SQLite database. For us to do this successfully we need to be able to AutoIncrement an id field in Excel like a database would. Does anyone know the best way to do this? Thanks, Keith Tag: Maximize and Protect Worksheet Tag: 997835
  - 25
    - radio button with VB code Hello all, Iâ??m needing the following: 4 radio buttons, â??Defaultâ??, â??GTDâ??, â??ECOâ??, â??BOTHâ??. When running a VB code, I need to code to look at what button has been selected and call another VB code or in the case of â??Defaultâ?? it keeps running the code with out interruption. So, VB code is running, then it looks at the radio buttons, see that â??GTDâ?? is select and then calls another VB code, then it continues on with the rest of the code when the other code is finished. Thanks Tag: Maximize and Protect Worksheet Tag: 997827

I am trying to lock two out of three pages or a workbook plus Maximize the
workbook upon open. I can not get the Maximize to work. Someone please tell
me the secret to the maximize command in Excel with a locked page.

Thanks

DDBeards

Top