Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
VBA UserForm Creation
- Thread starter sawali
- Start date
SkipVought
Programmer
Hi,
You made a statement, not a question.
What is your question?
Skip,
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
Just traded in my old subtlety...for a NUANCE!
![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
- Thread starter
- #3
Dear Sir,
I am creating the data entry userform to easy my work. for that i am tryin gto creat the user form using VBA Excel.
My aim is to creat the excel file with three sheets
sheet1 shall contain the data for input information (standard) value.
for Ex.
Product
Batch code.
shelf life
std. Qty.
Sheet2 shall contain the blank table (except Product) with column heads as
Product
Batch Code
Batch No.
Shelf Life
Mfg.
Exp.
Qty.
User
Date issue
Sheet3 shall contain the blank table similar to the sheet2 but totally blank and can use as data register entry.
Now i want to creat the userform includes the Combobox, Text Box, CMD Buttons.
1. Combobox1 should link with product details in sheet1 for input value.
2. as i select the product in the combobox1 the batch code of the same product shall appear in the text box1 using index/match function or similar,
3. after that the value entered in textbox2 shall be only Numeric 4 degits only,
4. TextBox3 and 4 shall be for Mfg. date and Exp. Date as Jan.2010, Dec.2013 respectively. here the date selection function should be available.
5. TextBox5 shall be for the Qty and the same shall be only in numeric, no digit limit.
6. cmd button1 shall be for validating the entries in the userform filled are complet and shall match the product row in the sheet2 and update the entries to the sheet form the userform using again index/match function.
7. cmd button2 shall append the entries from the userform to sheet3 and additionally shall put the sequential serial no. to the new entry in the first column.
8. The entries in the sheet2 can be overwritten because the respective cells are linked to the word file to print the respective format.
9. cmd button3 for printing the file link mentioned in the respective row updated in the sheet2 in PDF format using ADOBE PDF Printer and save the PDF file to the fixed path
10. This user form shall be password protected.
Dear Sir, Considering the above requirement can we design the form in VBA.
I have designed such document in Excel and using but it is complecated.
attached specimen.
I am creating the data entry userform to easy my work. for that i am tryin gto creat the user form using VBA Excel.
My aim is to creat the excel file with three sheets
sheet1 shall contain the data for input information (standard) value.
for Ex.
Product
Batch code.
shelf life
std. Qty.
Sheet2 shall contain the blank table (except Product) with column heads as
Product
Batch Code
Batch No.
Shelf Life
Mfg.
Exp.
Qty.
User
Date issue
Sheet3 shall contain the blank table similar to the sheet2 but totally blank and can use as data register entry.
Now i want to creat the userform includes the Combobox, Text Box, CMD Buttons.
1. Combobox1 should link with product details in sheet1 for input value.
2. as i select the product in the combobox1 the batch code of the same product shall appear in the text box1 using index/match function or similar,
3. after that the value entered in textbox2 shall be only Numeric 4 degits only,
4. TextBox3 and 4 shall be for Mfg. date and Exp. Date as Jan.2010, Dec.2013 respectively. here the date selection function should be available.
5. TextBox5 shall be for the Qty and the same shall be only in numeric, no digit limit.
6. cmd button1 shall be for validating the entries in the userform filled are complet and shall match the product row in the sheet2 and update the entries to the sheet form the userform using again index/match function.
7. cmd button2 shall append the entries from the userform to sheet3 and additionally shall put the sequential serial no. to the new entry in the first column.
8. The entries in the sheet2 can be overwritten because the respective cells are linked to the word file to print the respective format.
9. cmd button3 for printing the file link mentioned in the respective row updated in the sheet2 in PDF format using ADOBE PDF Printer and save the PDF file to the fixed path
10. This user form shall be password protected.
Dear Sir, Considering the above requirement can we design the form in VBA.
I have designed such document in Excel and using but it is complecated.
attached specimen.
SkipVought
Programmer
Yes.
Skip,
Just traded in my old subtlety...for a NUANCE!
- Thread starter
- #6
- Thread starter
- #7
I have designed the userform for the above.
Now pls. can you tell me how shall i link the combobox witht he excel sheet1 for input data.
and how to write the index formula for textbox to take the data matching the contents form combobox to sheet1.
Pls. can you guide me?
Thanks and regards
Sunil
Now pls. can you tell me how shall i link the combobox witht he excel sheet1 for input data.
and how to write the index formula for textbox to take the data matching the contents form combobox to sheet1.
Pls. can you guide me?
Thanks and regards
Sunil
SkipVought
Programmer
You have not provided suficient information.
What is the structure of your sheet?
What is the structure of your UserForm?
How do you want the UserForm to function?
Skip,
Just traded in my old subtlety...for a NUANCE!
- Thread starter
- #9
- Thread starter
- #11
SkipVought
Programmer
Search for Need File Storage? on this page for the link.
Skip,
Just traded in my old subtlety...for a NUANCE!
Skip, that link is "no longer active" for me.
sawali, I am not sure what you mean by: "how to write the index formula for textbox"
A textbox on a userform does not take a formula.
I will try to answer the first two items on your list.
1. Combobox1 should link with product details in sheet1 for input value.
A combobox (or a listbox) can take a data source from your sheet.[/color red]
2. as i select the product in the combobox1 the batch code of the same product shall appear in the text box1 using index/match function or similar,
Again, there is no native function that will do this. You will have to write your own. You will have to determine the logic and write the code.[/color red]
Gerry
sawali, I am not sure what you mean by: "how to write the index formula for textbox"
A textbox on a userform does not take a formula.
I will try to answer the first two items on your list.
1. Combobox1 should link with product details in sheet1 for input value.
A combobox (or a listbox) can take a data source from your sheet.[/color red]
2. as i select the product in the combobox1 the batch code of the same product shall appear in the text box1 using index/match function or similar,
Again, there is no native function that will do this. You will have to write your own. You will have to determine the logic and write the code.[/color red]
Gerry
- Thread starter
- #14
Dear Sir,
Mr. SkipVought,
Thanks for the valuable time spend for me, i found the code in different way that is find, add, amend, select and delete the existing entry or new addition.
i have tried to attach my file for your reference but it is not possible due to some error.
Actually i have prepared the data base file in excel and like to control the data entry using the userform and not directly in the excel because there are more than 20 colums to be filled for the data and the user may confuse to entry and my the earlier entry can be edited. to avoid all such incidents i am preparing the userform. I am new for the vba and preparing the same by taking help form cooperative peoples like you and found some guidelines, i will try first and if required will come back
Mr. SkipVought,
Thanks for the valuable time spend for me, i found the code in different way that is find, add, amend, select and delete the existing entry or new addition.
i have tried to attach my file for your reference but it is not possible due to some error.
Actually i have prepared the data base file in excel and like to control the data entry using the userform and not directly in the excel because there are more than 20 colums to be filled for the data and the user may confuse to entry and my the earlier entry can be edited. to avoid all such incidents i am preparing the userform. I am new for the vba and preparing the same by taking help form cooperative peoples like you and found some guidelines, i will try first and if required will come back
Try googling for
excel userform examples
Post back here with the code you have tried and what doesn't work about it.
I looked briefly at the idea of userforms for a project of my own. I concluded that for my purposes, in order to validate data entry a lot of code would be needed. So I decided upon a different approach.
This is how my alternative approach might be applied to your situation:
Use Data, Validation to control what users can enter (no code needed)
Use VBA events to switch protection on and off so users can only change rows you want them to be able to change.
So, each time the workbook is opened some code would unprotect the workbook, select all populated rows in your database and lock them, protect the workbook.
It could also add x number of blank rows complete with formulae, data validation and conditional formatting.
If a user wanted to modify an existing record you could provide code to enable that if you wished.
In terms of some of your listed requirements:
1. Use data,validation and named ranges
2. as you say
3. Standard data validation rule: Whole number between 1000 and 9999. If you need values such as 0001 then its a bit more complex.
6. You could still use buttons for this - attached to the worksheet. Also you could use the BeforeSave event to ensure that incomplete items could not be saved. Or if you did allow saving then you could flag the row as incomplete and make sure that those rows would not be locked by your on-Open macro.
Gavin
excel userform examples
Post back here with the code you have tried and what doesn't work about it.
I looked briefly at the idea of userforms for a project of my own. I concluded that for my purposes, in order to validate data entry a lot of code would be needed. So I decided upon a different approach.
This is how my alternative approach might be applied to your situation:
Use Data, Validation to control what users can enter (no code needed)
Use VBA events to switch protection on and off so users can only change rows you want them to be able to change.
So, each time the workbook is opened some code would unprotect the workbook, select all populated rows in your database and lock them, protect the workbook.
It could also add x number of blank rows complete with formulae, data validation and conditional formatting.
If a user wanted to modify an existing record you could provide code to enable that if you wished.
In terms of some of your listed requirements:
1. Use data,validation and named ranges
2. as you say
3. Standard data validation rule: Whole number between 1000 and 9999. If you need values such as 0001 then its a bit more complex.
6. You could still use buttons for this - attached to the worksheet. Also you could use the BeforeSave event to ensure that incomplete items could not be saved. Or if you did allow saving then you could flag the row as incomplete and make sure that those rows would not be locked by your on-Open macro.
Gavin
- Status
Similar threads
- Question
- Locked
- Question