alexsheehan
Member
Hi folks
I have a group of cells in $D$23:$I$23 & $D$25:$I$25 that I want to change to Tick/Cross values (a checkbox without using Form Controls or ActiveX Controls) and I have managed to do this with VBA, however the problem comes when trying to copy this to multiplpe ranges within the same sheet.
I can get the my code to copy so far down the sheet but then I see to hit a brick wall with my "Range" selection code.
To be more specific, I have in-cell checkboxes in cell ranges $D$23:$I$23 & $D$25:$I$25 (using hyperlinks instead of using Form Controls or ActiveX controls and using Wingdings font) eg. a "Cross" is =HYPERLINK("","ý") and a "Tick" is =HYPERLINK("","þ").
The Hyperlinks repeat every other 32 rows, so $D$57:$I$57 & $D$59:$I$59 will be the next range(s) and so on every other 32 rows down the sheet. Here's my code;-
[pre]
[/pre]
Everything worked as it should until I added another range to the code above ($D$295:$I$295, $D$297:$I$297). Upon which it returns the error "Run time error 1004, Methos 'Range' of object '_Worksheet' failed"
As soon as I remove these ranges from the code it functions as normal.
Is there some kind of undocumented limit to the Range() function? Is there something really simple I am missing?
If there is a simpler way of achieving the result I want to achieve then I am open to suggestions, or if there is a way to offset each range by 32 and cycle through each range until it reaches the end of the data range then that would be favourable, but I don't know with my current level of knowledge if this is achievable or not!
Can anyone shed light on this for me please?
Cheers guys
Alex
I have a group of cells in $D$23:$I$23 & $D$25:$I$25 that I want to change to Tick/Cross values (a checkbox without using Form Controls or ActiveX Controls) and I have managed to do this with VBA, however the problem comes when trying to copy this to multiplpe ranges within the same sheet.
I can get the my code to copy so far down the sheet but then I see to hit a brick wall with my "Range" selection code.
To be more specific, I have in-cell checkboxes in cell ranges $D$23:$I$23 & $D$25:$I$25 (using hyperlinks instead of using Form Controls or ActiveX controls and using Wingdings font) eg. a "Cross" is =HYPERLINK("","ý") and a "Tick" is =HYPERLINK("","þ").
The Hyperlinks repeat every other 32 rows, so $D$57:$I$57 & $D$59:$I$59 will be the next range(s) and so on every other 32 rows down the sheet. Here's my code;-
[pre]
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("$D$23:$I$23, $D$25:$I$25, $D$57:$I$57,
$D$59:$I$59, $D$91:$I$91, $D$93:$I$93, $D$125:$I$125, $D$127:$I$127,
$D$159:$I$159, $D$161:$I$161, $D$193:$I$193, $D$195:$I$195, $D$227:$I$227,
$D$229:$I$229, $D$261:$I$261, $D$263:$I$263, $D$295:$I$295, $D$297:$I$297"))
Is Nothing Then
If Target = "ý" Then
Target = "=Hyperlink("""",""þ"")"
Target.Font.Name = "Wingdings"
Target.Font.Underline = False
Target.Font.Size = 22
Else
Target = "ý"
Target = "=Hyperlink("""",""ý"")"
Target.Font.Name = "Wingdings"
Target.Font.Underline = False
Target.Font.Size = 22
End If
End If
End SubEverything worked as it should until I added another range to the code above ($D$295:$I$295, $D$297:$I$297). Upon which it returns the error "Run time error 1004, Methos 'Range' of object '_Worksheet' failed"
As soon as I remove these ranges from the code it functions as normal.
Is there some kind of undocumented limit to the Range() function? Is there something really simple I am missing?
If there is a simpler way of achieving the result I want to achieve then I am open to suggestions, or if there is a way to offset each range by 32 and cycle through each range until it reaches the end of the data range then that would be favourable, but I don't know with my current level of knowledge if this is achievable or not!
Can anyone shed light on this for me please?
Cheers guys
Alex
