vba in userform to delete columns

[ashleywanless]

Hi,

I have created a form with 40 checkboxes and each one relates to a column. When the user clicks the command button on the form i want the code to check if checkbox value is true and if so delete column.

The only way i can think of to do this is by writing 40 if statements and for each if stating if value is true use the find function to find the column header name, the delete the active column.

Is there a better way of doing this? i can set find to start in A1 and find by row but still worried this may not be the best way of doing this|?

Thanks

 

[MickG]

Try this, Looping through the Userform Controls:-
NB:- The tabIndex = Zero based , so by adding "1" as in the code below,
Then if you Check CheckBox2 and checkbox5, columns (2) & (5) will be deleted.

[COLOR=navy]Sub[/COLOR] MG01Nov37
[COLOR=navy]Dim[/COLOR] chbox [COLOR=navy]As[/COLOR] Control
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] chbox [COLOR=navy]In[/COLOR] Me.Controls
[COLOR=navy]If[/COLOR] TypeName(chbox) = "CheckBox" [COLOR=navy]Then[/COLOR]
    [COLOR=navy]If[/COLOR] chbox.value = True [COLOR=navy]Then[/COLOR]
        Columns(chbox.TabIndex + 1).Delete
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] chbox
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[MickG]

I've just realised that won't work , you need to loop through your columns last to first,Try this:-

Private [COLOR=navy]Sub[/COLOR] CommandButton1_Click()
[COLOR=navy]Dim[/COLOR] Num [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]For[/COLOR] Num = 40 To 1 [COLOR=navy]Step[/COLOR] -1
    [COLOR=navy]If[/COLOR] Me.Controls("CheckBox" & Num).Object.value = True [COLOR=navy]Then[/COLOR]
       Columns(Num).Delete
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Num
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[ashleywanless]

thanks Mick