I need help I am unsure of what is going on. I have five userforms
and five worksheets.. and an additional worksheet for reference page.
The useforms are used to input data. There is a complex code that
denies any duplicate data to be entered into the referring worksheet,
example sheet1 is used to store userform1's data so if you enter 2
into userform1 sheet1 will not store it if 2 is already in sheet1. It
is all controlled by code stored in the userform data validation;
validate then input. The problem is that the sheets will ultimately be
hidden from view and the reference page will be left standing alone.
When I go to implement this task there is a issue of getting the
validation code to work for duplicate data. The duplicate data code
only works when the sheet that you are trying to enter data into is
open and selected????? Is there anyone who understands what I am saying
and knows a way to help me.

Example:

Private Sub Save_Click()
If ValidateData = True Then
EnterDataInWorksheet
Unload Me
With Application
If .WorksheetFunction.CountIf(.Range("a2:a100"), FirstName.Value) = 1
Then
Unload Me
'more code...
Else

End If
'other action
End With
End If
End Sub

I tried storing the code into the save button along with the validate
data I am unsure of Private and Public?

Here is code in validate

Public Function ValidateData() As Boolean
' Returns True if the data in the user form
' is complete, False otherwise. Displays a
' message identifying the problem.
With Application
If .WorksheetFunction.CountIf(.Range("a2:a100"), FirstName.Value) = 1
Then
MsgBox "alert duplicate data", 16, "input!"
Unload Me
'more code...
Else

End If
'other action
End With
If FirstName.Value = "" Then
MsgBox "You must enter a First Name."
ValidateData = False
Exit Function
End If
ValidateData = True
End Function

Please don't multi post into all of the different forums. It just causes
duplication of effort from those who are replying. That being said... You
have the line:

With Application
If .WorksheetFunction.CountIf(.Range("a2:a100"), FirstName.Value) = 1

Which translates to Application.Range("a2:a100") which is not valid syntax.
Ranges are contained in sheets so you need a valid sheet object something
like this...

Activesheet.Range("a2:a100")
or
Sheet1.Range("a2:a100")
or
Sheets("MySheet").Range("a2:a100")


--
HTH...

Jim Thomlinson


"Zigball" wrote:

I need help I am unsure of what is going on. I have five userforms
and five worksheets.. and an additional worksheet for reference page.
The useforms are used to input data. There is a complex code that
denies any duplicate data to be entered into the referring worksheet,
example sheet1 is used to store userform1's data so if you enter 2
into userform1 sheet1 will not store it if 2 is already in sheet1. It
is all controlled by code stored in the userform data validation;
validate then input. The problem is that the sheets will ultimately be
hidden from view and the reference page will be left standing alone.
When I go to implement this task there is a issue of getting the
validation code to work for duplicate data. The duplicate data code
only works when the sheet that you are trying to enter data into is
open and selected????? Is there anyone who understands what I am saying
and knows a way to help me.

Example:

Private Sub Save_Click()
If ValidateData = True Then
EnterDataInWorksheet
Unload Me
With Application
If .WorksheetFunction.CountIf(.Range("a2:a100"), FirstName.Value) = 1
Then
Unload Me
'more code...
Else

End If
'other action
End With
End If
End Sub

I tried storing the code into the save button along with the validate
data I am unsure of Private and Public?

Here is code in validate

Public Function ValidateData() As Boolean
' Returns True if the data in the user form
' is complete, False otherwise. Displays a
' message identifying the problem.
With Application
If .WorksheetFunction.CountIf(.Range("a2:a100"), FirstName.Value) = 1
Then
MsgBox "alert duplicate data", 16, "input!"
Unload Me
'more code...
Else

End If
'other action
End With
If FirstName.Value = "" Then
MsgBox "You must enter a First Name."
ValidateData = False
Exit Function
End If
ValidateData = True
End Function

