Select row number and save variable

E

Eric Carolus

Board Regular
Joined
Sep 17, 2012
Messages
133
Office Version
  1. 2016
Platform
  1. Windows
Hi folks
''This this the code for the worksheet called Teachers
VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 On Error Resume Next
    If Target.Row >= 1 And Target.Row <= 2 Then

       MsgBox "You Cannot Delete the Header,called 'Teachers', OK?" _
    & vbCrLf _
    & vbCrLf _
    & "You are restricted from doing so!" _
    & vbCrLf _
    & vbCrLf _
    & ".................................................." _
     & vbCrLf _
    & vbCrLf _
    & "                    Thanks" _
    & vbCrLf _
    & vbCrLf _
    & vbCrLf _
    & vbCrLf _
    & " Author: [EMAIL]solutions.crow@gmail.com[/EMAIL]", vbInformation, "                            Restricted from Deletion"

        'MsgBox "Check"
        ActiveSheet.Protect Password:="CROW-RA1ND-RO2BY-AR3LE-RENN", DrawingObjects:=True, contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingRows:=False, AllowDeletingRows:=False

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Else
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Selection.EntireRow.Select

''1. WORKED LIKE A CHARM!!
NamesXX = Intersect(ActiveCell, Range("C3:C100")).Value
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'2. WORKED LIKE A CHARM!!
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'NamesXX = Intersect(ActiveCell, Range("Teachers")).Value

If NamesXX = "" Then
    Exit Sub
Else
Dim answer As String

'Request from user if he/she wish to remove the teacher
answer = MsgBox("You want to remove teacher" & "' " & NamesXX & "' " & "?" _
         & vbCrLf _
         & vbCrLf _
         & "Remember, you are about to remove this" _
         & vbCrLf _
         & vbCrLf _
         & "teacher permanently from the teacher list!" _
         & vbCrLf _
         & vbCrLf _
         & "........................................................................" _
         & vbCrLf _
         & vbCrLf _
         & "In ADDITION,the teacher will also be" _
         & vbCrLf _
         & vbCrLf _
         & "removed from the allocation!", vbCritical + vbOKCancel, "                      Permanent removal of teacher!")

'Display the name of the chosen teacher
MsgBox NamesXX
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If answer = vbOK Then

'Remove the name of the teacher from the list of teachers
    Intersect(ActiveCell, Range("C3:C100")).ClearContents

MsgBox NamesXX

'Do not remove the teacher if the user Cancel the action
ElseIf answer = vbCancel Then
'    Exit Sub
     MsgBox " You chose not to remove the teacher!"
     NamesXX = ""
End If
End If
        ActiveSheet.Unprotect Password:="CROW-RA1ND-RO2BY-AR3LE-RENN"

End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 End Sub
''

''This is the worksheet called Uneven N Days
Private Sub Worksheet_Activate()
MsgBox NamesXX

Dim TeacherNames As Range
Dim cell  As Range
''
Set TeacherNames = Worksheets("Uneven N Days").Range("C5:AV35")

If NamesXX = "" Then
    Exit Sub

Else
For Each cell In TeacherNames
    If cell.Value = NamesXX Then
        cell.ClearContents
        cell.Offset(0, 1).ClearContents
    End If
Next
End If

End Sub

I have a workbook with two sheets only, one called Teachers and the other called Uneven N Days.
On the sheet called Teachers, I have a dynamic range of teacher names. If I simply select a teacher name (A CELL), the user will be warned that that particular teacher may be deleted BUT
then the user have the choice to delete the teacher or not.
Should the user wish to delete the teacher, the teacher will then be removed (from the list of teacher names).
At the same time THAT teacher's name will be saved in a variable called NamesXX.

When you activate the sheet called Uneven N Days, a Msgbox will show the DELETED teacher that had been deleted from the sheet called Teachers.
That teacher will the be deleted from the sheet called Uneven N Days, WITH the number next to that teacher.

My problem: I want the same to happen when I SELECT THE ROW NUMBER ON THE SHEET CALLED "Teachers".
Its a matter of selecting a cell vs selecting a row number on the sheet called "Teachers"
I can use all help.
I thank you in advance.

Crow
 
