Using VBA Code change Height and Top on an Active X control - Run-time error 424

[SimpleSusan]

Hi,
I'm developing a form which hides and unhides rows depending on a series of conditions. Some of these rows have Active X controls in them. Due a bug in Excel 2010, I am having to write additional code to reset the Height and Top of these controls after you unhide the row (anyone else familiar with these disappearing ActiveX controls following hide, save, close, open, unhide - where has my bloody option button gone??) I have written some code that works perfectly if it is associated to a command button in the same worksheet as the Active X controls that I'm trying to change Height and Top on. However, I want to launch this code from a different worksheet, and I get a Run-time error 424 "Object Required" issue.

I am trying to launch the code from sheet "Incidents" relating to items on sheet "Claim Form". Code as follows. I have highlighted the line in red where the debugger starts - seems to relate to the Option Buttons

Sub Incident_C()
'Incident_C Macro
'This code unhides all rows that may have previously been hidden
'This code is valid for all initiator types
ActiveWorkbook.Sheets("Claim form").Activate
Rows("75:356").Select
Selection.EntireRow.Hidden = False

'This code hides the columns containing major incident notification requirements
'This code is valid for all initiator types
Columns("I:N").Select
Selection.EntireColumn.Hidden = True

'This code hides initiator "C" items
'Rows("75:81").Select
'Selection.EntireRow.Hidden = True

'This code hides initiator "D" items
Rows("82:101").Select
Selection.EntireRow.Hidden = True

'This code hides initiator "E" items
Rows("102:119").Select
Selection.EntireRow.Hidden = True

'This code hides initiator "F" items
Rows("120:168").Select
Selection.EntireRow.Hidden = True

'This code hides initiator "G" items
Rows("169:205").Select
Selection.EntireRow.Hidden = True

'This code hides initiator "H" items
Rows("206:215").Select
Selection.EntireRow.Hidden = True

'This code hides initiator "I" items
Rows("216:240").Select
Selection.EntireRow.Hidden = True

'This code hides initiator "J" items
Rows("241:258").Select
Selection.EntireRow.Hidden = True

'This code hides initiator "K" items
Rows("259:272").Select
Selection.EntireRow.Hidden = True

'This code hides "Credit Details" items
Rows("273:285").Select
Selection.EntireRow.Hidden = True

'This code hides "Product Code Details" items
'This code is conditionally required for initator types
'Rows("286:309").Select
'Selection.EntireRow.Hidden = True

'This code hides "Major Incident Notificaton" items
'This code is valid for all initiator types
Rows("316:356").Select
Selection.EntireRow.Hidden = True

'This code resets the height and position of all ActiveX controls within the C initiator
'It is required due to a bug with Microsoft Excel 2010 - Thanks Bill Gates
OptionButton1.Height = 19.5
OptionButton1.Top = 1066.5
OptionButton2.Height = 19.5
OptionButton2.Top = 1066.5

'This code sets which button to be selected at start
OptionButton1.Value = True
'This code takes you to the top of the form
'This code is valid for all initiator types
Range("A1").Select

'This code takes you to the Incident C dashboard
ActiveWorkbook.Sheets("C Incident").Activate
ActiveSheet.Range("A1").Select

End Sub

I just can't nut this one out. I know there is a patch that is "supposed" to fix this Active X disappearing issue, but the company I work for won't roll it so I have to find a work around

Thanks in advance,
Susan

 

[AlphaFrog]

Try something like this. Set the sheet name to suit.

[color=darkblue]With[/color] Sheets("Sheet1")
    [color=darkblue]With[/color] .OLEObjects("OptionButton1")
        .Height = 19.5
        .Top = 1066.5
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]With[/color] .OLEObjects("OptionButton2")
        .Height = 19.5
        .Top = 1066.5
    [color=darkblue]End[/color] With
End With

 

[SimpleSusan]

Thanks for this. I thought this was working, but for some reason, it doesn't work for all options. When I save the file, open it, then run the code to expand the rows, the buttons don't appear, yet looking at the properties, they are there, and with the correct height, but for some reason they're not visible. I'll then run a different "initiator" (ie: similiar code but for a different set of buttons) and it works ok. I then go back to the original one (ie: the one that still wouldn't show the buttons) and it then works ok!! What????? It seems the first time I run it it doesn't work, but the second time, it works ok. This makes no sense to me.

All of this because of Microsoft's bug they can't fix with a decent patch. Are there any other options to make these things visible again after hiding the rows, saving, exiting then unhiding the rows again?