jIM, i'LL TRY THIS OUT IT MAKES SENSE, THANK YOU, HOPEFULLY IT WORKS IF
NOT I'LL WRITE BACK. ABOUT THE MULTI POST I WAS TOLD TO CROSS POST ?
ANYWAY I'LL CUT THE CROSS POSTING AND MULTI POSTING OUT.
THANK YOU AGAIN!
Jim Thomlinson wrote:
Please don't multi post into all of the different forums. It just causes
duplication of effort from those who are replying. That being said... You
have the line:

With Application
If .WorksheetFunction.CountIf(.Range("a2:a100"), FirstName.Value) = 1

Which translates to Application.Range("a2:a100") which is not valid syntax.
Ranges are contained in sheets so you need a valid sheet object something
like this...

Activesheet.Range("a2:a100")
or
Sheet1.Range("a2:a100")
or
Sheets("MySheet").Range("a2:a100")


--
HTH...

Jim Thomlinson


"Zigball" wrote:

I need help I am unsure of what is going on. I have five userforms
and five worksheets.. and an additional worksheet for reference page.
The useforms are used to input data. There is a complex code that
denies any duplicate data to be entered into the referring worksheet,
example sheet1 is used to store userform1's data so if you enter 2
into userform1 sheet1 will not store it if 2 is already in sheet1. It
is all controlled by code stored in the userform data validation;
validate then input. The problem is that the sheets will ultimately be
hidden from view and the reference page will be left standing alone.
When I go to implement this task there is a issue of getting the
validation code to work for duplicate data. The duplicate data code
only works when the sheet that you are trying to enter data into is
open and selected????? Is there anyone who understands what I am saying
and knows a way to help me.

Example:

Private Sub Save_Click()
If ValidateData = True Then
EnterDataInWorksheet
Unload Me
With Application
If .WorksheetFunction.CountIf(.Range("a2:a100"), FirstName.Value) = 1
Then
Unload Me
'more code...
Else

End If
'other action
End With
End If
End Sub

I tried storing the code into the save button along with the validate
data I am unsure of Private and Public?

Here is code in validate

Public Function ValidateData() As Boolean
' Returns True if the data in the user form
' is complete, False otherwise. Displays a
' message identifying the problem.
With Application
If .WorksheetFunction.CountIf(.Range("a2:a100"), FirstName.Value) = 1
Then
MsgBox "alert duplicate data", 16, "input!"
Unload Me
'more code...
Else

End If
'other action
End With
If FirstName.Value = "" Then
MsgBox "You must enter a First Name."
ValidateData = False
Exit Function
End If
ValidateData = True
End Function

Jim Thomlinson, you are a great help, thank you, i have another problem
if you are up to it I will write you later same place same different
time, thanks again! speak with you later

Jim Thomlinson wrote:
Please don't multi post into all of the different forums. It just causes
duplication of effort from those who are replying. That being said... You
have the line:

With Application
If .WorksheetFunction.CountIf(.Range("a2:a100"), FirstName.Value) = 1

Which translates to Application.Range("a2:a100") which is not valid syntax.
Ranges are contained in sheets so you need a valid sheet object something
like this...

Activesheet.Range("a2:a100")
or
Sheet1.Range("a2:a100")
or
Sheets("MySheet").Range("a2:a100")


--
HTH...

Jim Thomlinson


"Zigball" wrote:

I need help I am unsure of what is going on. I have five userforms
and five worksheets.. and an additional worksheet for reference page.
The useforms are used to input data. There is a complex code that
denies any duplicate data to be entered into the referring worksheet,
example sheet1 is used to store userform1's data so if you enter 2
into userform1 sheet1 will not store it if 2 is already in sheet1. It
is all controlled by code stored in the userform data validation;
validate then input. The problem is that the sheets will ultimately be
hidden from view and the reference page will be left standing alone.
When I go to implement this task there is a issue of getting the
validation code to work for duplicate data. The duplicate data code
only works when the sheet that you are trying to enter data into is
open and selected????? Is there anyone who understands what I am saying
and knows a way to help me.

Example:

