Command button and msgbox's

  • Thread starter Thread starter Candee
  • Start date Start date
C

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
 
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
 
Candee,

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

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
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
 
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)
 
Thanks so much! I'm going to stick to easy, that always works bette
for me. Have a great day all
 
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.
 
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
 
Back
Top