Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.office.developer.com.add_ins,microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make userform work publically with hidden sheets?
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 |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.office.developer.com.add_ins
|
|||
|
|||
How to make userform work publically with hidden sheets?
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 |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.office.developer.com.add_ins
|
|||
|
|||
How to make userform work publically with hidden sheets?
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 |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.office.developer.com.add_ins
|
|||
|
|||
How to make userform work publically with hidden sheets?
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 |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.office.developer.com.add_ins
|
|||
|
|||
How to make userform work publically with hidden sheets?
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 |
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.office.developer.com.add_ins
|
|||
|
|||
How to make userform work publically with hidden sheets?
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 |
#7
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.office.developer.com.add_ins
|
|||
|
|||
How to make userform work publically with hidden sheets?
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 |
#8
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.office.developer.com.add_ins
|
|||
|
|||
How to make userform work publically with hidden sheets?
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 __________________________________________________ ___________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hidden Sheets | Excel Worksheet Functions | |||
Macro and hidden sheets | Excel Discussion (Misc queries) | |||
Number of worksheet tabs as cell value? ie 4 work sheets = 4 cell value | Excel Discussion (Misc queries) | |||
Open hidden sheets from a drop down list selection | Excel Discussion (Misc queries) | |||
make hidden window or workbook visible without specify the name | Excel Worksheet Functions |