none
Trouble making shapes visible RRS feed

  • Question

  • I have a worksheet with a variety of shapes (option buttons, buttons, rectangles).  Depending on the option buttons selected, different rows within the worksheet are hidden.  This is to streamline what the user has to enter on the spreadsheet based on the options selected.  

    I have different subroutines written for each option selected but all are running into the same problem.  When the subroutine is run, the shapes are not visible on the worksheet.  If I highlight the rows, I can see the shapes there.  Also, and I find this to be very odd, if I step through the code using PF8, the shapes are visible.  But if I let the code just run automatically upon the option selection or run the subroutine code using PF5, they are not visible.  

    The code below is a sample of one of the macros which executes automatically upon an option selection.  What am I missing -- or doing wrong?  Any help you could offer will be appreciated.  I'm about ready to just give up on streamlining (hiding rows). 

    Sub Mega_Click()
        
        Dim wsRequest As Worksheet
        
        Set wsRequest = ThisWorkbook.Worksheets("Request")
        Set wsRequest = ActiveSheet
        
        With wsRequest

            .Range("EagleFileRows").EntireRow.Hidden = False
            
            If .Range("Converted") = "Yes" Then
                .Range("ConvDateRows").EntireRow.Hidden = False
                ActiveSheet.Shapes("Button 39").Visible = msoTrue   'Report 171
                ActiveSheet.Shapes("Button 41").Visible = msoTrue   'Conversion Date Eagle Trade Activity
                ActiveSheet.Shapes("Button 42").Visible = msoTrue   'Lot conversion workbook
            Else
                .Range("ConvDateRows").EntireRow.Hidden = True
            End If
            
            .Range("Platform") = "Mega"
            .Range("TransFilePath") = ""
            .Range("ConvLotPath") = ""
            .Range("cTradeActivityPath") = ""
            .Range("Converted") = ""

            ActiveSheet.Shapes("Rectangle 11").Visible = msoTrue   'Execute macro button
            ActiveSheet.Shapes("Group Box 46").Visible = msoTrue
            ActiveSheet.Shapes("Option Button 44").Visible = msoTrue
            ActiveSheet.Shapes("Option Button 45").Visible = msoTrue
            ActiveSheet.Shapes("Group Box 20").Visible = msoTrue
            ActiveSheet.Shapes("Option Button 8").Visible = msoTrue
            ActiveSheet.Shapes("Option Button 9").Visible = msoTrue
            ActiveSheet.Shapes("Option Button 27").Visible = msoTrue
            ActiveSheet.Shapes("Button 36").Visible = msoTrue   'Realized Gain/Loss report
            ActiveSheet.Shapes("Button 37").Visible = msoTrue   'Trade Activity report
            ActiveSheet.Shapes("Button 40").Visible = msoTrue   'Port Val
            
            If .Range("Converted") = "Yes" Then
                ActiveSheet.Shapes("Button 39").Visible = msoTrue   'Report 171
                ActiveSheet.Shapes("Button 41").Visible = msoTrue   'Conversion Date Trade Activity
                ActiveSheet.Shapes("Button 42").Visible = msoTrue   'Lot conversion workbook
            End If

        End With
        
    End Sub

    Wednesday, September 3, 2014 5:14 PM

All replies

  • Are the shapes within the ranges being hidden? If so then while the button is visible right click and select Format control and then Properties tab and set option to "Don't move or size with cells"

    For shapes like rectangles right click and select Size and properties and then select properties in the left column and set option similarly to above.


    Regards, OssieMac

    Saturday, September 6, 2014 3:17 AM