Posts by DM2

    Still getting an error on "Invalid Procedure Call..." for the first "For".

    I've seen a number of posts that do most of what I need, but not exactly what i'm looking for.


    I'd like to run the VBA on an active worksheet

    The VBA would copy the active work sheet and duplicate it and many times as there are names in a column on another spreadsheet.

    Worksheet "DATA", Cells A5:A34 contain the names I want to use

    Often the 1st cell has the name of the active worksheet so I want to test for that and make sure I don't duplicate it.

    I want the duplicate work sheets to appear after the active worksheet.


    I've placed the following code on the "DATA" Worksheet:

    I've placed the following code in a module:

    Code
    Function Sheet_Exists(WorkSheet_Name As String) As Boolean
        Dim Work_sheet As Worksheet
        Sheet_Exists = False
        
        For Each Work_sheet In ThisWorkbook.Worksheets
            If Work_sheet.Name = WorkSheet_Name Then
                Sheet_Exists = True
            End If
        Next
    End Function

    The line "Worksheets.Add().Name=Sheet-Name", adds new work sheets but doesn't copy the active worksheet

    The Line "ActiveSheet.Copy After :=ActiveWorkbook.Sheets(Worksheets.Count)" only duplicates the work sheet and doesn't place it after the active work sheet but rather at the end of the workbook.

    Re: Change Shape of Rectangle "Upward" not "Downward"


    That kind of works, but I realized that the height of rectangle "Wire_Duct_Fill" should be a percentage of the associated Wire_Duct_X image. I don't know how to determine the height of the Wire_Duct_X image. If the image spans 20 rows and each row is 12.75 in height, would the height be 20 x 12.75=255 (Wire Duct Height)?


    If so, than the the formula would be WDH (Wire Duct Height) x WDF (Wire Duct Fill Percentage)
    ...or "255 x 50% = 127.5" right???


    Also because the macro needs to affect the ...Fill... based on a formula, I changed the macro to "Private Sub Worksheet_Calculate()" which I think will now run based on a formula rather than the user manual entering the value.


    As a result the following needs to change, I'm not sure how to

    Code
    If Target.Address = "$C$41" Then
            Shapes("Wire_Duct_Fill").Height = WDH * WDF
            Shapes("Wire_Duct_Fill").Top = 429 - ActiveSheet.Shapes("Wire_Duct_Fill").Height
        End If


    The problem is that the macro hangs with the above code.


    Below is the new code.

    I'm working in Excel 2003, but eventually the macros will also need to work in Excel 2010.


    I'm trying to automate the size of a shape labeled "Wire_Duct_Fill", as well as possible clean up some other macros I've written.


    I have a sheet with five (4) images on the sheet which are pictures of plastic wire duct used hold wiring in a control panel. They're labeled "Wire_Duct_1", "...2" etc. The Macro sets all of these images "...Visible = False".
    Several Cells provide the following:
    - Cell "C39" = This is the "In Cell" drop down for the user to select the size of Wire Duct to Use
    - Cell "C40" = Contains an "IF" function to set the height of the Wire Duct.
    - Cell "C41" = Indicated the percentage the wire duct cavity is filled (I've got to write some formulas for this value to change automatically and will get to that latter)



    Is there a simpler way to determine if the image should be visible or not?


    The problem with the "...Fill..." portion is that the rectangle increase in height the wrong way...it gets larger in the downward direction and I want it to go in the up direction. I'm not sure if there isn't something I can add to the macro to anchor the bottom left corner of the "Wire_Duct_Fill" object.


    I tried to upload the EXCEL 2003 file which was 203 KB, which is slightly below the 205 KB limit but it wouldn't accept it unless I reduced the file size by Zipping it...


    Help is greatly appreciated.


    Regards,
    Dan