remove or disable the cancel button in an input pop up window

tomtat

New Member
Joined
Mar 25, 2014
Messages
10
i want to remove the cancel button to force a user to enter there name before they can access the workbook. if i can make the cancel button close the workbook with a error message that would be good.

The input box that asked for the week start date i would like to not be shown if there is already a date in the cell F4.


ANY HELP WOULD BE MUCH APPRECIATED
thank you :)

my current code is this

Private Sub Workbook_Open()
Worksheets("sheet1").Activate


Dim emptyrow As Long
emptyrow = WorksheetFunction.CountA(Sheets("sheet1").Range("B:B")) + 1
Cells(emptyrow, 2).Value = Date & " " & Time

Application.DisplayAlerts = False
Sheets("sheet1").Range("E3").Value = InputBox("Enter Your Name", "Authentication")
Range("F4").Value = InputBox("Enter the week start date (dd/Month/yyyy) Note- You must enter the month in text form", "Welcome to the York Cash Collection Workbook " & Sheets("sheet1").Range("E3"))

emptyrow = WorksheetFunction.CountA(Sheets("sheet1").Range("A:A")) + 1
Cells(emptyrow, 1).Value = (Sheets("Sheet1").Range("E3"))
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Maybe

Code:
x = InputBox("Enter Your Name", "Authentication")
If x = "False" Then
    MsgBox "Incorrect"
    ActiveWorkbook.Close False
End If
Sheets("sheet1").Range("E3").Value = x
 

tomtat

New Member
Joined
Mar 25, 2014
Messages
10
thanks Peter but this didn't work i was still able to press cancel and access the workbook without entering a name
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Then maybe

Code:
x = InputBox("Enter Your Name", "Authentication")
If x = "False" Or x = "" Then
    MsgBox "Incorrect"
    ActiveWorkbook.Close False
End If
Sheets("sheet1").Range("E3").Value = x
 

tomtat

New Member
Joined
Mar 25, 2014
Messages
10

ADVERTISEMENT

thank you Peter this works perfect.

Do you know of a way to then only allow enter to the workbook if a series of correct names are put into the input window. say for example the names - John and Glen

Thanks
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Maybe like this

Code:
x = InputBox("Enter Your Name", "Authentication")
If x = "False" Or x = "" Then
    MsgBox "Incorrect: you must enter a name"
    ActiveWorkbook.Close False
End If
If x <> "John" And x <> "Glen" Then
    MsgBox "Incorrect name"
    ActiveWorkbook.Close False
End If
Sheets("sheet1").Range("E3").Value = x
 

Watch MrExcel Video

Forum statistics

Threads
1,129,579
Messages
5,637,209
Members
416,961
Latest member
sigrid6940

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top