VBA Code to Calculate Formula in Cells

[Helen87]

So, I'm a complete novice at this VBA but have managed to create a code which works. However, I want to add another level to it.Basically, I've got a sheet with a form combo box (named in the VBA code as ComboBox) on, which when the selection changes, prompts for a password to be entered. It gives two chance for the password to be entered, then asks the user to try again. When the drop down changes, there's a Vlookup formula in cells F7:F9, which looks up the value in cell E1 (the reference cell for the combo box), in the array on Sheet 2, A2:D7. What I really want is for this Vlookup formula only to be calculated if the password has been entered correctly, otherwise I want them to be blank.The code I have at the moment is:Sub ComboBox_Change() With ThisWorkbook.Sheets("Sheet1").Shapes("ComboBox").ControlFormat Select Case .List(.Value) Case "J Smith": Password1 Case "K Elliot": Password2 Case "T Harrison": Password3 Case "S Jones": Password4 Case "M Barker": Password5 End Select End WithEnd SubSub Password1()pword = Application.InputBox("Please enter your password", "Password Required")If pword = "Test1" Then Worksheets("Sheet1").Visible = TrueElsepword = Application.InputBox("Please enter your password", "Password Required")If pword = "Test1" Then Worksheets("Sheet1").Visible = TrueElse MsgBox "You have entered your password incorrectly twice. Please re-select your name and try again"End IfEnd IfEnd SubSub Password2()pword = Application.InputBox("Please enter your password", "Password Required")If pword = "Test2" Then Worksheets("Sheet1").Visible = TrueElsepword = Application.InputBox("Please enter your password", "Password Required")If pword = "Test2" Then Worksheets("Sheet1").Visible = TrueElse MsgBox "You have entered your password incorrectly twice. Please re-select your name and try again"End IfEnd IfEnd SubSub Password3()pword = Application.InputBox("Please enter your password", "Password Required")If pword = "Test3" Then Worksheets("Sheet1").Visible = TrueElsepword = Application.InputBox("Please enter your password", "Password Required")If pword = "Test3" Then Worksheets("Sheet1").Visible = TrueElse MsgBox "You have entered your password incorrectly twice. Please re-select your name and try again"End IfEnd IfEnd SubSub Password4()pword = Application.InputBox("Please enter your password", "Password Required")If pword = "Test4" Then Worksheets("Sheet1").Visible = TrueElsepword = Application.InputBox("Please enter your password", "Password Required")If pword = "Test4" Then Worksheets("Sheet1").Visible = TrueElse MsgBox "You have entered your password incorrectly twice. Please re-select your name and try again"End IfEnd IfEnd SubSub Password5()pword = Application.InputBox("Please enter your password", "Password Required")If pword = "Test5" Then Worksheets("Sheet1").Visible = TrueElsepword = Application.InputBox("Please enter your password", "Password Required")If pword = "Test5" Then Worksheets("Sheet1").Visible = TrueElse MsgBox "You have entered your password incorrectly twice. Please re-select your name and try again"End IfEnd IfEnd SubAnyone have any ideas? Is it even possible?? Am I making sence??!Thanks in advance!

 

[Joe4]

Welcome to the Board!

Your post is pretty much unreadable because all the VBA code is wrapped into one long string.
Please try posting it again, using Code Tags.
See: https://www.mrexcel.com/forum/board-announcements/515787-how-post-your-vba-code.html