Command button and msgbox's
 

Hello everyone.

I have a worksheet that opens a simple userform when the file is opene
with 2 command buttons (bOwner and bUser). It checks to see who ha
opened the file (Application.UserName), looks up the persons ID in
list to determine if they are an owner or a user of the file. If th
person is not an owner, then the Owner button is disable
(bOwner.Enabled = False). Each button has vba attached to hide or sho
certain sheets in the file. I have disabled the "X" This part work
fine.

My question is, there are some people that will try to click th
"Owner" button even though it has been disabled. Can I have a messag
box appear that tells them they have to choose the other button (eve
though this one is disabled)?

TIA

Cande

--
Cande
-----------------------------------------------------------------------
Candee's Profile: http://www.excelforum.com/member.php...info&userid=63
View this thread: http://www.excelforum.com/showthread.php?threadid=48358

Command button and msgbox's
 

Good evening Candee

AFAIK there is no check within VBA once a button is disabled, so yo
cannot check if it has been clicked. You could set up a label t
display a message such as this if a user logs on. Alternatively, yo
could just hide the button altogether if the user is a user (if you se
what I mean):

bOwner.Visible=False

So the user only sees what they are authorised to.

HTH

Dominic

--
dominic
-----------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...fo&userid=1893
View this thread: http://www.excelforum.com/showthread.php?threadid=48358

Command button and msgbox's
 

Candee,

Why not hide it (Visible property) rather than disable it?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Candee" wrote in
message ...

Hello everyone.

I have a worksheet that opens a simple userform when the file is opened
with 2 command buttons (bOwner and bUser). It checks to see who has
opened the file (Application.UserName), looks up the persons ID in a
list to determine if they are an owner or a user of the file. If the
person is not an owner, then the Owner button is disabled
(bOwner.Enabled = False). Each button has vba attached to hide or show
certain sheets in the file. I have disabled the "X" This part works
fine.

My question is, there are some people that will try to click the
"Owner" button even though it has been disabled. Can I have a message
box appear that tells them they have to choose the other button (even
though this one is disabled)?

TIA

Candee


--
Candee
------------------------------------------------------------------------
Candee's Profile:
http://www.excelforum.com/member.php...nfo&userid=638
View this thread: http://www.excelforum.com/showthread...hreadid=483581

Command button and msgbox's
 

Thanks for your responses dominicb and Bob.

If I were to hide it using the visible property, would th
worksheet-beforeclose be the correct place to unhide it so that whe
the file is opened the next time, it would be there? Or should I jus
change the vb to be that if the username is an owner that butto
appears or user just that button shows?

Can this coding be done so that I don't even need to have the userfor
at all? Just check the name when the workbook opens and show/hid
sheets based on that?

I appreciate all your help and comments,
Cande

--
Cande
-----------------------------------------------------------------------
Candee's Profile: http://www.excelforum.com/member.php...info&userid=63
View this thread: http://www.excelforum.com/showthread.php?threadid=48358

Command button and msgbox's
 

Easiest thing is to change the existing code from enabling/disabling to
hiding/unhiding.

You could add buttons to a worksheet, there are two types - forms toolbar
buttons and control toolbox buttons to choose from.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Candee" wrote in
message ...

Thanks for your responses dominicb and Bob.

If I were to hide it using the visible property, would the
worksheet-beforeclose be the correct place to unhide it so that when
the file is opened the next time, it would be there? Or should I just
change the vb to be that if the username is an owner that button
appears or user just that button shows?

Can this coding be done so that I don't even need to have the userform
at all? Just check the name when the workbook opens and show/hide
sheets based on that?

I appreciate all your help and comments,
Candee


--
Candee
------------------------------------------------------------------------
Candee's Profile:
http://www.excelforum.com/member.php...nfo&userid=638
View this thread: http://www.excelforum.com/showthread...hreadid=483581

Command button and msgbox's
 

Thanks so much! I'm going to stick to easy, that always works bette
for me. Have a great day all

--
Cande
-----------------------------------------------------------------------
Candee's Profile: http://www.excelforum.com/member.php...info&userid=63
View this thread: http://www.excelforum.com/showthread.php?threadid=48358

Command button and msgbox's
 

Here is a little trick:
make two command buttons. The first one, CommandButton1, will "hide" behind
CommandButton2 so make it the same size and if needed use Send to Back to
make sure 1 is behind 2.

When 2 (in front) is enabled, it will detect the button press, not 1, so it
functions normally as long as it is enabled. But when 2 is disabled, button
1 picks up the button press and you could display a messagebox in the
CommandButton1_Click() procedure. The user never sees button 1 but it is
active.
--
- K Dales


"Candee" wrote:


Hello everyone.

I have a worksheet that opens a simple userform when the file is opened
with 2 command buttons (bOwner and bUser). It checks to see who has
opened the file (Application.UserName), looks up the persons ID in a
list to determine if they are an owner or a user of the file. If the
person is not an owner, then the Owner button is disabled
(bOwner.Enabled = False). Each button has vba attached to hide or show
certain sheets in the file. I have disabled the "X" This part works
fine.

My question is, there are some people that will try to click the
"Owner" button even though it has been disabled. Can I have a message
box appear that tells them they have to choose the other button (even
though this one is disabled)?

TIA

Candee


--
Candee
------------------------------------------------------------------------
Candee's Profile: http://www.excelforum.com/member.php...nfo&userid=638
View this thread: http://www.excelforum.com/showthread...hreadid=483581

Command button and msgbox's
 

You could add code to the button to check (don't disable it).

if whateveryoucheckedtodetermineowner = false then
msgbox "You're not an owner"
exit sub
end if

'your real code here.

If IsError(Application.Match _
(Application.UserName, Worksheets("Hiddennamehere").Range("a:a"), 0)) Then
MsgBox "You're not an owner"
Exit Sub
End If

Candee wrote:

Hello everyone.

I have a worksheet that opens a simple userform when the file is opened
with 2 command buttons (bOwner and bUser). It checks to see who has
opened the file (Application.UserName), looks up the persons ID in a
list to determine if they are an owner or a user of the file. If the
person is not an owner, then the Owner button is disabled
(bOwner.Enabled = False). Each button has vba attached to hide or show
certain sheets in the file. I have disabled the "X" This part works
fine.

My question is, there are some people that will try to click the
"Owner" button even though it has been disabled. Can I have a message
box appear that tells them they have to choose the other button (even
though this one is disabled)?

TIA

Candee

--
Candee
------------------------------------------------------------------------
Candee's Profile: http://www.excelforum.com/member.php...nfo&userid=638
View this thread: http://www.excelforum.com/showthread...hreadid=483581

--

Dave Peterson