Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Linking worksheet event codes
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Mr. G.
 
Posts: n/a
Default Linking worksheet event codes
The (row shading) worksheet event code furnished by B.Phillips was exactly
what I've been looking for. However, since I have already have a worksheet
event code running (developed by Julie D) when the user opens up the
spreadsheet, does anyone know if it's possible to still incorporate/link his
code with mine?

(Mr. Phillips)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
End With
.FormatConditions(1).Interior.ColorIndex = 20
End With

(Mine)
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("F12:F15")) Is Nothing Then '1"
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
For Each c In Range("F13:F15")
If c.Value = "" And c.Offset(0, 46) = 0 Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.EntireRow.Hidden = True
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
For Each c In Range("F12:F15")
If c.Value < "" Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.Offset(1, 0).EntireRow.Hidden = False
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
ElseIf Not Intersect(Target, Me.Range("F17:F20")) Is Nothing Then '1"
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
For Each c In Range("F18:F20")
If c.Value = "" And c.Offset(0, 46) = 0 Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.EntireRow.Hidden = True
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
For Each c In Range("F17:F20")
If c.Value < "" Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.Offset(1, 0).EntireRow.Hidden = False
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End If
End Sub

Reply With QuoteReply With Quote
  #2   Report Post  
Kassie
 
Posts: n/a
Default
Hi

Simply call his routine from yours, just before you end your routine

Worksheet_SelectionChange
End Sub

Or copy and paste the body into your routine, where you want it to execute;

Or vice versa of course, depending on which one you want to execute first

"Mr. G." wrote:

The (row shading) worksheet event code furnished by B.Phillips was exactly
what I've been looking for. However, since I have already have a worksheet
event code running (developed by Julie D) when the user opens up the
spreadsheet, does anyone know if it's possible to still incorporate/link his
code with mine?

(Mr. Phillips)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
End With
.FormatConditions(1).Interior.ColorIndex = 20
End With

(Mine)
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("F12:F15")) Is Nothing Then '1"
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
For Each c In Range("F13:F15")
If c.Value = "" And c.Offset(0, 46) = 0 Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.EntireRow.Hidden = True
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
For Each c In Range("F12:F15")
If c.Value < "" Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.Offset(1, 0).EntireRow.Hidden = False
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
ElseIf Not Intersect(Target, Me.Range("F17:F20")) Is Nothing Then '1"
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
For Each c In Range("F18:F20")
If c.Value = "" And c.Offset(0, 46) = 0 Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.EntireRow.Hidden = True
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
For Each c In Range("F17:F20")
If c.Value < "" Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.Offset(1, 0).EntireRow.Hidden = False
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End If
End Sub

Reply With QuoteReply With Quote
  #3   Report Post  
Mr. G.
 
Posts: n/a
Default
Hi Kassie,

Thank you for the prompt resonse. Unfortunately, I may have misunderstood
your instuctions because I'me receiving the following Compile Error message:
Expected End Sub. Since I'm very new with macros/wksht codes, I'm probably
gonna need a little more visual aide. Was I suppose to attach Mr. Phillip's
code to the end of mine (btwn "End If" and "End Sub")? Because that's what I
did and subsequently, the row shading function appeared to work but when I
performed a function that suppose to activate my event code (unhiding rows
automatically) I received the earlier mentioned Error message. When viewing
the worksheet code, the last "End If" was highlighted. Can you tell/show me
what I need to do?

Thank you,



"Kassie" wrote:

Hi

Simply call his routine from yours, just before you end your routine

Worksheet_SelectionChange
End Sub

Or copy and paste the body into your routine, where you want it to execute;

Or vice versa of course, depending on which one you want to execute first

"Mr. G." wrote:

The (row shading) worksheet event code furnished by B.Phillips was exactly
what I've been looking for. However, since I have already have a worksheet
event code running (developed by Julie D) when the user opens up the
spreadsheet, does anyone know if it's possible to still incorporate/link his
code with mine?

(Mr. Phillips)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
End With
.FormatConditions(1).Interior.ColorIndex = 20
End With

