Check box question???

[Pas]

Is there a way by clicking on a check box, it would do the following to a
particular cell:
1. insert a border
2. insert a data validation list
3. colour the back ground to white

and when unchecked it all disappears untill its checked again.

 

[Dave Peterson]

Record a macro when you do the border, the data|validation and the formatting.

Then record a different macro when you remove what you want.

Then add a checkbox from the Forms toolbar (not the control toolbox toolbar) to
the worksheet.

Then assign this macro to the checkbox.

Option Explicit
Sub TestMe()

Dim CBX As CheckBox

Set CBX = ActiveSheet.CheckBoxes(Application.Caller)

If CBX.Value = xlOn Then
Call AddTheStuff
Else
Call RemoveTheStuff
End If

End Sub

I used AddTheStuff and RemoveTheStuff for the names of the macros in my testing.

You may want to change the recorded macro names to something significant--along
with the macro (TestMe isn't a very good name!).


(for testing only)

Sub AddTheStuff()
MsgBox "Your code to do the work"
End Sub
Sub RemoveTheStuff()
MsgBox "Your code to remove the work"
End Sub

 

[Pas]

Thanks Dave
I shall try that.

Record a macro when you do the border, the data|validation and the formatting.

Then record a different macro when you remove what you want.

Then add a checkbox from the Forms toolbar (not the control toolbox toolbar) to
the worksheet.

Then assign this macro to the checkbox.

Option Explicit
Sub TestMe()

Dim CBX As CheckBox

Set CBX = ActiveSheet.CheckBoxes(Application.Caller)

If CBX.Value = xlOn Then
Call AddTheStuff
Else
Call RemoveTheStuff
End If

End Sub

I used AddTheStuff and RemoveTheStuff for the names of the macros in my testing.

You may want to change the recorded macro names to something significant--along
with the macro (TestMe isn't a very good name!).


(for testing only)

Sub AddTheStuff()
MsgBox "Your code to do the work"
End Sub
Sub RemoveTheStuff()
MsgBox "Your code to remove the work"
End Sub

 

[Pas]

It works without the validation entry. I will have a go at trying to see
whats wrong and let you know

 

[Pas]

Final code works well and as follows:

Option Explicit

Sub AddTheStuff()
'
' AddTheStuff Macro
' Macro recorded 09/03/2010 by pas.wilts
'
Sheets("Sheet5").Select
Range("F12:H12").Select
ActiveCell.FormulaR1C1 = "Rear Facing Mid"
Range("F13:H13").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=pax1"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Interior.ColorIndex = 2
Sheets("Seats").Select
End Sub
Sub RemoveTheStuff()
'
' RemoveTheStuff Macro
' Macro recorded 09/03/2010 by pas.wilts
'
Sheets("Sheet5").Select
Range("F12:H13").Select
Selection.Interior.ColorIndex = 34
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("F13:H13").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Range("F12:H12").Select
Selection.ClearContents
Range("C10").Select
Sheets("Seats").Select
End Sub

Thank you