ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   UserForms stuff (https://www.excelbanter.com/excel-programming/354963-userforms-stuff.html)

Mark Dullingham
UserForms stuff
 
I have a minor problem thats really bugging me and I hope some can help.

I have a userform with 4 check boxes and 2 command buttons. When opened the
check box values are true(ticked). The form has the following code-

Private Sub CheckBox1_Click()
If CheckBox1.Value = False Then
Range("D:D,I:I,O:O,R:R,W:W,AB:AB,AG:AG").Select
Selection.EntireColumn.Hidden = True
End If
If CheckBox1.Value = True Then
Range("D:D,I:I,O:O,R:R,W:W,AB:AB,AG:AG").Select
Selection.EntireColumn.Hidden = False
End If
End Sub
Private Sub CheckBox2_Click()
If CheckBox2.Value = False Then
Range("E:E,J:J,P:P,S:S,X:X,AC:AC,AH:AH").Select
Selection.EntireColumn.Hidden = True
End If
If CheckBox2.Value = True Then
Range("E:E,J:J,P:P,S:S,X:X,AC:AC,AH:AH").Select
Selection.EntireColumn.Hidden = False
End If
End Sub
Private Sub CheckBox3_Click()
If CheckBox3.Value = False Then
Range("F:F,K:K,O:O,T:T,Y:Y,AD:AD,AI:AI").Select
Selection.EntireColumn.Hidden = True
End If
If CheckBox3.Value = True Then
Range("F:F,K:K,O:O,T:T,Y:Y,AD:AD,AI:AI").Select
Selection.EntireColumn.Hidden = False
End If
End Sub
Private Sub CheckBox4_Click()
If CheckBox4.Value = False Then
Range("G:G,L:L,U:U,Z:Z,AB:AJ").Select:
Selection.EntireColumn.Hidden = True
Rows("26:41").Select
Selection.EntireRow.Hidden = True
End If
If CheckBox4.Value = True Then
Range("G:G,L:L,U:U,Z:Z,AB:AJ").Select
Selection.EntireColumn.Hidden = False
Rows("26:41").Select
Selection.EntireRow.Hidden = False
End If
End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub CommandButton2_Click()
CheckBox1.Value = True
CheckBox2.Value = True
CheckBox3.Value = True
CheckBox4.Value = True
ActiveSheet.Range("A:AM").Select
Selection.EntireColumn.Hidden = False
Rows("26:41").Select
Selection.EntireRow.Hidden = False
End Sub

My problem is that when the check boxes are re-checked (this is in case the
user make a mistake or changes their mind) or command button 2 (this is a
reset button)is used, the selection in the 'ranges' are highlighted and I
don't want them to be.

Could any one help please, this is really annoying.

Thanks in Advance

Mark

Dave Peterson
UserForms stuff
 
Remove the .select's.

Range("D:D,I:I,O:O,R:R,W:W,AB:AB,AG:AG").Select
Selection.EntireColumn.Hidden = True

becomes

Range("D:D,I:I,O:O,R:R,W:W,AB:AB,AG:AG").EntireCol umn.Hidden = True



Mark Dullingham wrote:

I have a minor problem thats really bugging me and I hope some can help.

I have a userform with 4 check boxes and 2 command buttons. When opened the
check box values are true(ticked). The form has the following code-

Private Sub CheckBox1_Click()
If CheckBox1.Value = False Then
Range("D:D,I:I,O:O,R:R,W:W,AB:AB,AG:AG").Select
Selection.EntireColumn.Hidden = True
End If
If CheckBox1.Value = True Then
Range("D:D,I:I,O:O,R:R,W:W,AB:AB,AG:AG").Select
Selection.EntireColumn.Hidden = False
End If
End Sub
Private Sub CheckBox2_Click()
If CheckBox2.Value = False Then
Range("E:E,J:J,P:P,S:S,X:X,AC:AC,AH:AH").Select
Selection.EntireColumn.Hidden = True
End If
If CheckBox2.Value = True Then
Range("E:E,J:J,P:P,S:S,X:X,AC:AC,AH:AH").Select
Selection.EntireColumn.Hidden = False
End If
End Sub
Private Sub CheckBox3_Click()
If CheckBox3.Value = False Then
Range("F:F,K:K,O:O,T:T,Y:Y,AD:AD,AI:AI").Select
Selection.EntireColumn.Hidden = True
End If
If CheckBox3.Value = True Then
Range("F:F,K:K,O:O,T:T,Y:Y,AD:AD,AI:AI").Select
Selection.EntireColumn.Hidden = False
End If
End Sub
Private Sub CheckBox4_Click()
If CheckBox4.Value = False Then
Range("G:G,L:L,U:U,Z:Z,AB:AJ").Select:
Selection.EntireColumn.Hidden = True
Rows("26:41").Select
Selection.EntireRow.Hidden = True
End If
If CheckBox4.Value = True Then
Range("G:G,L:L,U:U,Z:Z,AB:AJ").Select
Selection.EntireColumn.Hidden = False
Rows("26:41").Select
Selection.EntireRow.Hidden = False
End If
End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub CommandButton2_Click()
CheckBox1.Value = True
CheckBox2.Value = True
CheckBox3.Value = True
CheckBox4.Value = True
ActiveSheet.Range("A:AM").Select
Selection.EntireColumn.Hidden = False
Rows("26:41").Select
Selection.EntireRow.Hidden = False
End Sub

My problem is that when the check boxes are re-checked (this is in case the
user make a mistake or changes their mind) or command button 2 (this is a
reset button)is used, the selection in the 'ranges' are highlighted and I
don't want them to be.

Could any one help please, this is really annoying.

Thanks in Advance

Mark

--

Dave Peterson

Mark Dullingham
UserForms stuff
 
Thanks Dave,
seems pretty obvious when you see it written down.
I'm a novice at code, so to create this kind of thing I record a macro then
copy the code, hence the mistake.

Thanks once again for your speedy response. These forums, or more to the
point, the people who monitor them, always come up trumps.

"Dave Peterson" wrote:

Remove the .select's.

Range("D:D,I:I,O:O,R:R,W:W,AB:AB,AG:AG").Select
Selection.EntireColumn.Hidden = True

becomes

Range("D:D,I:I,O:O,R:R,W:W,AB:AB,AG:AG").EntireCol umn.Hidden = True



Mark Dullingham wrote:

I have a minor problem thats really bugging me and I hope some can help.

I have a userform with 4 check boxes and 2 command buttons. When opened the
check box values are true(ticked). The form has the following code-

Private Sub CheckBox1_Click()
If CheckBox1.Value = False Then
Range("D:D,I:I,O:O,R:R,W:W,AB:AB,AG:AG").Select
Selection.EntireColumn.Hidden = True
End If
If CheckBox1.Value = True Then
Range("D:D,I:I,O:O,R:R,W:W,AB:AB,AG:AG").Select
Selection.EntireColumn.Hidden = False
End If
End Sub
Private Sub CheckBox2_Click()
If CheckBox2.Value = False Then
Range("E:E,J:J,P:P,S:S,X:X,AC:AC,AH:AH").Select
Selection.EntireColumn.Hidden = True
End If
If CheckBox2.Value = True Then
Range("E:E,J:J,P:P,S:S,X:X,AC:AC,AH:AH").Select
Selection.EntireColumn.Hidden = False
End If
End Sub
Private Sub CheckBox3_Click()
If CheckBox3.Value = False Then
Range("F:F,K:K,O:O,T:T,Y:Y,AD:AD,AI:AI").Select
Selection.EntireColumn.Hidden = True
End If
If CheckBox3.Value = True Then
Range("F:F,K:K,O:O,T:T,Y:Y,AD:AD,AI:AI").Select
Selection.EntireColumn.Hidden = False
End If
End Sub
Private Sub CheckBox4_Click()
If CheckBox4.Value = False Then
Range("G:G,L:L,U:U,Z:Z,AB:AJ").Select:
Selection.EntireColumn.Hidden = True
Rows("26:41").Select
Selection.EntireRow.Hidden = True
End If
If CheckBox4.Value = True Then
Range("G:G,L:L,U:U,Z:Z,AB:AJ").Select
Selection.EntireColumn.Hidden = False
Rows("26:41").Select
Selection.EntireRow.Hidden = False
End If
End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub CommandButton2_Click()
CheckBox1.Value = True
CheckBox2.Value = True
CheckBox3.Value = True
CheckBox4.Value = True
ActiveSheet.Range("A:AM").Select
Selection.EntireColumn.Hidden = False
Rows("26:41").Select
Selection.EntireRow.Hidden = False
End Sub

My problem is that when the check boxes are re-checked (this is in case the
user make a mistake or changes their mind) or command button 2 (this is a
reset button)is used, the selection in the 'ranges' are highlighted and I
don't want them to be.

Could any one help please, this is really annoying.

Thanks in Advance

Mark

--

Dave Peterson


All times are GMT +1. The time now is 09:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com