Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

delete column when checkbox is false

0

Hello

in  this  file  I  have  command  button  to  clear  all of  data  in sheet2 when and  make  all of checkboxes are false  , but  I  want  when make  any  check box  is  false,  then  should  delete  the  column, just   the  checkbox is  false   and  ignore  the  others are true .

 I add this  by  call  from  every  checkbox  but  doesn't  work 

Private Sub ForAllCheckBoxes(ChkBox As Control)
Dim fndHead As Range
If ChkBox.Value = False Then
With sh2

 Set fndHead = .Rows("1:1").Find(What:=ChkBox.Caption, LookIn:=xlValues, _
                                        LookAt:=xlWhole, SearchOrder:=xlByColumns, _
                                        SearchDirection:=xlNext, MatchCase:=False)
        If Not fndHead Is Nothing Then
                        .Columns(fndHead.Column).Delete
                        End If
                        End With
                        End If

End Sub
Private Sub CheckBox1_Click()
With CheckBox1
    If .Value = True Then Call CopyColumn(.Caption)
    If .Value = False Then Call ForAllCheckBoxes

End With
End Sub
excel
posted by: Halk (rep: 30) on Mon Oct 10, 2022 at 3:26 pm
Answer
Discuss

Answers

0
Selected Answer

Hi Halk

The problem is that you didn't pass the variable needed by the new sub ForAllCheckBoxes(ChkBox As Control). Your CheckBox subs need to read (changes in bold):

Private Sub CheckBox1_Click()
With CheckBox1
    If .Value = True Then Call CopyColumn(.Caption)
    If .Value = False Then Call ForAllCheckBoxes(CheckBox1)

End With
End Sub

Private Sub CheckBox2_Click()
With CheckBox2
    If .Value = True Then Call CopyColumn(.Caption)
    If .Value = False Then Call ForAllCheckBoxes(CheckBox2)
End With
End Sub

etc.

You only call your new sub when the CheckBox is clicked to False so your new sub can be reduced to this (and lose the Block Ifs):

Private Sub ForAllCheckBoxes(ChkBox As Control)
Dim fndHead As Range

With sh2
 Set fndHead = .Rows("1:1").Find(What:=ChkBox.Caption, LookAt:=xlWhole)
    If Not fndHead Is Nothing Then .Columns(fndHead.Column).Delete
End With

End Sub

Those changes are made in the revised file attached.

Note that it will only delete the first instance of the heading. If you expect to have (say) "EMPLOYEE" copied twice or more to sheet2, you'll need to add a loop to capture all instances.

Hope this works for you.

posted by: John_Ru (rep: 6142) on Tue Oct 11, 2022 at 6:39 am
Discuss

Discussion

If you expect to have (say) "EMPLOYEE" copied twice or more to sheet2, you'll need to add a loop to capture all instances.
no I  don't  need  it .
thanks  again John for  your  supporting !
Halk (rep: 30) Oct 11, '22 at 7:55 am
I suspected you would not need that but wanted to point out a possible problem.

Thanks for selecting my Answer, Halk.
John_Ru (rep: 6142) Oct 11, '22 at 8:39 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login