1

I have a form where a macro will hide the relevant rows when called. It has multiple checkboxes within the range of rows. I know I can set the CheckBoxes to "Size and Move with Cells" but I do not want them to size with cells as the row needs to be resized to auto fit to the contents and it makes the CheckBox unusable. I also need to loop by row as the CheckBoxes are not within the same column and referencing a specific cell range will force me to rebuild this entire workbook.

I am able to loop through and hide all checkboxes on the sheet, but I need this to only hide CheckBoxes within a certain range of rows. Ideally, I would be able to reference the range in an indexed format to save a few hours of updating work. My current code throws the error "Object or With Variable not set" on the Intersect line. Any way to better reference the control/CheckBox location or even an entirely different method works for me and is much appreciated!

Dim CheckBoxes As OLEObject
Dim xRange As String
Dim r1 As Integer
Dim r2 As Integer

'r1 is beginning of row range, r2 is end of row range. 
'I use a formula but I believe it is irrelevant to include it here

r1 = 12
r2 = r1 + 5


xRange = r1 & ":" & r2

For Each CheckBox In ActiveSheet.OLEObjects
If Intersect(CheckBox.TopLeftCell, Range(xRange)) Then ***Error occurs here***
        CheckBox.Visible = False
Next

Edit: I am using Excel 2016 if that makes any difference

Quimby
  • 11
  • 3

1 Answers1

1

Range(xRange) does not resolve to a proper reference. Since it looks like you want to reference rows, try Rows(xRange) instead. Then, since Intersect returns a Range object, your If statement should be as follows . . .

If Not Intersect(CheckBox.TopLeftCell, Rows(xRange)) Is Nothing Then
Domenic
  • 7,844
  • 2
  • 9
  • 17
  • That worked for hiding the check boxes! Now I duplicated to code to make the checkboxes visible again and for some reason it is not working. Does excel not recognize the checkbox if the visible property is set to False? I'm confused why it will work to hide but not to make visible – Quimby Feb 16 '22 at 16:00
  • Setting the Visible property to True should make the checkbox visible again. – Domenic Feb 16 '22 at 18:39
  • That portion of the code is what is not working. I switched Checkbox.Visible = False to True. Selecting the box in design mode and viewing properties shows that the Visible property stays as False even after the code is run. I used the direct name of that box to set Visible to true. It makes it visible but then the checkbox is not clickable. the shadow appears a few rows down when attempting to click. Any idea what is occurring here? – Quimby Feb 16 '22 at 19:49
  • Could it be your `If` statement that's the problem? It should be `If Not Intersect( ....) Is Nothing Then`. – Domenic Feb 16 '22 at 20:00
  • Both are set to `If Not Intersect(CheckBox.TopLeftCell, Rows(xRange)) Is Nothing Then ` I can’t figure out why it works to hide the box but doesn’t to make it visible – Quimby Feb 17 '22 at 04:53