Private Sub Save_Click()
If ValidateData = True Then
EnterDataInWorksheet
Unload Me
With Application
If .WorksheetFunction.CountIf(.Range("a2:a100"), FirstName.Value) = 1
Then
Unload Me
'more code...
Else

End If
'other action
End With
End If
End Sub

I tried storing the code into the save button along with the validate
data I am unsure of Private and Public?

Here is code in validate

Public Function ValidateData() As Boolean
' Returns True if the data in the user form
' is complete, False otherwise. Displays a
' message identifying the problem.
With Application
If .WorksheetFunction.CountIf(.Range("a2:a100"), FirstName.Value) = 1
Then
MsgBox "alert duplicate data", 16, "input!"
Unload Me
'more code...
Else

End If
'other action
End With
If FirstName.Value = "" Then
MsgBox "You must enter a First Name."
ValidateData = False
Exit Function
End If
ValidateData = True
End Function

Check out this link...

http://www.cpearson.com/excel/newposte.htm
--
HTH...

Jim Thomlinson


"Zigball" wrote:

jIM, i'LL TRY THIS OUT IT MAKES SENSE, THANK YOU, HOPEFULLY IT WORKS IF
NOT I'LL WRITE BACK. ABOUT THE MULTI POST I WAS TOLD TO CROSS POST ?
ANYWAY I'LL CUT THE CROSS POSTING AND MULTI POSTING OUT.
THANK YOU AGAIN!
Jim Thomlinson wrote:
Please don't multi post into all of the different forums. It just causes
duplication of effort from those who are replying. That being said... You
have the line:

With Application
If .WorksheetFunction.CountIf(.Range("a2:a100"), FirstName.Value) = 1

Which translates to Application.Range("a2:a100") which is not valid syntax.
Ranges are contained in sheets so you need a valid sheet object something
like this...

Activesheet.Range("a2:a100")
or
Sheet1.Range("a2:a100")
or
Sheets("MySheet").Range("a2:a100")


--
HTH...

Jim Thomlinson


"Zigball" wrote:

I need help I am unsure of what is going on. I have five userforms
and five worksheets.. and an additional worksheet for reference page.
The useforms are used to input data. There is a complex code that
denies any duplicate data to be entered into the referring worksheet,
example sheet1 is used to store userform1's data so if you enter 2
into userform1 sheet1 will not store it if 2 is already in sheet1. It
is all controlled by code stored in the userform data validation;
validate then input. The problem is that the sheets will ultimately be
hidden from view and the reference page will be left standing alone.
When I go to implement this task there is a issue of getting the
validation code to work for duplicate data. The duplicate data code
only works when the sheet that you are trying to enter data into is
open and selected????? Is there anyone who understands what I am saying
and knows a way to help me.

Example:

Private Sub Save_Click()
If ValidateData = True Then
EnterDataInWorksheet
Unload Me
With Application
If .WorksheetFunction.CountIf(.Range("a2:a100"), FirstName.Value) = 1
Then
Unload Me
'more code...
Else

End If
'other action
End With
End If
End Sub

I tried storing the code into the save button along with the validate
data I am unsure of Private and Public?

Here is code in validate

Public Function ValidateData() As Boolean
' Returns True if the data in the user form
' is complete, False otherwise. Displays a
' message identifying the problem.
With Application
If .WorksheetFunction.CountIf(.Range("a2:a100"), FirstName.Value) = 1
Then
MsgBox "alert duplicate data", 16, "input!"
Unload Me
'more code...
Else

End If
'other action
End With
If FirstName.Value = "" Then
MsgBox "You must enter a First Name."
ValidateData = False
Exit Function
End If
ValidateData = True
End Function

