When I click from Tab1 to Tab2 I need code to run which will clear the
contents of the cell in column A of the current row if the cell in column B
is empty. I already use the code rng = Cells(ActiveCell.Row, 1) to
determine the Active row.

I assume I can place the code in a MultiPage1_Exit sub routine.

Hi Patrick,

The following code is the only way I have been able to reference the active
cell on a sheet which I deactivate. If anyone else reads this and has other
options then I am certainly interested.

My method involves the following:-

Saving the name of the new sheet.
Re-activating the old sheet.
Assign the active cell to a variable.
Turn off events .
Re-select the new required worksheet.

Also your code 'rng = Cells(ActiveCell.Row, 1)'. If you want to assign a
range to a variable then it should have Set in front of it:-
Set rng = Cells(ActiveCell.Row, 1)

To put the following code in the correct place, right click on the worksheet
name (tab) that is being deactivated and select View code. The VBA editor
will open in the right place.

Private Sub Worksheet_Deactivate()
Dim Rng As Range
Dim strNewSht As String

'Following line is for info only confirming the the new sheet
'is the active sheet before any of the code is processed.
MsgBox "Active Sheet is: " & ActiveSheet.Name

'Save the new sheet name
strNewSht = ActiveSheet.Name

'Select the original sheet
Sheets("Sheet1").Select

'Assign required range to a variable

Set Rng = Cells(ActiveCell.Row, 1)

'Disable events and re-select the new sheet
Application.EnableEvents = False
Sheets(strNewSht).Select
'Re-enable events
Application.EnableEvents = True

'Can now use the range as a reference to
'manipulate dat on the deactivated sheet
If Rng.Offset(0, 1) = "" Then
Rng.Clear
End If

End Sub

Regards,

OssieMac

Thanks for your reply. I was able to use part of what you suggested If
Rng.Offset(0, 1) = "" Then

Rng.Clear
It works because the worksheet is still active.

Below is the subroutine I came up with, but I must be missing something. I
thought "MultiPage1_Exit" would only apply to page 1 of the 6 pages. But the
code runs every time I switch from one page to the next. How can I make this
run only when I leave Page1?



Private Sub MultiPage1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Set Rng = Cells(ActiveCell.Row, 1)
If Rng.Offset(0, 1) = "" Then
Rng.Clear
End If

End Sub


"OssieMac" wrote in message
...
Hi Patrick,

The following code is the only way I have been able to reference the
active
cell on a sheet which I deactivate. If anyone else reads this and has
other
options then I am certainly interested.

My method involves the following:-

Saving the name of the new sheet.
Re-activating the old sheet.
Assign the active cell to a variable.
Turn off events .
Re-select the new required worksheet.

Also your code 'rng = Cells(ActiveCell.Row, 1)'. If you want to assign a
range to a variable then it should have Set in front of it:-
Set rng = Cells(ActiveCell.Row, 1)

To put the following code in the correct place, right click on the
worksheet
name (tab) that is being deactivated and select View code. The VBA editor
will open in the right place.

Private Sub Worksheet_Deactivate()
Dim Rng As Range
Dim strNewSht As String

'Following line is for info only confirming the the new sheet
'is the active sheet before any of the code is processed.
MsgBox "Active Sheet is: " & ActiveSheet.Name

'Save the new sheet name
strNewSht = ActiveSheet.Name

'Select the original sheet
Sheets("Sheet1").Select

'Assign required range to a variable

Set Rng = Cells(ActiveCell.Row, 1)

'Disable events and re-select the new sheet
Application.EnableEvents = False
Sheets(strNewSht).Select
'Re-enable events
Application.EnableEvents = True

'Can now use the range as a reference to
'manipulate dat on the deactivated sheet
If Rng.Offset(0, 1) = "" Then
Rng.Clear
End If

End Sub

Regards,

OssieMac

Patrick,

The "Page1_Exit" that you're seeing is referring to MultiPage_1, which is
the name of the whole MP control.

This is not something I've much experience with, but I believe you need to
use the MP's Change event and a public variable to hold the value of the
current page. Here's a simple example:

Option Explicit
Dim CurrentPage As Long
Private Sub MultiPage1_Change()
If CurrentPage + 1 = 1 then 'page index starts at 0
MsgBox "Now leaving page 1"
EndIf
CurrentPage = Me.MultiPage1.Value ' set it to the new "active" page
End Sub

hth,

Doug

"Patrick C. Simonds" wrote in message
...
Thanks for your reply. I was able to use part of what you suggested If
Rng.Offset(0, 1) = "" Then

Rng.Clear
It works because the worksheet is still active.

Below is the subroutine I came up with, but I must be missing something. I
thought "MultiPage1_Exit" would only apply to page 1 of the 6 pages. But
the code runs every time I switch from one page to the next. How can I
make this run only when I leave Page1?



Private Sub MultiPage1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Set Rng = Cells(ActiveCell.Row, 1)
If Rng.Offset(0, 1) = "" Then
Rng.Clear
End If

End Sub


"OssieMac" wrote in message
...
Hi Patrick,

The following code is the only way I have been able to reference the
active
cell on a sheet which I deactivate. If anyone else reads this and has
other
options then I am certainly interested.

My method involves the following:-

Saving the name of the new sheet.
Re-activating the old sheet.
Assign the active cell to a variable.
Turn off events .
Re-select the new required worksheet.

Also your code 'rng = Cells(ActiveCell.Row, 1)'. If you want to assign a
range to a variable then it should have Set in front of it:-
Set rng = Cells(ActiveCell.Row, 1)

To put the following code in the correct place, right click on the
worksheet
name (tab) that is being deactivated and select View code. The VBA editor
will open in the right place.

Private Sub Worksheet_Deactivate()
Dim Rng As Range
Dim strNewSht As String

