command button and assign macro

R

RichardO

Hello all:

I am just learning how to use vba in excel.

I have a code that I would like to
1) assign to a command button, how do I do this?
2) I also don't want users to be able to move or delete the comman
button, how do I do this.
3) Can I protect the code in the command button so that users can'
change the code?
4) Also, is it possible for me to disable the button once it has bee
clicked once, so that users don't keep on running the code once it'
been executed once?

Thanks much for all your help.


Richardo
 
D

Dave Peterson

#1. There are two different types of buttons you can use on a worksheet.

The first is from the control toolbox toolbar. You just double click on it and
put your code within that sub.

The second is from the Forms toolbar. You put your code in a General module.
Then add the button. When you've added it, you'll be prompted to assign the
macro to the button. (You can right click on it later and then assign the macro
if you weren't ready.)

#2. If you protect the worksheet, then the buttons will be safe.

#3. Inside the VBE, you can protect your project.
Select your project and then Tools|VBAProject Properties|Protection Tab

Give it a memorable password and check that box to lock from viewing.

#4. From the Control toolbox toolbar:

Option Explicit
Private Sub CommandButton1_Click()
MsgBox "HI"
Me.CommandButton1.Visible = False
'or
Me.CommandButton1.Enabled = False
End Sub

You could hide it or disable it. (and show it later/enable it later).

From the Forms toolbar:
Option Explicit
Sub testme01()
MsgBox "Yo"
With ActiveSheet.Buttons(Application.Caller)
.Enabled = True
'or
.Visible = False
End With
End Sub
 
R

RichardO

Wow, thanks much for your explanations, Dave:

For your response to #4, are you saying that if I want to disable/hid
and then enable/unhide the button, I should only use the Forms toolba
(and not the controlbox toolbar) and the following code will disabl
and then enable the button later on?

Option Explicit
Sub testme01()
MsgBox "Yo"
With ActiveSheet.Buttons(Application.Caller)
.Enabled = True
'or
.Visible = False
End With
End Sub


Richard
 
D

Dave Peterson

That's not what I meant--but may have been what I wrote!

I meant that you'd need different code depending on what type of buttons you
used to disable/hide the button.

And if you disable/hide either of the buttons, then you won't be able to click
on that button to toggle it back to enabled/visible. You'll need some other way
to turn it back on--another button with code that points at that other button.

And just in case, I meant that you could choose to disable the button or hide
it. You don't need to do both.
 
Top