Hello Jim Thomlinson, as promised i am back needing more help o
yeah,and by the way thanks for the help and thanks for the link to the
site wow!
On to buisness, this problem seems to be a little more tricky but there
all the same to me.
I believe that the problem is very similar to the last problem it just
doesn't have a starting point that i can see being a beginner? This
time I am having trouble with the GetData Sub. This is used in my
userforms to scroll through rows and columns refering to a specific
worksheet. Example: userform1 inputs data into sheet1 then userform1
wants to getdata to display inside of the approiate fields in the
userform. there are four command buttons first, previous, next and
last. there is a text box named rownumber that displays the rownumber
that is being viewed. As before it will work if I am inside the
corresponding sheet but if that sheet is hidden or not selected it will
not work. Would you be able to figure this out? Here is an example of
the code that I have stored in the userform.

The main goal is to have it work while the sheet it is searching is
veryhidden!

Public LastRow As Long
Private Sub GetData()
Dim r As Long
Dim r1 As Range, r2 As Range
Set r1 = Worksheets("sheet1").Range("A1:A100").CurrentRegio n
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
Else
ClearData
MsgBox "Illegal row number"
Exit Sub
End If
If r 1 And r <= LastRow Then
FirstName.Text = r1.Cells(r, 1)

ElseIf r = 2 Then
ClearData
Else
ClearData
MsgBox "Invalid row number"
End If
End Sub

Private Sub ClearData()
FirstName.Text = ""

End Sub

Private Sub RowNumber_Change()
GetData
End Sub

Private Sub First_Click()
RowNumber.Text = "2"
End Sub

Private Sub Previous_Click()
Dim r As Long
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
r = r - 1
If r 1 And r <= LastRow Then
RowNumber.Text = FormatNumber(r, 0)
End If
End If
End Sub

Private Sub Next1_Click()
Dim r As Long
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
r = r + 1
If r 1 And r <= LastRow Then
RowNumber.Text = FormatNumber(r, 0)
End If
End If
End Sub

Private Function FindLastRow()
Dim r As Long
r = 2
Do While r < 65536 And Len(Cells(r, 1).Text) 0
r = r + 1
Loop
FindLastRow = r
End Function

O YEAH THE FIFTH COMMAND BUTTON THE ADD BUTTON CAN'T GET THIS TO WORK
PROPERLY EITHER MAIN CONCERN IS THE REFERENCEING GETTING THE USERFORM
TO PREFORM THE CODE WHILE SHEET IS VERYHIDDEN!
Private Sub Add_Click()
RowNumber.Text = FormatNumber(LastRow, 1)
End Sub

ANOTHER STORY THE PUT DATA I CAN NOT GET IT TO WORK ITS SUPPOSE TO
ALLOW YOU TO UPDATE THE DATA AFTER IT WAS ENTER INTO THE SPREADSHEET! I
THOUGHT I WOULD INCLUDE IT ALSO MAYBE IT CAN HELP OR ?
Private Sub PutData()
Dim r As Long
Dim r1 As Range, r2 As Range
Set r1 = Worksheets("1stProcess").Range("A1").CurrentRegion
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
Else
MsgBox "Illegal row number"
Exit Sub
End If
If r 1 And r < LastRow Then
r1.Cells(r, 1) = ClientID.Text
r1.Cells(r, 2) = LastName.Text
r1.Cells(r, 3) = FirstName.Text
r1.Cells(r, 4) = SpouseLastName.Text
r1.Cells(r, 5) = SpouseFirstName.Text
r1.Cells(r, 6) = DateIn.Text
r1.Cells(r, 7) = DateOut.Text
r1.Cells(r, 8) = Description.Text
r1.Cells(r, 9) = EFile.Text
r1.Cells(r, 10) = Estimates.Text
r1.Cells(r, 11) = Vouchers.Text
r1.Cells(r, 12) = State.Text
DisableSave
Else
MsgBox "Invalid row number"
End If
End Sub

THANK YOU IN ADVANCE JIM!


Jim Thomlinson wrote:
Check out this link...

http://www.cpearson.com/excel/newposte.htm
--
HTH...

Jim Thomlinson


"Zigball" wrote:

