Code for Exception

inactiveUser214710

Board Regular
Joined
Apr 27, 2012
Messages
171
Hello everyone
I have a userform with several commdandbuttons and I use the code below to close all, when I give instrution for that.
But I need just one that must be open and not closed
How can i do for that exception.
Thank you
Code:
Sub resertFormClose()
Dim ctl
    For Each ctl In UserForm1.Controls
        
        If TypeOf ctl Is msforms.CommandButton Then
            ctl.Value = False
            ctl.BackColor = RGB(192, 192, 192)
            ctl.Enabled = False
            ctl.TabStop = True
        End If
    Next ctl
    
End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Try (amending the button name)...,
Code:
[COLOR=#333333]If TypeOf ctl Is msforms.CommandButton And ctrl.Name <> "CommandButton33" Then [/COLOR]
 
Last edited:
Upvote 0
Thank you for answer
I don't understand, sorry
I Have three commandbutton (cmd1;cmd2;cmd3 and cmd4).
cmd1 whose name is "Submit"
cmd2 ..................... "Clear"
cmd3 ..................... "Exit"
cmd4" .... ................."Month's Close"
When I click "cmd4" the module above close all, but I need cmd3 open to be exit of userform.
when you say (amending the button name), which name? cmd3 or "exit"?
Thank you
 
Upvote 0
but I need cmd3 open to be exit of userform
perhaps this...

Code:
Sub resertFormClose()
Dim ctl
    For Each ctl In UserForm1.Controls
        
        If TypeOf ctl Is msforms.CommandButton And ctrl.Name <> "[COLOR=#ff0000]cmd3[/COLOR]" Then 
            ctl.Value = False
            ctl.BackColor = RGB(192, 192, 192)
            ctl.Enabled = False
            ctl.TabStop = True
        End If
    Next ctl
    
End Sub
 
Upvote 0
Ah - a small mistake which perhaps you did not notice ...:)


Code:
Sub resertFormClose()
Dim ctl
    For Each ctl In UserForm1.Controls
        
        If TypeOf ctl Is msforms.CommandButton And [COLOR=#ff0000]ctl[/COLOR].Name <> "cmd3" Then 
            ctl.Value = False
            ctl.BackColor = RGB(192, 192, 192)
            ctl.Enabled = False
            ctl.TabStop = True
        End If
    Next ctl
    
End Sub
 
Upvote 0
Hi Yongle
Perfect!
Solved
The vba is very sensitive, a letter in wrong place "ZazaS", gives an error.
Now, it works.
Many Thanks for your help.
My sincere thanks
CG
 
Upvote 0

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top