'Following line is for info only confirming the the new sheet
'is the active sheet before any of the code is processed.
MsgBox "Active Sheet is: " & ActiveSheet.Name

'Save the new sheet name
strNewSht = ActiveSheet.Name

'Select the original sheet
Sheets("Sheet1").Select

'Assign required range to a variable

Set Rng = Cells(ActiveCell.Row, 1)

'Disable events and re-select the new sheet
Application.EnableEvents = False
Sheets(strNewSht).Select
'Re-enable events
Application.EnableEvents = True

'Can now use the range as a reference to
'manipulate dat on the deactivated sheet
If Rng.Offset(0, 1) = "" Then
Rng.Clear
End If

End Sub

Regards,

OssieMac

Hi again Patrick,

If I had read your post properly before I would have realized that you were
using a multitab form and not referring to tabs (worksheets) in a workbook.

I see that you now have another reply while I was working on another answer
and I don't know whether it answers your question or not. However, since I
have spent the time on it I might as well post what I have.

I suggest that you create a multitab in a new workbook and copy this code
(both procedures) into the forms code and run it and then select various tabs
on the form and observe what it does. Page3 (or item2) is set to be the tab
which is identified as the one needing other code if you exit from it. See my
comments at the top of the first procedure re the multitab.

You probably do not need to change back to the previous tab to run the code
required, just identify that it was the previous tab. I changed back to it
and the placed the msgbox just as a demo.

Feel free to get back to me again

It is the only way I know of individually testing for a specific tab which
has just been exited. However, I have not tested it to the nth degree so see
what it does for you.

Private Sub UserForm_Initialize()

'Make sure that you have a form that contains a MultiPage
'named MultiPage1 and that it has 5 or 6 tabs.

'Copy both procedures of this sample code to the form code area
'and run this (Sub UserForm_Initialize) procedure from the VBA editor.

Dim PageName As String
For i = 0 To MultiPage1.Count - 1
PageName = MultiPage1.pages(i).Name
Next i
End Sub


Private Sub Multipage1_Click(ByVal index As Long)
Dim myPage
Dim myPagePrev

'Assign previous saved page number to a variable
myPagePrev = Sheets("Sheet1").Cells(1, 1)

myPage = index 'Set to newly activated page
If myPagePrev = 2 Then 'Only if previous active page
'Change back to the previous page
Application.EnableEvents = False
MultiPage1.Value = 2
Application.EnableEvents = True

'Insert code for what need to be done here
MsgBox "Previous tab " & MultiPage1.pages(myPagePrev) _
.Name & " Re-activated so the required code could be run"

'Re-activate the selected page (tab)
Application.EnableEvents = False
MultiPage1.Value = myPage 'Change to the new page
Application.EnableEvents = True
MsgBox "New tab = " & MultiPage1.pages(myPage).Name & " now active"
End If
'Assign the active page (tab) number to a cell for saving
Sheets("Sheet1").Cells(1, 1) = myPage

End Sub


Regards,

OssieMac




"Doug Glancy" wrote:

Patrick,

The "Page1_Exit" that you're seeing is referring to MultiPage_1, which is
the name of the whole MP control.

This is not something I've much experience with, but I believe you need to
use the MP's Change event and a public variable to hold the value of the
current page. Here's a simple example:

Option Explicit
Dim CurrentPage As Long
Private Sub MultiPage1_Change()
If CurrentPage + 1 = 1 then 'page index starts at 0
MsgBox "Now leaving page 1"
EndIf
CurrentPage = Me.MultiPage1.Value ' set it to the new "active" page
End Sub

hth,

Doug

"Patrick C. Simonds" wrote in message
...
Thanks for your reply. I was able to use part of what you suggested If
Rng.Offset(0, 1) = "" Then

Rng.Clear
It works because the worksheet is still active.

Below is the subroutine I came up with, but I must be missing something. I
thought "MultiPage1_Exit" would only apply to page 1 of the 6 pages. But
the code runs every time I switch from one page to the next. How can I
make this run only when I leave Page1?



Private Sub MultiPage1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Set Rng = Cells(ActiveCell.Row, 1)
If Rng.Offset(0, 1) = "" Then
Rng.Clear
End If

End Sub


"OssieMac" wrote in message
...
Hi Patrick,

The following code is the only way I have been able to reference the
active
cell on a sheet which I deactivate. If anyone else reads this and has
other
options then I am certainly interested.

My method involves the following:-

Saving the name of the new sheet.
Re-activating the old sheet.
Assign the active cell to a variable.
Turn off events .
Re-select the new required worksheet.

Also your code 'rng = Cells(ActiveCell.Row, 1)'. If you want to assign a
range to a variable then it should have Set in front of it:-
Set rng = Cells(ActiveCell.Row, 1)

To put the following code in the correct place, right click on the
worksheet
name (tab) that is being deactivated and select View code. The VBA editor
will open in the right place.

Private Sub Worksheet_Deactivate()
Dim Rng As Range
Dim strNewSht As String

'Following line is for info only confirming the the new sheet
'is the active sheet before any of the code is processed.
MsgBox "Active Sheet is: " & ActiveSheet.Name

'Save the new sheet name
strNewSht = ActiveSheet.Name

'Select the original sheet
Sheets("Sheet1").Select

'Assign required range to a variable

Set Rng = Cells(ActiveCell.Row, 1)

'Disable events and re-select the new sheet
Application.EnableEvents = False
Sheets(strNewSht).Select
'Re-enable events
Application.EnableEvents = True

'Can now use the range as a reference to
'manipulate dat on the deactivated sheet
If Rng.Offset(0, 1) = "" Then
Rng.Clear
End If

End Sub

Regards,

OssieMac