(Mine)
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("F12:F15")) Is Nothing Then '1"
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
For Each c In Range("F13:F15")
If c.Value = "" And c.Offset(0, 46) = 0 Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.EntireRow.Hidden = True
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
For Each c In Range("F12:F15")
If c.Value < "" Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.Offset(1, 0).EntireRow.Hidden = False
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
ElseIf Not Intersect(Target, Me.Range("F17:F20")) Is Nothing Then '1"
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
For Each c In Range("F18:F20")
If c.Value = "" And c.Offset(0, 46) = 0 Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.EntireRow.Hidden = True
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
For Each c In Range("F17:F20")
If c.Value < "" Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.Offset(1, 0).EntireRow.Hidden = False
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End If
End Sub

Reply With QuoteReply With Quote
  #4   Report Post  
Kassie
 
Posts: n/a
Default
Hi

I think what you did, was that you copied the complete macro, i.e. from the
name down to the end, into your code. You should only have copied the part
from Cells.FormatConditions.Delete to the end into your routine

"Mr. G." wrote:

Hi Kassie,

Thank you for the prompt resonse. Unfortunately, I may have misunderstood
your instuctions because I'me receiving the following Compile Error message:
Expected End Sub. Since I'm very new with macros/wksht codes, I'm probably
gonna need a little more visual aide. Was I suppose to attach Mr. Phillip's
code to the end of mine (btwn "End If" and "End Sub")? Because that's what I
did and subsequently, the row shading function appeared to work but when I
performed a function that suppose to activate my event code (unhiding rows
automatically) I received the earlier mentioned Error message. When viewing
the worksheet code, the last "End If" was highlighted. Can you tell/show me
what I need to do?

Thank you,



"Kassie" wrote:

Hi

Simply call his routine from yours, just before you end your routine

Worksheet_SelectionChange
End Sub

Or copy and paste the body into your routine, where you want it to execute;

Or vice versa of course, depending on which one you want to execute first

"Mr. G." wrote:

The (row shading) worksheet event code furnished by B.Phillips was exactly
what I've been looking for. However, since I have already have a worksheet
event code running (developed by Julie D) when the user opens up the
spreadsheet, does anyone know if it's possible to still incorporate/link his
code with mine?

(Mr. Phillips)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
End With
.FormatConditions(1).Interior.ColorIndex = 20
End With

(Mine)
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("F12:F15")) Is Nothing Then '1"
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
For Each c In Range("F13:F15")
If c.Value = "" And c.Offset(0, 46) = 0 Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.EntireRow.Hidden = True
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
For Each c In Range("F12:F15")
If c.Value < "" Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.Offset(1, 0).EntireRow.Hidden = False
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
ElseIf Not Intersect(Target, Me.Range("F17:F20")) Is Nothing Then '1"
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
For Each c In Range("F18:F20")
If c.Value = "" And c.Offset(0, 46) = 0 Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.EntireRow.Hidden = True
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
For Each c In Range("F17:F20")
If c.Value < "" Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.Offset(1, 0).EntireRow.Hidden = False
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End If
End Sub

Reply With QuoteReply With Quote
  #5   Report Post  
Mr. G.
 
Posts: n/a
Default
Hi Kassie,

Unfortunately, I still can't get it to work. But I think I may have found
part of the problem. If you notice my worksheet code includes a protection
password (lindAP). Populating (or unpopulating) certain cells activates this
code which unprotects the worksheet in order to unhide (or hide) partiular
rows and before reverting back to its protected status. During my (many)
unsuccessful attempts, I did discover that when I manually unprotect this
worksheet, the function to highlight rows operates. However, I can't get my
rows to unhide. Any suggestions, or possible solution?





"Kassie" wrote:

Hi

I think what you did, was that you copied the complete macro, i.e. from the
name down to the end, into your code. You should only have copied the part
from Cells.FormatConditions.Delete to the end into your routine

"Mr. G." wrote:

Hi Kassie,

