Select ActiveCell Range

T

Tanya

Hi
I have this checkbox which runs two macros as follows

Private Sub CheckBox1_Click()
If CheckBox1 Then
Yellow
Else
Green
End If
End Sub

Macro 1 is as Follows
Sub Yellow()
'
' Macro1 Macro
'
Range("D4").Select
Range(ActiveCell, ActiveCell.Offset(29, 0)).Select

With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Sub

Question ----------------------------------------------------
How can I in macro1 select those cells immediate below the buttom so that I
can use the same macro with another checkbox?

I also have a password on this worksheet and it appears I want to be able to
unprotect then protect the range at the conclusion. This is confusing for me
because I have two macros [i.e. macro1 and macro2] to consider.

Any assistance would be appreciated.
Regards
Tanya
 
T

Tanya

I want to lock these cells so that they can only be edited if the checkbox is
ticked.
Thanking anyone that can help in advance.
Tanya
 
G

Gary Keramidas

not sure exactly what you're trying to do, but maybe this will at least give an
idea. there is no need to select the cells.

untested
Sub test2()
' Macro1 Macro
If checkbox1.Value = True Then

With Range(Range("D4"), Range("D4").Offset(29))
.Locked = False
.Interior.ColorIndex = 36
.Interior.Pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
End With
End If
End Sub



--


Gary


Tanya said:
I want to lock these cells so that they can only be edited if the checkbox is
ticked.
Thanking anyone that can help in advance.
Tanya

Tanya said:
Hi
I have this checkbox which runs two macros as follows

Private Sub CheckBox1_Click()
If CheckBox1 Then
Yellow
Else
Green
End If
End Sub

Macro 1 is as Follows
Sub Yellow()
'
' Macro1 Macro
'
Range("D4").Select
Range(ActiveCell, ActiveCell.Offset(29, 0)).Select

With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Sub

Question ----------------------------------------------------
How can I in macro1 select those cells immediate below the buttom so that I
can use the same macro with another checkbox?

I also have a password on this worksheet and it appears I want to be able to
unprotect then protect the range at the conclusion. This is confusing for me
because I have two macros [i.e. macro1 and macro2] to consider.

Any assistance would be appreciated.
Regards
Tanya
 
T

Tanya

Hi Gary
I am working on a grade workbook for my classes and would like to highlight
the focus outcomes by column. The workbook is protected and I have since
worked out how to get around that problem with the adjustments below. The
idea is to make the focus outcomes change colour by column so that a teacher
can quickly place the appropriate grade against a student under the relevant
outcome. I have decided upon two colour [macro 'Yellow' and macro 'Green'.
At the same time it would be beneficial if the teacher was unable to select a
cell other than those highlighted, just to prevent further error. Therefore
I am trying to write a command to unlock the cells in question only when the
box has been selected and locked [Macro: 'Yellow'] again when deselected
[Macro: 'Green']

This is as far as I have gotten on this problem to date:
!!! I have just noticed you have added a line [ .Locked = False ] and will
look at this.


Private Sub CheckBox1_Click()

ActiveSheet.Unprotect Password:="BBHS"

If CheckBox1 Then
Yellow
Else
Green
End If

ActiveSheet.Protect Password:="BBHS"
End Sub

Thanks for your prompt feedback.
Regards
Tanya

Gary Keramidas said:
not sure exactly what you're trying to do, but maybe this will at least give an
idea. there is no need to select the cells.

untested
Sub test2()
' Macro1 Macro
If checkbox1.Value = True Then

With Range(Range("D4"), Range("D4").Offset(29))
.Locked = False
.Interior.ColorIndex = 36
.Interior.Pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
End With
End If
End Sub



--


Gary


Tanya said:
I want to lock these cells so that they can only be edited if the checkbox is
ticked.
Thanking anyone that can help in advance.
Tanya

Tanya said:
Hi
I have this checkbox which runs two macros as follows

Private Sub CheckBox1_Click()
If CheckBox1 Then
Yellow
Else
Green
End If
End Sub

Macro 1 is as Follows
Sub Yellow()
'
' Macro1 Macro
'
Range("D4").Select
Range(ActiveCell, ActiveCell.Offset(29, 0)).Select

With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Sub

Question ----------------------------------------------------
How can I in macro1 select those cells immediate below the buttom so that I
can use the same macro with another checkbox?

I also have a password on this worksheet and it appears I want to be able to
unprotect then protect the range at the conclusion. This is confusing for me
because I have two macros [i.e. macro1 and macro2] to consider.

Any assistance would be appreciated.
Regards
Tanya
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top