Dear
I am making one userform in excel which has around 64 textboxes for entry into excel sheet. Now i need your help in writing a code that if run will check all field textboxes in userform (Frame ) and then save data to respective range in excel sheet. I wrote a code but that is copying only value of first text box on frame to all cells in sheet. i am attaching snapshot and excel for you to help me.
Hi there,
what You code do, is:
if control = textbox and its value<>"" then put its value to all columns in specified row.
So when You fill more rows on Your userform, You will see that it will put value from each textbox to one row on Your sheet.
You have to specify the destination for each textbox:
textbox5 to col A
textbox6 to col B
.
.
.
and againg:
textbox13 to col A
textbox14 to col B
.
.
.
You better name Your texboxes. For example: Textbox5 to Text1A, Textbox6 to Text1B etc and in the loop just extract the column (A,B) form the textbox name to specifi the column to which should the value be pasted.
Does it make sence for You?
Regards
Miroslav R.
(If You like my solutions, feel free to add reputation.)
You really gave good idea and working fine for saving data into sheet. But i have another issue, i will use the same frame and text boxes for other three options given in frame i.e with same text box value will be saved in column I to P if optionbutton2 is enabled. So can you help in how can i offset each range by 8 columns.
Hi there,
for this case, You can proceed this way:
name the texbox like 'Text11' (first digit = 1st row of textboxes in Your userform, second digit = 1column)
extract the column number form textbox name (maybe in this way):
xcol = cint(right(ctl.name),1)
define offset according to opt btn:
for example:
and replace the destination range definition:
Please Login or Register to view this content.
with this definition:Please Login or Register to view this content.
where:Please Login or Register to view this content.
Y+1 stands for row number
xcol+xofset stands for column number
You can manage like that more optionbuttons, but take care of the textbox names.
(Note that I described only the possible way, not tested)
hi, as per you, i could able to now change column but another problem is after every 8 controls, row number should change. How to handle it.
Basically can we write some code which change row number whenever control value increased by 8.
i managed by adding another count variable.
okay...
but as mentioned earlier, take care of the textbox names. If You have more textboxes alsom for other input data, create unique string for those 64 textboxes and check the unique string in name instead of typename.
Best of luck
one more issue now, as i add more rows, so textbox count is going to three digits and code is working for picking last two digits. Any idea for that.
Last row textbox names are coming as text110, where 1 is col and 10 is row count and code xcol = cint(right(ctl.name),1) will give result as 0 instead of 10.
Okay...
so play a little bit with your code and replace the digits for column number in textbox name with letter for respective column => 'Text1A' and extract it in loop =>. This solution will give You possibility for 26 rows in Your userform. I hope it enought.Please Login or Register to view this content.
If You applied offset, You can manage that:
I understand that it is a lot of work to rename those 64 textboxes, but You had only 8x8 array of textboxes in Your example file...Please Login or Register to view this content.
double post
thanks, i used another method by checking len of control name and using if condition at border (i.e when len increased with another digit) it worked.
That is also a good solution!
i am stuck at one more point, need your help. Attaching final updated sheet for review. Now when i run form first time all values goes perfectly to designated fields in sheet but at next load of form, values of 2 combo boxes and 4 text boxes are not properly recorded. Suppose during first run, i filled 4 rows again option 1, 2 rows in option 2, so as per coding as option 1 and two belong to same report so all combo and text box (frame 2) filling all 4 rows with data. Now when form is loaded next time if i fill 1 row in option 1 and 2 row in option then logically combo and text box (frame 2) should fill data against row 1 only and leaving row 2 empty.
Hi,
so if I dod not misunderstand, let me summarize the situation as it is now:
scenario1:
click any of opt btns - type first set of values
when save, everything is OK.
scenario2:
click any of opt btns - type first set of values
click different opt btn - type second set of values
when save, only the second set is recorded...
This is due to this reason:
when You click second opt btn, You clear all all textboxes with 'clfra' WITHOUT recording their values to sheet or in some variable. Therefore only the second (or last ) set is recorded. The first set is cleared.
So as I can see, You have 2 options:
1) call the save procedure before 'clfra' when You click second (other) opt btn.
2) record values to array (or public type or class or variable...) before other opt btn click, and write from variable to sheet when pressing save btn all sets in one.
Or also there is an option to redesign Your userform, add new multipage and by clicking opt btn, specified page will be activated. But that means to have 4x more textboxes on Your userform, and that is definitelly not confortable to handle.
(I would prefer 1st option, but keep in mind that Your row for new entry has to be defined with userform activation).
Is it understandable? I hope so...
No, it is not the scene. Case is
scenario2:
click any of opt btns - type first set of values
click different opt btn - type second set of values
when save, first set & the second set is recorded...but not values of comboboxes and 4 text boxes in frame 2 against all first and second set values.
If you see in attached file sent, for row 7 & 8, values from AG7 to AL8 are not captured whereas they should be. (They are captured for Row 6 & 5 only against second set values)
Hi,
I debug You code, and found strange loop definition:
Look into Your 'basic' macro:
This code should record Your text&combo boxes values record to worksheet, but what is this loop?Please Login or Register to view this content.
When I debug:Please Login or Register to view this content.
x=6, ic=6 => a=0 and the loop:
from a = 7 to 6... ??? that is not a valid loop...
do you have some better idea to feed data. i am not able to make a universal logic.
Hi,
what is the variable 'ic'?
I did not want to disrupt Your logic of the code, so pls provide more details, what should the 'basic' macro do...
i have changed few lines and added few more to make code workable for me. Now it is running fine. For your info, IC is variable picking up max value of each option button fed data during last form unload and basic is saving values of frame2 and frame3 controls into sheet against frame4 textboxes.
Okay,
happy that You fix that.
Best of luck and have a nice day
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks