Excel Using the VB activate event in Excel

A

arukas03

Joined
Mar 31, 2008
Messages
1
Reaction score
0
Hi,
I am looking for some help in coding a simple function for a scoring program i have written in excel. The goal of the function is to hide rows/columns that contain data entry areas and display areas that arent being used. It works by testing for the presence of a participants name and then declaring the cells as hidden true or false.

The code i have currently does this job perfectly, the problem is that it does it using SelectionChange. Given the nature of the page it is on is data entry, a selection change occurs often and it is very annoying to have the page refresh all the time. My goal is to have it run using the activate event or the follow hyperlink event. I have tried for several hours to figure it out myself but i don't have a very good handle on VB. Any help would be appreciated.

Regards
Tom

The sheet users navigate from to get to the data entry page is called TitlePage.
The data entry page is called JudgesScores.

The current code is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If (Worksheets("TitlePage").Range("I15") = "") Then
Worksheets("JudgesScores").Rows("2:12").Hidden = True
Else
Worksheets("JudgesScores").Rows("2:12").Hidden = False
End If
If (Worksheets("TitlePage").Range("I17") = "") Then
Worksheets("JudgesScores").Rows("13:23").Hidden = True
Else
Worksheets("JudgesScores").Rows("13:23").Hidden = False
End If
If (Worksheets("TitlePage").Range("I19") = "") Then
Worksheets("JudgesScores").Rows("24:34").Hidden = True
Else
Worksheets("JudgesScores").Rows("24:34").Hidden = False
End If
If (Worksheets("TitlePage").Range("I21") = "") Then
Worksheets("JudgesScores").Rows("35:45").Hidden = True
Else
Worksheets("JudgesScores").Rows("35:45").Hidden = False
End If
If (Worksheets("TitlePage").Range("I23") = "") Then
Worksheets("JudgesScores").Rows("46:56").Hidden = True
Else
Worksheets("JudgesScores").Rows("46:56").Hidden = False
End If
If (Worksheets("TitlePage").Range("I25") = "") Then
Worksheets("JudgesScores").Rows("57:67").Hidden = True
Else
Worksheets("JudgesScores").Rows("57:67").Hidden = False
End If
If (Worksheets("TitlePage").Range("I27") = "") Then
Worksheets("JudgesScores").Rows("68:78").Hidden = True
Else
Worksheets("JudgesScores").Rows("68:78").Hidden = False
End If
If (Worksheets("TitlePage").Range("I29") = "") Then
Worksheets("JudgesScores").Rows("79:89").Hidden = True
Else
Worksheets("JudgesScores").Rows("79:89").Hidden = False
End If
If (Worksheets("TitlePage").Range("I31") = "") Then
Worksheets("JudgesScores").Rows("90:100").Hidden = True
Else
Worksheets("JudgesScores").Rows("90:100").Hidden = False
End If
If (Worksheets("TitlePage").Range("I33") = "") Then
Worksheets("JudgesScores").Rows("101:111").Hidden = True
Else
Worksheets("JudgesScores").Rows("101:111").Hidden = False
End If
End Sub
 
Back
Top