Command button and msgbox's

[Candee]

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

 

[dominicb]

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

 

[Bob Phillips]

Candee,

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

--

HTH

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

 

[Candee]

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

 

[Bob Phillips]

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]

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

 

[Guest]

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.

 

[Dave Peterson]

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