jIM, i'LL TRY THIS OUT IT MAKES SENSE, THANK YOU, HOPEFULLY IT WORKS IF
NOT I'LL WRITE BACK. ABOUT THE MULTI POST I WAS TOLD TO CROSS POST ?
ANYWAY I'LL CUT THE CROSS POSTING AND MULTI POSTING OUT.
THANK YOU AGAIN!
Jim Thomlinson wrote:
Please don't multi post into all of the different forums. It just causes
duplication of effort from those who are replying. That being said... You
have the line:

With Application
If .WorksheetFunction.CountIf(.Range("a2:a100"), FirstName.Value) = 1

Which translates to Application.Range("a2:a100") which is not valid syntax.
Ranges are contained in sheets so you need a valid sheet object something
like this...

Activesheet.Range("a2:a100")
or
Sheet1.Range("a2:a100")
or
Sheets("MySheet").Range("a2:a100")


--
HTH...

Jim Thomlinson


"Zigball" wrote:

I need help I am unsure of what is going on. I have five userforms
and five worksheets.. and an additional worksheet for reference page.
The useforms are used to input data. There is a complex code that
denies any duplicate data to be entered into the referring worksheet,
example sheet1 is used to store userform1's data so if you enter 2
into userform1 sheet1 will not store it if 2 is already in sheet1. It
is all controlled by code stored in the userform data validation;
validate then input. The problem is that the sheets will ultimately be
hidden from view and the reference page will be left standing alone.
When I go to implement this task there is a issue of getting the
validation code to work for duplicate data. The duplicate data code
only works when the sheet that you are trying to enter data into is
open and selected????? Is there anyone who understands what I am saying
and knows a way to help me.

Example:

Private Sub Save_Click()
If ValidateData = True Then
EnterDataInWorksheet
Unload Me
With Application
If .WorksheetFunction.CountIf(.Range("a2:a100"), FirstName.Value) = 1
Then
Unload Me
'more code...
Else

End If
'other action
End With
End If
End Sub

I tried storing the code into the save button along with the validate
data I am unsure of Private and Public?

Here is code in validate

Public Function ValidateData() As Boolean
' Returns True if the data in the user form
' is complete, False otherwise. Displays a
' message identifying the problem.
With Application
If .WorksheetFunction.CountIf(.Range("a2:a100"), FirstName.Value) = 1
Then
MsgBox "alert duplicate data", 16, "input!"
Unload Me
'more code...
Else

End If
'other action
End With
If FirstName.Value = "" Then
MsgBox "You must enter a First Name."
ValidateData = False
Exit Function
End If
ValidateData = True
End Function

FormatNumber is not the function you want... You just want format. it is
essentially the same as the Text function in Excel.

RowNumber.Text = FormatNumber(LastRow, 1)
should be
RowNumber.Text = Format(LastRow, "0")

And this function can be a whole lot easier...
Private Function FindLastRow() as long
FindLastRow = cells(rows.count, "A").end(xlUp).Row
End Function

--
HTH...

Jim Thomlinson


"Zigball" wrote:

Hello Jim Thomlinson, as promised i am back needing more help o
yeah,and by the way thanks for the help and thanks for the link to the
site wow!
On to buisness, this problem seems to be a little more tricky but there
all the same to me.
I believe that the problem is very similar to the last problem it just
doesn't have a starting point that i can see being a beginner? This
time I am having trouble with the GetData Sub. This is used in my
userforms to scroll through rows and columns refering to a specific
worksheet. Example: userform1 inputs data into sheet1 then userform1
wants to getdata to display inside of the approiate fields in the
userform. there are four command buttons first, previous, next and
last. there is a text box named rownumber that displays the rownumber
that is being viewed. As before it will work if I am inside the
corresponding sheet but if that sheet is hidden or not selected it will
not work. Would you be able to figure this out? Here is an example of
the code that I have stored in the userform.

The main goal is to have it work while the sheet it is searching is
veryhidden!

Public LastRow As Long
Private Sub GetData()
Dim r As Long
Dim r1 As Range, r2 As Range
Set r1 = Worksheets("sheet1").Range("A1:A100").CurrentRegio n
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
Else
ClearData
MsgBox "Illegal row number"
Exit Sub
End If
If r 1 And r <= LastRow Then
FirstName.Text = r1.Cells(r, 1)

