How to create a MACRO run button and hide all the VBA Codes

[PRADEEPB270]

Hello Hansav,

Please refer the attach file:-

Now,I want to learn some more curiosity.These are:-

1-How to create macro run button on Summary sheet?Please mention the step wise process.
2-Can the VBA codes be hide for run this macro button?Pl.mention the steps
3-If wants to modify this macro button vba codes,then it is possible or not?How?Pl.explain the process.

[Rudi]

1-How to create macro run button on Summary sheet?Please mention the step wise process.
2-Can the VBA codes be hide for run this macro button?Pl.mention the steps
3-If wants to modify this macro button vba codes,then it is possible or not?How?Pl.explain the process.

Hi,

Question 1:
To create the macro button, you must access the Developer ribbon.
Right click on any ribbon and choose Customise ribbon, or go through the File|Options route
In the Customise dialog, tick the Developer option in the right column (See: http://www.taltech.com/images/uploads/p ... -tab-2.jpg)
Once you have the developer ribbon available, use the Insert button and choose the Command button under the Active X Controls
In Excel, click on the sheet to add a default button. (You can drag it to size and position it)
Double click it to access the event handler (where you assign the code it must run) : In your sample button it is the Private Sub CommandButton1_Click() handler.

1.jpg

Question 2:
You can protect the VBA Project with a password to prevent persons from accessing and modifying the code.
To do this, you must right click on the Project entry in the Projects window and choose VBAProject Properties
In the dialog, click on the Protection Tab
Tick the Lock project for viewing option
Give the project a password.

2.jpg

3.jpg

Question 3:
To modify the code under the button, you can press ALT+F11 (to access the VBA Editor)
Double click on the Sheet1 (Summary) item in the Project window and it will access the code below the button.
You can also right click on the Macro button and choose View Code to access the code, BUT, the button must be in Design mode before you can do this.
To do this, click the Design Mode button on the Developer ribbon.
Note: If you have assigned a password to your VBA project, you will get prompted for the password before you can access the code and events.

Observation:
In your button code there is a common error that I recommend you correct.
Each DIM statement must have an AS clause.... In otherwords it is not good practice to do this:
Dim d As Object, e, f
but rather do this:
Dim d As Object, e As Object, f As Object

In your example, the variables for "e" and "f" as seen as Variant.
Even if you intend to have them Variant, it is better practice to explicitly declare them like this:
Dim e As Variant, F As Variant

[PRADEEPB270]

I have applied as you have explained but the code is not running.I have given the password as you explained in question no.2 i.e.1234.

Please look the attach file,for what am I missing the steps to run the working?

[HansV]

The code you have is for CommandButton1_Click, but the command button is named CommandButton2:

S0354.png

If you double-click this command button while in Design Mode, you'll see that a NEW procedure is created:

S0355.png

Move the code to the new procedure, then delete the old one (it serves no purpose):

S0356.png

(I inserted a line Option Explicit at the top - see The importance of 'Option Explicit' for the reason why)

[PRADEEPB270]

Now,I have understand these procedures.Thank a lot to you.