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.

 

[Firesword]

Hi jdphilippe

I'm not sure that this is need, all cells by default are flagged as protected, but just to make sure run this code to mark all cells except cell C12 as protected.

Sub Lockallcells()

'The flag on all cells in set to lock by default but just in case run this as a one off to make sure.
ActiveSheet.Cells.Locked = True
'Turn the flag off for cell C12
ActiveSheet.Range("C12").Locked = False

End Sub

Then protect the worksheet, then the users can only access cell C12 without the need to keep unlocking/locking the worksheet.
or use this code to get the user input enter it into cell C12 and then re-protect the worksheet

Sub GetPONumber()

Dim lPOnumber As Long

    ActiveSheet.Unprotect "YourPassWordHere"

Repeat:
    Range("C12") = InputBox(Prompt:="Please Enter Your PO Number")
    If Range("C12") = "" Then GoTo Repeat
    ActiveSheet.Protect Password:="YourPassWordHere", DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

Or if I've misunderstood your request then please let me know.

 

[jdphilippe]

Then protect the worksheet, then the users can only access cell C12 without the need to keep unlocking/locking the worksheet.
or use this code to get the user input enter it into cell C12 and then re-protect the worksheet

Sub GetPONumber()

Dim lPOnumber As Long

    ActiveSheet.Unprotect "YourPassWordHere"

Repeat:
    Range("C12") = InputBox(Prompt:="Please Enter Your PO Number")
    If Range("C12") = "" Then GoTo Repeat
    ActiveSheet.Protect Password:="YourPassWordHere", DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

I used this and it workes perfectly! Yes I think you understood what I was trying to do. I need to protect the sheet because I do not want the users to be able to manually enter the PO number in C12 so this works for what I need to.

However once I trigger the VBA I want to be able to hit cancel and it goes back to the lock sheet without changing the value in C12. How do we have that happen?

 

[jdphilippe]

Also is there a way to assign a hotkey to execute the VBA as well as clicking the button?

 

[jdphilippe]

Also also lol I cant seem to change the Buttom text or fill color. When I go to the properties of the button I can change the font, size and color but thats it. I am using Excel Mac 2016. I've tried to look this up and seen that since I dont have th design mode in the ribbon its difficult to do and might have to make a VBA for it. I am looking to give a button a border and change the button text to Search instead of Button 1. Even if I can only change the text that is most important.

 

[Firesword]

Yes to all.
I have a few questions though.
1) Do you log in with a unique user name and password to windows?
2) Is there just one sheet to protect or a few/lots?
3) I prefer (just me) to not use buttons but an active cell so once selected it runs the code, I'll give options to both.
4) It's best not to include any hot key or macro to unlock the worksheet as anyone can then run it but there are ways around that.

If 1) = true run this code but do not post it here. It will list your username that we can then use to protect your files.

Sub GetMyUserName()
range("C12") = Application.UserName
End sub

 

[Firesword]

Button

There is not much scope to change how a button looks, but a macro can be assigned to any shape or picture. Click on Insert - (Shapes or Pictures). Pick any shape/picture and you'll get a lot of format options. To assign a marco to this shape or picture (it's called an object) right click it select "Assign Macro...".

 

[Firesword]

Assgin hot key to a macro

Click on the developer tab, then Macros select the marco you want to assgin a hot key to and click options enter the key you want. FYI most keys already have a function assigned to them but J and M are free.

 

[Firesword]

For this to work you will need to add Microsoft Forms 2.0 Object Library, I always have trouble finding this. Best way to add it is:-

With the VBA screen open (Alt+F11), right click on Modules and insert a userform. You can delete this straight away or leave it makes no difference.

What this will do is insert the Microsoft Forms 2.0 Object Library for you. This can be viewed by clicking on Tools - References in the VBA screen (Alt+F11)

Then change your code to the following add or remove the last line as needed.

Sub GetPONumber()

Dim lPOnumber As Long
Dim obj As New DataObject

    Sheets(Sheet1.Name).Unprotect "YourPassWordHere"

Repeat:
    Sheets(Sheet1.Name).Cells.Locked = True
    Sheets(Sheet1.Name).Range("C12") = InputBox(Prompt:="Please Enter Your PO Number")
    If Sheets(Sheet1.Name).Range("C12") = "" Then GoTo Repeat
    obj.SetText Sheets(Sheet1.Name).Range("F20")
    obj.PutInClipboard
    Sheets(Sheet1.Name).Protect Password:="YourPassWordHere", DrawingObjects:=True, Contents:=True, Scenarios:=True
    '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

This line will copy Cell F20 on sheet1 into the clipboard change as needed.

    obj.SetText Sheets(Sheet1.Name).Range("F20")

Also for the button, if you right click on the button then left click on the text you can edit the description, or as above insert a shape and then assign a hot key.

Let me know if you want the option to have the spreadsheet unlock based on your username.

 

[Firesword]

Oops I missed the part where you mention about hitting cancel. Use this code for that:-

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
            Sheets(Sheet1.Name).Protect Password:="YourPassWordHere", DrawingObjects:=True, Contents:=True, Scenarios:=True
            Exit Sub
        Else
            Sheets(Sheet1.Name).Range("C12") = strInputBox
        End If
    
    obj.SetText Sheets(Sheet1.Name).Range("F20")
    obj.PutInClipboard
    Sheets(Sheet1.Name).Protect Password:="YourPassWordHere", DrawingObjects:=True, Contents:=True, Scenarios:=True
    '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