Run VBA-code after Private Sub Worksheet_SelectionChange(ByVal Target As Range)

[KlausW]

Hi,
can anyone help me get this VBA code to run when a code that starts with Private Sub Worksheet_SelectionChange(ByVal Target As Range) is run?

The vba codes are in the same sheet.

All help would be appreciated.

Best regards Klaus W

Sub macro1()

Static Passed As Boolean

Dim P$

If Target.Address <> "$A$42" Or Passed Then Exit Sub

Do

P = InputBox(vbLf & vbLf & "Indtast adgangskode for at ændre denne celle:", "Begrænset adgang")

If P = "" Then Target(1, 2).Select: Exit Sub

Loop Until P = "XXXXXXX"

Passed = True

End Sub

 

[Micron]

put a line in the change event code that calls your posted code.

'some code lines here
macro1
'more code lines

You can use Call macro1 but you don't usually need the Call statement. If you disable events before the call, macro1 won't run.

EDIT - as written, this will never be True: Or Passed Then Exit Sub
EDIT 2 - The second sub will have no idea what Target is because that only exists in the sheet event sub. You'd need to pass the address to the 2nd sub. Why not make it easier and do it all in the sheet event sub?

 

[KlausW]

Hi Micron,
Thanks I'm not quite sure where to put it. This is the VBA code I want the other one to run from.
Best Regards Klaus W

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set Target = Range("k12")

If Target = "" Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Target, 31)

Exit Sub
     
End Sub

 

[CSmith]

Hi Micron,
Thanks I'm not quite sure where to put it. This is the VBA code I want the other one to run from.
Best Regards Klaus W

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set Target = Range("k12")

If Target = "" Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Target, 31)

Exit Sub
    
End Sub

Klaus,
That code is run every time a new range is selected and is entered at the worksheet versus macro or module level.

 

[Micron]

Klaus, best to explain what you want to have happen. As you have it, every time you select a new cell you would get a prompt for a password. Is that really what you want? Plus, there is no 'connection' between the 2 codes you posted aside from the fact that they're in the same sheet module. If you call the 2nd sub and input the proper password, in the 1st sub nothing will happen as a result. Also, would you not want a maximum number of attempts? As you have it, the user would be locked into an endless cycle of prompts until the correct pw is provided. Or they likely would have to kill Excel and lose their work. A certain number of attempts is usually what designers code for. After that, things usually just stop.

 

[KlausW]

Could it be like this? When VBA code 2 to run after VBA code 1.
Klaus W

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set Target = Range("k12")

If Target = "" Then Exit Sub

Application.ActiveSheet.Name = VBA.Left(Target, 31)

macro1

Exit Sub

End Sub


Sub macro1()

Static Passed As Boolean

Dim P$

If Target.Address <> "$A$42" Or Passed Then Exit Sub

Do

P = InputBox(vbLf & vbLf & "Indtast adgangskode for at ændre denne celle:", "Begrænset adgang")

If P = "" Then Target(1, 2).Select: Exit Sub

Loop Until P = "1234"

Passed = True

End Sub

 

[Tupe77]

If i understand you asked this, but I'm not sure if that's what you want?

TestRNG for testing if Range("k12") is ""
Sub macro1(Target as range) calls your subroutine macro1 and passes it the Target value of the Worksheet_SelectionChange event.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim TestRNG As Range
Set TestRNG = Range("k12")

If TestRNG = "" Then Exit Sub
' Rename Worksheet if sheet name is not VBA.Left(TestRNG, 31)
If Application.ActiveSheet.Name <> VBA.Left(TestRNG, 31) Then
    Application.ActiveSheet.Name = VBA.Left(TestRNG, 31)
End If
'Calling the macro1 subroutine using the Target value as a parameter.
Call macro1(Target)

Exit Sub
End Sub

Private Sub macro1(Target As Range)
Debug.Print "m1 start"
Static Passed As Boolean
Dim P

If Target.Address <> "$A$42" Or Passed Then Exit Sub

Do
P = InputBox(vbLf & vbLf & "Indtast adgangskode for at ændre denne celle:", "Begrænset adgang")

If P = "" Then Target(1, 2).Select: Exit Sub

Loop Until P = "XXXXXXX"

Passed = True

End Sub

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

 

[Micron]

I realize the last posted code is from the OP, but still, some of that doesn't make sense to me.
- why set an object variable for k12 if all you want to know is if it contains anything? And if it does, you want to be prompted for a password every time you select any other cell (assuming the rest works)? That's odd.
- exit sub isn't needed just before an End Sub
- re: Or Passed Then Exit Sub - as I said before, Passed will never be true so that part makes no sense
- macro1 has no idea what Target.Address is so code will fail
- if user forgot the password they will be stuck in a loop (if they get that far)
- if Passed is ever set to true in that line, immediately thereafter it is dumped when the sub ends and nothing is done with the value
EDIT - after posting I noticed that Target is passed to macro1 in the newer code version
BTW, the application.activesheet.name part fails for me

 

[Micron]

I don't understand what is wanted with respect to the activesheet.name part so I left it out. This is more like how I'd probably do it

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer
Dim Pwd As Variant
Dim msg As String


If Not Range("K12") = "" And Target.Address = "$A$42" Then
    msg = vbLf & vbLf & "Indtast adgangskode for at ændre denne celle:"
    Do Until i = 2 'skift 2 til det antal forsøg, du ønsker(change 2 to the number of attempts you want)
        Pwd = Application.InputBox(msg, "Begrænset adgang", Type:=2)
        If Pwd = False Then Exit Sub
        If Pwd = "1234" Then
            MsgBox "OK, så hvad skal man gøre nu?"
            Exit Sub
        End If
        i = i + 1
    Loop
End If

End Sub

As far as I know, default input box is VBA.InputBox which cannot easily determine if user clicks Cancel. Application.InputBox can return False if canceled. That's why Pwd was declared as a variant.

 

[KlausW]

If i understand you asked this, but I'm not sure if that's what you want?

TestRNG for testing if Range("k12") is ""
Sub macro1(Target as range) calls your subroutine macro1 and passes it the Target value of the Worksheet_SelectionChange event.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim TestRNG As Range
Set TestRNG = Range("k12")

If TestRNG = "" Then Exit Sub
' Rename Worksheet if sheet name is not VBA.Left(TestRNG, 31)
If Application.ActiveSheet.Name <> VBA.Left(TestRNG, 31) Then
    Application.ActiveSheet.Name = VBA.Left(TestRNG, 31)
End If
'Calling the macro1 subroutine using the Target value as a parameter.
Call macro1(Target)

Exit Sub
End Sub

Private Sub macro1(Target As Range)
Debug.Print "m1 start"
Static Passed As Boolean
Dim P

If Target.Address <> "$A$42" Or Passed Then Exit Sub

Do
P = InputBox(vbLf & vbLf & "Indtast adgangskode for at ændre denne celle:", "Begrænset adgang")

If P = "" Then Target(1, 2).Select: Exit Sub

Loop Until P = "XXXXXXX"

Passed = True

End Sub

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

Hi Tube 77, as it should be, thank you. And to everyone else who has come up with ideas to solve my challenge. Thank you too. Good day to everyone from Klaus W in Denmark