ElseIf r = 2 Then
ClearData
Else
ClearData
MsgBox "Invalid row number"
End If
End Sub

Private Sub ClearData()
FirstName.Text = ""

End Sub

Private Sub RowNumber_Change()
GetData
End Sub

Private Sub First_Click()
RowNumber.Text = "2"
End Sub

Private Sub Previous_Click()
Dim r As Long
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
r = r - 1
If r 1 And r <= LastRow Then
RowNumber.Text = FormatNumber(r, 0)
End If
End If
End Sub

Private Sub Next1_Click()
Dim r As Long
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
r = r + 1
If r 1 And r <= LastRow Then
RowNumber.Text = FormatNumber(r, 0)
End If
End If
End Sub

Private Function FindLastRow()
Dim r As Long
r = 2
Do While r < 65536 And Len(Cells(r, 1).Text) 0
r = r + 1
Loop
FindLastRow = r
End Function

O YEAH THE FIFTH COMMAND BUTTON THE ADD BUTTON CAN'T GET THIS TO WORK
PROPERLY EITHER MAIN CONCERN IS THE REFERENCEING GETTING THE USERFORM
TO PREFORM THE CODE WHILE SHEET IS VERYHIDDEN!
Private Sub Add_Click()
RowNumber.Text = FormatNumber(LastRow, 1)
End Sub

ANOTHER STORY THE PUT DATA I CAN NOT GET IT TO WORK ITS SUPPOSE TO
ALLOW YOU TO UPDATE THE DATA AFTER IT WAS ENTER INTO THE SPREADSHEET! I
THOUGHT I WOULD INCLUDE IT ALSO MAYBE IT CAN HELP OR ?
Private Sub PutData()
Dim r As Long
Dim r1 As Range, r2 As Range
Set r1 = Worksheets("1stProcess").Range("A1").CurrentRegion
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
Else
MsgBox "Illegal row number"
Exit Sub
End If
If r 1 And r < LastRow Then
r1.Cells(r, 1) = ClientID.Text
r1.Cells(r, 2) = LastName.Text
r1.Cells(r, 3) = FirstName.Text
r1.Cells(r, 4) = SpouseLastName.Text
r1.Cells(r, 5) = SpouseFirstName.Text
r1.Cells(r, 6) = DateIn.Text
r1.Cells(r, 7) = DateOut.Text
r1.Cells(r, 8) = Description.Text
r1.Cells(r, 9) = EFile.Text
r1.Cells(r, 10) = Estimates.Text
r1.Cells(r, 11) = Vouchers.Text
r1.Cells(r, 12) = State.Text
DisableSave
Else
MsgBox "Invalid row number"
End If
End Sub

THANK YOU IN ADVANCE JIM!


Jim Thomlinson wrote:
Check out this link...

http://www.cpearson.com/excel/newposte.htm
--
HTH...

Jim Thomlinson


"Zigball" wrote:

jIM, i'LL TRY THIS OUT IT MAKES SENSE, THANK YOU, HOPEFULLY IT WORKS IF
NOT I'LL WRITE BACK. ABOUT THE MULTI POST I WAS TOLD TO CROSS POST ?
ANYWAY I'LL CUT THE CROSS POSTING AND MULTI POSTING OUT.
THANK YOU AGAIN!
Jim Thomlinson wrote:
Please don't multi post into all of the different forums. It just causes
duplication of effort from those who are replying. That being said... You
have the line:

With Application
If .WorksheetFunction.CountIf(.Range("a2:a100"), FirstName.Value) = 1

Which translates to Application.Range("a2:a100") which is not valid syntax.
Ranges are contained in sheets so you need a valid sheet object something
like this...

Activesheet.Range("a2:a100")
or
Sheet1.Range("a2:a100")
or
Sheets("MySheet").Range("a2:a100")