Last edited by a moderator:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
How about
VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Rng As Range
    
    If Target.Rows.Count > 1 Then Exit Sub
    
    If Target.Row >= 1 And Target.Row <= 2 Then

       MsgBox "You Cannot Delete the Header,called 'Teachers', OK?" _
    & vbCrLf _
    & vbCrLf _
    & "You are restricted from doing so!" _
    & vbCrLf _
    & vbCrLf _
    & ".................................................." _
     & vbCrLf _
    & vbCrLf _
    & "                    Thanks" _
    & vbCrLf _
    & vbCrLf _
    & vbCrLf _
    & vbCrLf _
    & " Author: [EMAIL]solutions.crow@gmail.com[/EMAIL]", vbInformation, "                            Restricted from Deletion"

        'MsgBox "Check"
        ActiveSheet.Protect Password:="CROW-RA1ND-RO2BY-AR3LE-RENN", DrawingObjects:=True, contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingRows:=False, AllowDeletingRows:=False

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Exit Sub
    End If
    Set Rng = Intersect(Target, Range("Teachers"))
    If Rng Is Nothing Then Exit Sub
    
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Selection.EntireRow.Select

''1. WORKED LIKE A CHARM!!
NamesXX = Rng.Value
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'2. WORKED LIKE A CHARM!!
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'NamesXX = Intersect(ActiveCell, Range("Teachers")).Value

If NamesXX = "" Then Exit Sub
Dim answer As String

'Request from user if he/she wish to remove the teacher
answer = MsgBox("You want to remove teacher" & "' " & NamesXX & "' " & "?" _
         & vbCrLf _
         & vbCrLf _
         & "Remember, you are about to remove this" _
         & vbCrLf _
         & vbCrLf _
         & "teacher permanently from the teacher list!" _
         & vbCrLf _
         & vbCrLf _
         & "........................................................................" _
         & vbCrLf _
         & vbCrLf _
         & "In ADDITION,the teacher will also be" _
         & vbCrLf _
         & vbCrLf _
         & "removed from the allocation!", vbCritical + vbOKCancel, "                      Permanent removal of teacher!")

'Display the name of the chosen teacher
MsgBox NamesXX
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If answer = vbOK Then

'Remove the name of the teacher from the list of teachers
    Rng.ClearContents

MsgBox NamesXX

'Do not remove the teacher if the user Cancel the action
ElseIf answer = vbCancel Then
'    Exit Sub
     MsgBox " You chose not to remove the teacher!"
     NamesXX = ""
End If
        ActiveSheet.Unprotect Password:="CROW-RA1ND-RO2BY-AR3LE-RENN"

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 End Sub
 
Upvote 0
Solution
Fluff said:
How about
VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Rng As Range
   
    If Target.Rows.Count > 1 Then Exit Sub
   
    If Target.Row >= 1 And Target.Row <= 2 Then

       MsgBox "You Cannot Delete the Header,called 'Teachers', OK?" _
    & vbCrLf _
    & vbCrLf _
    & "You are restricted from doing so!" _
    & vbCrLf _
    & vbCrLf _
    & ".................................................." _
     & vbCrLf _
    & vbCrLf _
    & "                    Thanks" _
    & vbCrLf _
    & vbCrLf _
    & vbCrLf _
    & vbCrLf _
    & " Author: [EMAIL]solutions.crow@gmail.com[/EMAIL]", vbInformation, "                            Restricted from Deletion"

        'MsgBox "Check"
        ActiveSheet.Protect Password:="CROW-RA1ND-RO2BY-AR3LE-RENN", DrawingObjects:=True, contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingRows:=False, AllowDeletingRows:=False

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Exit Sub
    End If
    Set Rng = Intersect(Target, Range("Teachers"))
    If Rng Is Nothing Then Exit Sub
   
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Selection.EntireRow.Select

''1. WORKED LIKE A CHARM!!
NamesXX = Rng.Value
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'2. WORKED LIKE A CHARM!!
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'NamesXX = Intersect(ActiveCell, Range("Teachers")).Value

If NamesXX = "" Then Exit Sub
Dim answer As String

'Request from user if he/she wish to remove the teacher
answer = MsgBox("You want to remove teacher" & "' " & NamesXX & "' " & "?" _
         & vbCrLf _
         & vbCrLf _
         & "Remember, you are about to remove this" _
         & vbCrLf _
         & vbCrLf _
         & "teacher permanently from the teacher list!" _
         & vbCrLf _
         & vbCrLf _
         & "........................................................................" _
         & vbCrLf _
         & vbCrLf _
         & "In ADDITION,the teacher will also be" _
         & vbCrLf _
         & vbCrLf _
         & "removed from the allocation!", vbCritical + vbOKCancel, "                      Permanent removal of teacher!")

