VBA Pop Up Form

[jdphilippe]

I have been working on a spreadsheet and I am no VBA expert so I always ask for some help.

I want to create a macro button that will bring up a Pop up form to allow users to enter a PO number in a text field with the execution button of "Search" and after hitting search it will unlock the protected sheet, paste the value in cell C12 and then protect the sheet again.

I am doing it because I have some crazy formulas that I don't want to be manipulated and I would want them to only paste in C12 without formatting but that's hard to ensure people do unless I add something like this. Some help in creating this VBA would be greatly appreciated.

 

[jdphilippe]

You are a godsend! I got an error message when I first did it but I dont know what I did but its working now!!! I'll PM you about the other things.

 

[Firesword]

Your welcome.
Okay you said you wanted to hide the tabs to stop users from messing with them. If you hide a tab you can still see it when you try to unhide it. So I tend to use veryhidden as only VBA code can unhide them. You'll also need to put a password on the VBA code too. To do this (from Alt+F11) click on Tools - VBAProject Properties, click on the tab "Protection", put a tick on Lock project for viewing and enter a password.
Add this code to the "ThisWorkbook"

Sub workbook_open()

    Application.ScreenUpdating = False
    Call CheckUserName
    Application.ScreenUpdating = True

End Sub
Sub Workbook_BeforeClose(Cancel As Boolean)

    Call Locksheets
    
End Sub

Then replace all the other code (say on Module 1) to this

Sub GetPONumber()

Dim lPOnumber As Long
Dim strInputBox As String
Dim obj As New DataObject

    Sheets(Sheet1.Name).Unprotect "YourPassWordHere"

    Sheets(Sheet1.Name).Cells.Locked = True
    strInputBox = InputBox(Prompt:="Please Enter Your PO Number")
        If strInputBox = "" Then
            If (Application.UserName = "User name1") Or _
               (Application.UserName = "User name2") Or _
               (Application.UserName = "User name3") Or _
               (Application.UserName = "User name4") Then
               Exit Sub
            Else
                Sheets(Sheet1.Name).Protect Password:="YourPassWordHere", DrawingObjects:=True, Contents:=True, Scenarios:=True
            Exit Sub
            End If
        Else
            Sheets(Sheet1.Name).Range("C12") = strInputBox
        End If
    
    obj.SetText Sheets(Sheet1.Name).Range("F20")
    obj.PutInClipboard
    If (Application.UserName = "User name1") Or _
       (Application.UserName = "User name2") Or _
       (Application.UserName = "User name3") Or _
       (Application.UserName = "User name4") Then
    Else
       Sheets(Sheet1.Name).Protect Password:="YourPassWordHere", DrawingObjects:=True, Contents:=True, Scenarios:=True
    End If
    
    'If you want to stay on the current sheet if run from a hot key do not include the next line
    'If you want to goto Sheet1 Cell C12 then include the next line.
    Application.Goto Reference:=Sheets(Sheet1.Name).Range("C12")

End Sub
Sub CheckUserName()
'Marco to lock the spreadsheet everyone bar the Named users.

Dim ws As Worksheet

    Application.ScreenUpdating = False
    
    For Each ws In ActiveWorkbook.Worksheets
        If (Application.UserName = "User name1") Or _
           (Application.UserName = "User name2") Or _
           (Application.UserName = "User name3") Or _
           (Application.UserName = "User name4") Then
           ws.Visible = xlSheetVisible
           ws.Unprotect Password:="YourPassWordHere"
        Else
            If ws.CodeName = "Sheet1" Then
               ws.Protect Password:="YourPassWordHere", DrawingObjects:=True, Contents:=True, Scenarios:=True
            Else
               'use Veryhidden as only VBA code can unhide these tabs
               ws.Visible = xlSheetVeryHidden
            End If
        End If
    Next
    
    Application.ScreenUpdating = True
    Application.Goto Reference:=Sheets(Sheet1.Name).Range("C12") 
End Sub
Sub Locksheets()
'Marco to lock all spreadsheets.

Dim ws As Worksheet

    Application.ScreenUpdating = False
    
    For Each ws In ActiveWorkbook.Worksheets
        If ws.CodeName = "Sheet1" Then
           ws.Protect Password:="YourPassWordHere", DrawingObjects:=True, Contents:=True, Scenarios:=True
        Else
           'use Veryhidden as only VBA code can unhide these tabs
           ws.Visible = xlSheetVeryHidden
        End If
    Next
    
    Application.ScreenUpdating = True
    Application.Goto Reference:=Sheets(Sheet1.Name).Range("C12") 

End Sub

If set the code to allow you multiply users full access (I've had to this for work ie My boss and the IT manager have full access) you can delete them as needed. You'll need to change at least "User name1" to your user name, everywhere this is listed, same goes for "YourPassWordHere". This code will then do the following:-
1) when the file is opened it will check for your user name and if you opened it, then the workbook will be unlocked and all sheets visible. Note I don't know what the rest of your workbook does, so test this as you may get a some errors.
2) If it's not you then everything is locked and with a password and all sheets are hidden except sheet 1.
3) When you close the file, all sheets are locked and hidden except sheet 1.

Let me know if you need anything else or if anything is not quite right.

 

[jdphilippe]

Ok so a couple of things I've noticed. I followed your instructions and the macros work if I run them from the macro window but not automatically when I close the file and open it. So for example when I open the locked file it doesn't autounlock and unhide all the sheets. I have to run the marco CheckUserName and then it will. Same for when I close the file as I believe the code is supposed to have it lock and hide sheets.

My macro that I assigned to hot key seems to no longer work on hotkey. When I go to assign it again, "options" is greyed out and I can't select it.

I think if you can tweak it a bit for that and let me know about the hotkey I can play around with it a bit more and try other users as well. Let me know. Thanks!

 

[jdphilippe]

Hey buddy, just checking in to see if you saw my previous comment on the thread. Thanks.

 

[Firesword]

Hi jdphilippe

Okay I hope the issue is the location that you've put these two subs:-
workbook_open()
Workbook_BeforeClose(Cancel As Boolean)

These will only auto run from one location which is as follows:-
On the VBA screen (Alt+F11), on the left had side you will see two folders which are normally expanded (open), one is called Microsoft Excel Objects, and one called Modules. The one called Modules is where you will post most of your main code, but not the ones you want to auto run.

The one called Microsoft Excel Objects will list all the sheets you have on your workbook it will list the codename and then the name that the user has called the sheet ie:-
Sheet1 (Open POs)
Sheet2 (Current stock)
and so on, the last one will be called ThisWorkbook, double click on ThisWorkbook, and past the code for the two subs in the window on the right.

If this works, and then you want to see how it will work for other users, just go to the place where your username is and remove a character. This will then treat you like a general user.

Not sure why the hot key is not working atm (it maybe due to the worksheet being protected).