Thank you for the prompt resonse. Unfortunately, I may have misunderstood
your instuctions because I'me receiving the following Compile Error message:
Expected End Sub. Since I'm very new with macros/wksht codes, I'm probably
gonna need a little more visual aide. Was I suppose to attach Mr. Phillip's
code to the end of mine (btwn "End If" and "End Sub")? Because that's what I
did and subsequently, the row shading function appeared to work but when I
performed a function that suppose to activate my event code (unhiding rows
automatically) I received the earlier mentioned Error message. When viewing
the worksheet code, the last "End If" was highlighted. Can you tell/show me
what I need to do?

Thank you,



"Kassie" wrote:

Hi

Simply call his routine from yours, just before you end your routine

Worksheet_SelectionChange
End Sub

Or copy and paste the body into your routine, where you want it to execute;

Or vice versa of course, depending on which one you want to execute first

"Mr. G." wrote:

The (row shading) worksheet event code furnished by B.Phillips was exactly
what I've been looking for. However, since I have already have a worksheet
event code running (developed by Julie D) when the user opens up the
spreadsheet, does anyone know if it's possible to still incorporate/link his
code with mine?

(Mr. Phillips)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
End With
.FormatConditions(1).Interior.ColorIndex = 20
End With

(Mine)
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("F12:F15")) Is Nothing Then '1"
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
For Each c In Range("F13:F15")
If c.Value = "" And c.Offset(0, 46) = 0 Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.EntireRow.Hidden = True
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
For Each c In Range("F12:F15")
If c.Value < "" Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.Offset(1, 0).EntireRow.Hidden = False
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
ElseIf Not Intersect(Target, Me.Range("F17:F20")) Is Nothing Then '1"
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
For Each c In Range("F18:F20")
If c.Value = "" And c.Offset(0, 46) = 0 Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.EntireRow.Hidden = True
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
For Each c In Range("F17:F20")
If c.Value < "" Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.Offset(1, 0).EntireRow.Hidden = False
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End If
End Sub

Reply With QuoteReply With Quote
  #6   Report Post  
Kassie
 
Posts: n/a
Default
Hi

Shouldn't you code read:

Worksheets("EXP RPT").Protect password:="lindAP"
and
Worksheets("EXP RPT").Unprotect password:="lindAP"?


"Mr. G." wrote:

Hi Kassie,

Unfortunately, I still can't get it to work. But I think I may have found
part of the problem. If you notice my worksheet code includes a protection
password (lindAP). Populating (or unpopulating) certain cells activates this
code which unprotects the worksheet in order to unhide (or hide) partiular
rows and before reverting back to its protected status. During my (many)
unsuccessful attempts, I did discover that when I manually unprotect this
worksheet, the function to highlight rows operates. However, I can't get my
rows to unhide. Any suggestions, or possible solution?





"Kassie" wrote:

Hi

I think what you did, was that you copied the complete macro, i.e. from the
name down to the end, into your code. You should only have copied the part
from Cells.FormatConditions.Delete to the end into your routine

"Mr. G." wrote:

Hi Kassie,

Thank you for the prompt resonse. Unfortunately, I may have misunderstood
your instuctions because I'me receiving the following Compile Error message:
Expected End Sub. Since I'm very new with macros/wksht codes, I'm probably
gonna need a little more visual aide. Was I suppose to attach Mr. Phillip's
code to the end of mine (btwn "End If" and "End Sub")? Because that's what I
did and subsequently, the row shading function appeared to work but when I
performed a function that suppose to activate my event code (unhiding rows
automatically) I received the earlier mentioned Error message. When viewing
the worksheet code, the last "End If" was highlighted. Can you tell/show me
what I need to do?

Thank you,



"Kassie" wrote:

Hi

Simply call his routine from yours, just before you end your routine

Worksheet_SelectionChange
End Sub

Or copy and paste the body into your routine, where you want it to execute;

Or vice versa of course, depending on which one you want to execute first

"Mr. G." wrote:

The (row shading) worksheet event code furnished by B.Phillips was exactly
what I've been looking for. However, since I have already have a worksheet
event code running (developed by Julie D) when the user opens up the
spreadsheet, does anyone know if it's possible to still incorporate/link his
code with mine?

