Toggle between button events in excel vba

[vijay.vizzu]

Hi..all

I am working on a project, in which i create a button for New & Cancel option. I did on form activate, the button caption will be "New". When that new button will click, the new caption will change to cancel.

But now i want, whenever this button again click that means for cancel purpose, all fields should be reset to their normal position. I can't able to achieve this. So please help me to resolve

Private Sub cmd_NewCancel_Click()
    cmd_Close.Enabled = False
    cmd_NewCancel.Enabled = True
    cmd_NewCancel.Caption = "Cancel"
    cmd_Save.Enabled = True
    cmd_Preview.Enabled = False
   
    txt_VendCd.Enabled = True
    txt_VendCd.SetFocus
   
    opt_SJP.Enabled = True
    opt_SJP.Value = True
    opt_CHE.Enabled = True
   
    lbl_Dt.Caption = Format(Now(), "dd-mmm-yyyy")
   
    cbo_Model.Enabled = True
    cbo_Req_No.Enabled = True
    cbo_PIC.Enabled = True
   
   
   
End Sub

 

[Hui]

What about something like :

  If cmd_NewCancel.Caption = "Cancel" Then
  cmd_NewCancel.Caption = "New"
  'other code here
   
  Else
  cmd_NewCancel.Caption = "Cancel"
  'other code here
   
  End If

 

[NARAYANK991]

Hi Vijay ,

One more option :

1. Declare a global variable New_Or_Cancel As Boolean , which will be initialized to TRUE.

2. In your button click procedure , you need to have just one statement :

New_Or_Cancel = New_Or_Cancel XOR True

3. In your main program , you can have :

If New_Or_Cancel Then
'  Your code for New button
Else
'  Your code for Cancel button
Endif

Narayan

 

[vijay.vizzu]

Thank you Hui and Naryank.

i will try and revert...

 

[vijay.vizzu]

Hi..all

I am unable to achieve my purpose. I have tried above code (Hui's code), but i can't able to achieve. What i required is.. when cmd_NewCancel button will click the caption should be changed to cancel and if the user click cancel button, all the fields should be reset.

I can able to change the caption as cancel, but how can i add click event for that..

Please give your valuable comments

 

[Hui]

We assume you are using an Active X Button
Can you post the file as we have to make assumptions without seeing specifically what your doing

 

[vijay.vizzu]

Dear Hui,

Yes, i am using active X buttons. Please find attachment. Problem appearing in frm_PO_make, have a look on it

 

[vijay.vizzu]

Dear Naryank,

I tried your code, but it works for me.. might be i am doing something wrong. Please have a look on my below code

Private Sub cmd_NewCancel_Click()
    New_or_Cancel = True
    New_or_Cancel = New_or_Cancel Xor True
   
    If New_or_Cancel Then
        cmd_Close.Enabled = False
        cmd_NewCancel.Enabled = True
        cmd_NewCancel.Caption = "Cancel"
        cmd_Save.Enabled = True
        cmd_Preview.Enabled = False
   
        txt_VendCd.Enabled = True
        txt_VendCd.SetFocus
   
        opt_SJP.Enabled = True
        opt_SJP.Value = True
        opt_CHE.Enabled = True
   
        lbl_Dt.Caption = Format(Now(), "dd-mmm-yyyy")
   
        cbo_Model.Enabled = True
        cbo_Req_No.Enabled = True
        cbo_PIC.Enabled = True
    Else
        cmd_NewCancel.Caption = "New"
        cmd_Close.Enabled = True
        cmd_Save.Enabled = False
        cmd_Preview.Enabled = False
       
        txt_VendCd.Value = ""
        txt_VendCd.Enabled = False
        opt_SJP.Enabled = False
        opt_SJP.Value = True
        opt_CHE.Enabled = False
       
        lbl_Dt.Caption = ""
       
        cbo_Model.Clear
        cbo_Model.Enabled = False
        cbo_Req_No.Clear
        cbo_Req_No.Enabled = False
        cbo_PIC.Clear
        cbo_PIC.Enabled = False
    End If
End Sub

 

[NARAYANK991]

Hi Vijay ,

1. I have mentioned that the variable New_Or_Cancel can be initialized to TRUE ; this means that if you have a Workbook_Open or UserForm_Initialize procedure , you should put the statement :

New_Or_Cancel = True

there.

2. Removing this one statement from your procedure will now make it work correctly.

Private Sub cmd_NewCancel_Click()
    If New_or_Cancel Then
        cmd_Close.Enabled = False
        cmd_NewCancel.Enabled = True
        cmd_NewCancel.Caption = "Cancel"
        cmd_Save.Enabled = True
        cmd_Preview.Enabled = False

        txt_VendCd.Enabled = True
        txt_VendCd.SetFocus

        opt_SJP.Enabled = True
        opt_SJP.Value = True
        opt_CHE.Enabled = True

        lbl_Dt.Caption = Format(Now(), "dd-mmm-yyyy")

        cbo_Model.Enabled = True
        cbo_Req_No.Enabled = True
        cbo_PIC.Enabled = True
    Else
        cmd_NewCancel.Caption = "New"
        cmd_Close.Enabled = True
        cmd_Save.Enabled = False
        cmd_Preview.Enabled = False
    
        txt_VendCd.Value = ""
        txt_VendCd.Enabled = False
        opt_SJP.Enabled = False
        opt_SJP.Value = True
        opt_CHE.Enabled = False
    
        lbl_Dt.Caption = ""
    
        cbo_Model.Clear
        cbo_Model.Enabled = False
        cbo_Req_No.Clear
        cbo_Req_No.Enabled = False
        cbo_PIC.Clear
        cbo_PIC.Enabled = False
    End If
    New_or_Cancel = New_or_Cancel Xor True
End Sub

When you start , the variable New_Or_Cancel will be True ; when you click on the button , the following part will be executed :

        cmd_Close.Enabled = False
        cmd_NewCancel.Enabled = True
        cmd_NewCancel.Caption = "Cancel"
        cmd_Save.Enabled = True
        cmd_Preview.Enabled = False

        txt_VendCd.Enabled = True
        txt_VendCd.SetFocus

        opt_SJP.Enabled = True
        opt_SJP.Value = True
        opt_CHE.Enabled = True

        lbl_Dt.Caption = Format(Now(), "dd-mmm-yyyy")

        cbo_Model.Enabled = True
        cbo_Req_No.Enabled = True
        cbo_PIC.Enabled = True

and before exiting the procedure , the variable will be put to False.

The next time the button is clicked , the following portion will be executed :

        cmd_NewCancel.Caption = "New"
        cmd_Close.Enabled = True
        cmd_Save.Enabled = False
        cmd_Preview.Enabled = False
   
        txt_VendCd.Value = ""
        txt_VendCd.Enabled = False
        opt_SJP.Enabled = False
        opt_SJP.Value = True
        opt_CHE.Enabled = False
   
        lbl_Dt.Caption = ""
   
        cbo_Model.Clear
        cbo_Model.Enabled = False
        cbo_Req_No.Clear
        cbo_Req_No.Enabled = False
        cbo_PIC.Clear
        cbo_PIC.Enabled = False

and before exiting the procedure , the variable will be put to True.

This process will repeat.

Narayan

 

[vijay.vizzu]

thank you so much naryank.. for your better explanation. I changed my code as above.. it works fine..