Check box question???

  • Thread starter Thread starter Pas
  • Start date Start date
P

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.
 
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
 
Thanks Dave
I shall try that.

Dave Peterson said:
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
Click to expand...
 
It works without the validation entry. I will have a go at trying to see
whats wrong and let you know
 
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
 
Back
Top