(Mr. Phillips)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
End With
.FormatConditions(1).Interior.ColorIndex = 20
End With

(Mine)
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("F12:F15")) Is Nothing Then '1"
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
For Each c In Range("F13:F15")
If c.Value = "" And c.Offset(0, 46) = 0 Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.EntireRow.Hidden = True
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
For Each c In Range("F12:F15")
If c.Value < "" Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.Offset(1, 0).EntireRow.Hidden = False
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
ElseIf Not Intersect(Target, Me.Range("F17:F20")) Is Nothing Then '1"
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
For Each c In Range("F18:F20")
If c.Value = "" And c.Offset(0, 46) = 0 Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.EntireRow.Hidden = True
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
For Each c In Range("F17:F20")
If c.Value < "" Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.Offset(1, 0).EntireRow.Hidden = False
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End If
End Sub

Reply With QuoteReply With Quote
  #7   Report Post  
Mr. G.
 
Posts: n/a
Default
You're absolutely right! It works!! Thank you so much.

"Kassie" wrote:

Hi

Shouldn't you code read:

Worksheets("EXP RPT").Protect password:="lindAP"
and
Worksheets("EXP RPT").Unprotect password:="lindAP"?


"Mr. G." wrote:

Hi Kassie,

Unfortunately, I still can't get it to work. But I think I may have found
part of the problem. If you notice my worksheet code includes a protection
password (lindAP). Populating (or unpopulating) certain cells activates this
code which unprotects the worksheet in order to unhide (or hide) partiular
rows and before reverting back to its protected status. During my (many)
unsuccessful attempts, I did discover that when I manually unprotect this
worksheet, the function to highlight rows operates. However, I can't get my
rows to unhide. Any suggestions, or possible solution?





"Kassie" wrote:

Hi

I think what you did, was that you copied the complete macro, i.e. from the
name down to the end, into your code. You should only have copied the part
from Cells.FormatConditions.Delete to the end into your routine

"Mr. G." wrote:

Hi Kassie,

Thank you for the prompt resonse. Unfortunately, I may have misunderstood
your instuctions because I'me receiving the following Compile Error message:
Expected End Sub. Since I'm very new with macros/wksht codes, I'm probably
gonna need a little more visual aide. Was I suppose to attach Mr. Phillip's
code to the end of mine (btwn "End If" and "End Sub")? Because that's what I
did and subsequently, the row shading function appeared to work but when I
performed a function that suppose to activate my event code (unhiding rows
automatically) I received the earlier mentioned Error message. When viewing
the worksheet code, the last "End If" was highlighted. Can you tell/show me
what I need to do?

Thank you,



"Kassie" wrote:

Hi

Simply call his routine from yours, just before you end your routine

Worksheet_SelectionChange
End Sub

Or copy and paste the body into your routine, where you want it to execute;

Or vice versa of course, depending on which one you want to execute first

"Mr. G." wrote:

The (row shading) worksheet event code furnished by B.Phillips was exactly
what I've been looking for. However, since I have already have a worksheet
event code running (developed by Julie D) when the user opens up the
spreadsheet, does anyone know if it's possible to still incorporate/link his
code with mine?

(Mr. Phillips)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
End With
.FormatConditions(1).Interior.ColorIndex = 20
End With

(Mine)
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("F12:F15")) Is Nothing Then '1"
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
For Each c In Range("F13:F15")
If c.Value = "" And c.Offset(0, 46) = 0 Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.EntireRow.Hidden = True
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
For Each c In Range("F12:F15")
If c.Value < "" Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.Offset(1, 0).EntireRow.Hidden = False
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
ElseIf Not Intersect(Target, Me.Range("F17:F20")) Is Nothing Then '1"
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
For Each c In Range("F18:F20")
If c.Value = "" And c.Offset(0, 46) = 0 Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.EntireRow.Hidden = True
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
For Each c In Range("F17:F20")
If c.Value < "" Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.Offset(1, 0).EntireRow.Hidden = False
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End If
End Sub