--
HTH...

Jim Thomlinson


"Zigball" wrote:

I need help I am unsure of what is going on. I have five userforms
and five worksheets.. and an additional worksheet for reference page.
The useforms are used to input data. There is a complex code that
denies any duplicate data to be entered into the referring worksheet,
example sheet1 is used to store userform1's data so if you enter 2
into userform1 sheet1 will not store it if 2 is already in sheet1. It
is all controlled by code stored in the userform data validation;
validate then input. The problem is that the sheets will ultimately be
hidden from view and the reference page will be left standing alone.
When I go to implement this task there is a issue of getting the
validation code to work for duplicate data. The duplicate data code
only works when the sheet that you are trying to enter data into is
open and selected????? Is there anyone who understands what I am saying
and knows a way to help me.

Example:

Private Sub Save_Click()
If ValidateData = True Then
EnterDataInWorksheet
Unload Me
With Application
If .WorksheetFunction.CountIf(.Range("a2:a100"), FirstName.Value) = 1
Then
Unload Me
'more code...
Else

End If
'other action
End With
End If
End Sub

I tried storing the code into the save button along with the validate
data I am unsure of Private and Public?

Here is code in validate

Public Function ValidateData() As Boolean
' Returns True if the data in the user form
' is complete, False otherwise. Displays a
' message identifying the problem.
With Application
If .WorksheetFunction.CountIf(.Range("a2:a100"), FirstName.Value) = 1
Then
MsgBox "alert duplicate data", 16, "input!"
Unload Me
'more code...
Else

End If
'other action
End With
If FirstName.Value = "" Then
MsgBox "You must enter a First Name."
ValidateData = False
Exit Function
End If
ValidateData = True
End Function

hello Jim that great but what about getting the code to refer to a
sheet while it is hidden for some reason it does not work!
Example:
Hello ,
I am having trouble with the GetData Sub I suppose. This is used in my

userforms to scroll through rows and columns refering to a specific
worksheet. Example: userform1 inputs data into sheet1 then userform1
wants to getdata to display inside of the approiate fields in the
userform. there are four command buttons first, previous, next and
last. there is a text box named rownumber that displays the rownumber
that is being viewed. As before it will work if I am inside the
corresponding sheet but if that sheet is hidden or not selected it will

not work. Would you be able to figure this out? Here is an example of
the code that I have stored in the userform.

The main goal is to have it work while the sheet it is getting data
from is
veryhidden!

__________________________________________________ ___________________________
Public LastRow As Long
Private Sub GetData()
Dim r As Long
Dim r1 As Range, r2 As Range
Set r1 = Worksheets("sheet1").Range("A1:A100").CurrentRegio n
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
Else
ClearData
MsgBox "Illegal row number"
Exit Sub
End If
If r 1 And r <= LastRow Then
FirstName.Text = r1.Cells(r, 1)


ElseIf r = 2 Then
ClearData
Else
ClearData
MsgBox "Invalid row number"
End If
End Sub
__________________________________________________ _________________

Private Sub ClearData()
FirstName.Text = ""


End Sub
__________________________________________________ __________________

Private Sub RowNumber_Change()
GetData
End Sub
__________________________________________________ _______________

Private Sub First_Click()
RowNumber.Text = "2"
End Sub
__________________________________________________ ____________________

Private Sub Previous_Click()
Dim r As Long
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
r = r - 1
If r 1 And r <= LastRow Then
RowNumber.Text = FormatNumber(r, 0)
End If
End If
End Sub
__________________________________________________ ____________________

Private Sub Next1_Click()
Dim r As Long
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
r = r + 1
If r 1 And r <= LastRow Then
RowNumber.Text = FormatNumber(r, 0)
End If
End If
End Sub
__________________________________________________ ______________________

Private Function FindLastRow()
Dim r As Long
r = 2
Do While r < 65536 And Len(Cells(r, 1).Text) 0
r = r + 1
Loop
FindLastRow = r
End Function

__________________________________________________ ___________________