'Display the name of the chosen teacher
MsgBox NamesXX
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If answer = vbOK Then

'Remove the name of the teacher from the list of teachers
    Rng.ClearContents

MsgBox NamesXX

'Do not remove the teacher if the user Cancel the action
ElseIf answer = vbCancel Then
'    Exit Sub
     MsgBox " You chose not to remove the teacher!"
     NamesXX = ""
End If
        ActiveSheet.Unprotect Password:="CROW-RA1ND-RO2BY-AR3LE-RENN"

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 End Sub
Click to expand...
Hi Fluff

Its works like a charm!!
Thanks a lot!!
Crow
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
Fluff said:
You're welcome & thanks for the feedback.
Click to expand...
Hi Fluff

The code you helped me with works just fine.

Just a follow up question.

However, the teacher to be removed, captured in NamesXX now needs to be removed from THREE

sheets called Uneven N Days, Even Days & 5 Day Timetable, instead of a single sheet.

My problem is: How do I get rid of the VALUE of NamesXX AFTER the teacher had been removed from the three sheets mentioned above?

For example, if you selected and removed a teacher from the sheet called Teachers, then activate each of the three sheets mentioned above (upon which the selected teacher will be removed) AND THEN go back to the three sheets again, the NamesXX variable still exist. (I tested it with Msgbox on the Even N Days).

I have tried to use the Deactivate event to say NamesXX = “” of NamesXX = vbNullString on one sheet, but then once I activate ANY of the two 2 other sheets, the selected teacher (NamesXX) wont be deleted from the other two sheets.

Any suggestions?

Thanks

Crow
 
Upvote 0
You could create another global variable to count how many sheets the teacher has been removed & when it equals 3 clear both variables.
 
Upvote 0
Hi Fluff

The following is a Sub that I call every time one of the 4 sheets is activted.

VBA Code: 
Sub counting()
Dim wb As Workbook
Dim ws As Worksheet
'Dim counter As Integer
'Dim SecondCounter As Integer
Set wb = ThisWorkbook

If counter = 4 Then
    counter = 0
    NamesXX = ""
    Exit Sub

Else
Set ws = wb.Worksheets("Uneven N Days")
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim ws2 As Worksheet
Set wb = ThisWorkbook

Set ws2 = wb.Worksheets("Even N Days")
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim ws3 As Worksheet
Set wb = ThisWorkbook

Set ws3 = wb.Worksheets("5 Day Timetable1")
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim ws4 As Worksheet
Set wb = ThisWorkbook

Set ws4 = wb.Worksheets("5 Day Timetable2")

If ws.Visible Then
    counter = counter + 1
'    MsgBox counter
ElseIf ws2.Visible Then
    counter = counter + 1
'    MsgBox counter
ElseIf ws3.Visible Then
    counter = counter + 1
'    MsgBox counter
ElseIf ws4.Visible Then
    counter = counter + 1

End If
End If
MsgBox counter
End Sub
 
Upvote 0
Hi Fluff

The problem is that I have to ensure that:
a). ONLY those 4 sheets are activated (does not matter in which order),
b). somehow once a sheet had been activated, that sheet cannot be activated before the
other 3 sheets had been activated (at which time the counter is reset to 0 and NamesXX =""
I need some help here please or point me in direction.

Thanks

Crow
 
Upvote 0
Must admit I am at aloss to understand exactly what you are trying to do or why. But if you simply want to delete the teachers name from multiple sheets, why not just do it in the original sub rather than relying on people to activate the right sheets in the right order.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
VBA Msgbox with clickable URL
Replies
1
Views
88
dreid1011
dreid1011
JenniferMurphy
  • Question Question
Comments on macro to help solve the problem of Excel changing column widths
2
Replies
11
Views
337
JenniferMurphy
JenniferMurphy
H
  • Question Question
Advanced Multi VBA - Creating multiple temporary files and attach them to one email on outlook
Replies
2
Views
263
Hayf13
H
L
  • Locked
  • Question Question
How to stop VBA from creating pdf from drop-down list if the value is empty?
Replies
1
Views
303
Fluff
Fluff
C
  • Question Question
Combining rows 3 through 7 into row 2, one row takes a painfully long time with this code. Why?
Replies
2
Views
247
jolivanes
jolivanes

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

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
Back
Top