Reply With QuoteReply With Quote
  #8   Report Post  
Kassie
 
Posts: n/a
Default
That was all my pleasure Mr. G! I'm glad you could sort it out!

"Mr. G." wrote:

You're absolutely right! It works!! Thank you so much.

"Kassie" wrote:

Hi

Shouldn't you code read:

Worksheets("EXP RPT").Protect password:="lindAP"
and
Worksheets("EXP RPT").Unprotect password:="lindAP"?


"Mr. G." wrote:

Hi Kassie,

Unfortunately, I still can't get it to work. But I think I may have found
part of the problem. If you notice my worksheet code includes a protection
password (lindAP). Populating (or unpopulating) certain cells activates this
code which unprotects the worksheet in order to unhide (or hide) partiular
rows and before reverting back to its protected status. During my (many)
unsuccessful attempts, I did discover that when I manually unprotect this
worksheet, the function to highlight rows operates. However, I can't get my
rows to unhide. Any suggestions, or possible solution?





"Kassie" wrote:

Hi

I think what you did, was that you copied the complete macro, i.e. from the
name down to the end, into your code. You should only have copied the part
from Cells.FormatConditions.Delete to the end into your routine

"Mr. G." wrote:

Hi Kassie,

Thank you for the prompt resonse. Unfortunately, I may have misunderstood
your instuctions because I'me receiving the following Compile Error message:
Expected End Sub. Since I'm very new with macros/wksht codes, I'm probably
gonna need a little more visual aide. Was I suppose to attach Mr. Phillip's
code to the end of mine (btwn "End If" and "End Sub")? Because that's what I
did and subsequently, the row shading function appeared to work but when I
performed a function that suppose to activate my event code (unhiding rows
automatically) I received the earlier mentioned Error message. When viewing
the worksheet code, the last "End If" was highlighted. Can you tell/show me
what I need to do?

Thank you,



"Kassie" wrote:

Hi

Simply call his routine from yours, just before you end your routine

Worksheet_SelectionChange
End Sub

Or copy and paste the body into your routine, where you want it to execute;

Or vice versa of course, depending on which one you want to execute first

"Mr. G." wrote:

The (row shading) worksheet event code furnished by B.Phillips was exactly
what I've been looking for. However, since I have already have a worksheet
event code running (developed by Julie D) when the user opens up the
spreadsheet, does anyone know if it's possible to still incorporate/link his
code with mine?

(Mr. Phillips)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
End With
.FormatConditions(1).Interior.ColorIndex = 20
End With

(Mine)
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("F12:F15")) Is Nothing Then '1"
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
For Each c In Range("F13:F15")
If c.Value = "" And c.Offset(0, 46) = 0 Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.EntireRow.Hidden = True
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
For Each c In Range("F12:F15")
If c.Value < "" Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.Offset(1, 0).EntireRow.Hidden = False
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
ElseIf Not Intersect(Target, Me.Range("F17:F20")) Is Nothing Then '1"
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
For Each c In Range("F18:F20")
If c.Value = "" And c.Offset(0, 46) = 0 Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.EntireRow.Hidden = True
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
For Each c In Range("F17:F20")
If c.Value < "" Then
Sheets("EXP RPT").Unprotect ("lindAP")
c.Offset(1, 0).EntireRow.Hidden = False
i = c.Row
Sheets("EXP RPT").Protect ("lindAP")
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End If
End Sub

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel worksheet linking Shiren Excel Worksheet Functions 0 July 6th 05 05:33 PM
Linking cells in a worksheet to other worksheets in a workbook Dave Excel Discussion (Misc queries) 4 June 24th 05 06:18 PM
Worksheet linking Bed Excel Worksheet Functions 1 April 29th 05 03:08 AM
Worksheet Row Change event crazybass2 Excel Discussion (Misc queries) 4 December 8th 04 05:29 PM
Linking items GREATER THAN O on another worksheet in the same Work Eddie Shapiro Excel Discussion (Misc queries) 4 December 1st 04 02:55 PM


All times are GMT +1. The time now is 04:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement