Need some help modifying my macro to loop different controls

[Pimp_mentality]

Good day to you all,

Here is the deal I have a UserForm which I use to input and edit data in a worksheet. Currently the Userform only uses Textboxes and accommodates up to 200 controls. I am working on another which will accommodate more controls, This time I would like to use Textboxes and ComboBoxes, I would also like to use Checkoxes and option boxes too. So I would greatly appreciate some assistance with this little project.

Here is an example of code I use in my aforementioned project which I was trying to modify to accomplish the same thing only this time with ComboBoxes and textboxes as controls.

Please Login or Register  to view this content.

Any assistance re this matter, given the significance of the project I am undertaking, would be GREATLY appreciated.

If you guys would like to see the work sheet I was experimenting with I can provide same on request.

[Pimp_mentality]

Ok been doing some reading and experimenting and found something that works but is very limited. See attached code:

Please Login or Register  to view this content.

by putting both textboxes and comboboxes in an array and treating same as a variant i accomplished the goal.

However, this would be very inefficient when dealing with many textboxes and comboboxes in the userform and i am yet to figure out how to incoperate checkboxes and optionbutton but the research continues.

[Pimp_mentality]

I sincerely hope that my query is not inappropriate or in someway (that I am genuinely not aware of)
insensitive to the time and efforts of forum contributors. If this is the cause please point this out so I my convey my apologies and adjust my forum conduct accordingly.

[xlnitwit]

Hi,

I do not entirely follow where you are having a problem but I would suggest you use the Tag property of each control to store the letter of the column it should populate. Your code then only needs to loop through all the controls and populate the required cell for each one.

[Pimp_mentality]

good day,

If it is not too much trouble, could you please provide me of a visual example of what you are suggesting as I am a visual learner and since I am fairly new to vba programming visuals are most helpful to me.

[xlnitwit]

Of course. Something along these lines

Please Login or Register  to view this content.

[Pimp_mentality]

Good day xlnitwit

Thanks for your interest thus far it has helped a lot and is much appreciated.

I have attempted to apply the method described above but I am not able to get it to save to the worksheet.

i have attached a worksheet I have been experimenting with. If you are able, good you give it the once over and advise re what i am doing wrong.


note my actual project will be required to handle a whole lot more information. this form is just for me to experiment with

[xlnitwit]

Hi again,

You have not put any column letters into any of the Tag properties of your controls (in the properties window in the VB Editor).

I did notice that the loop to populate your year list can be greatly simplified. You may replace this loop

Please Login or Register  to view this content.

with simply this

Please Login or Register  to view this content.

[Norie]

Are you going to have multiple pages with same controls layed out in the same way for each student?

[Pimp_mentality]

Yes Norie, in my actual project i utilize MultiPages which contains varied information for each student.

To xlnitwit, to be very honest with you, you are the one that has introduced me to the concept of Tag Properties, I am now doing some research to see if I can figure it out myself.

[Norie]

Do you really need a separate page for each student?

Have you considered having one page in which you could enter/alter/delete student data?

On that page you could navigate through student records with before/after buttons and/or some sort of search functionality?

[Pimp_mentality]

I am gonna post a strip down example (void of any code) of one of my projects that I am currently working on so the visuals will give you'll an idea of what I am trying to accomplish.

As you will observed i have used only comboboxes (in other forms i use only textboxes) as i only know how to create a loop which would accommodate the one type of control hence my original querries.
since i am trying to create something which is easy to manipulate (i.e) easy to input and edit) using all the control types (textbox, comboboxes, optionbuttons checkboxes etc.) would make this process MUCH easier.

I have been reading up on tag properties and this concept sounds like exactly what I need to learn (here is where i was reading http://www.ozgrid.com/forum/showthread.php?t=12119)to accomplish my goals.

[Pimp_mentality]

here my stripped down form

[sanram]

I have googled for your problem and find this link helpful for this. You can check that link also.

http://www.ozgrid.com/VBA/control-loop.htm

As you have used Multipage so you need something like that :

Please Login or Register  to view this content.

[Pimp_mentality]

I have been trying to use the method provided to loop through all control but I keep getting "block if without end if" error. Now to the best of my knowledge this means that I am missing an "end if" statement somewhere but from what I see I have left none out.

Please Login or Register  to view this content.

Also can xlnitwit or any other contributor, with the requisite knowledge, point out what would i need to write in the tag properties in order to properly utilize the following codes

Please Login or Register  to view this content.

[Pimp_mentality]

I don't know if I am asking too much (if I am PLEASE let me now).

I am requesting that one of the contributors verse in Tag Properties modify my Save Button code in my Sample1 UserForm (which I have attached again) using this method. It is my hope that once I have seen a working example i will be able to adapt it to my larger project.

As I intimated earlier I am a Visual Learner

[Norie]

There are End Ifs missing here.

Please Login or Register  to view this content.

Each If should have an End If and the ElseIf should be an If.

Please Login or Register  to view this content.

By the way, do you actually have controls named OptMale and OptFemale?

Also, are the variables declared here actually existing controls on the userform?

Please Login or Register  to view this content.

If they are there's no need to actually declare them.

[Pimp_mentality]

well i was using OptMale and OptFemale? to represent the corresponding options buttons as when either is selected i want the corresponding value (i.e. male or female) to be saved to the relevant cell when my save button is clicked.

i was not sure if what i did was correct as i was experimenting

[Norie]

Do you have option buttons named OptMale and OptFemale?

[Pimp_mentality]

I have Option buttons labeled male and female but they retained their optionbutton1 and optionbutton2 designations

[Pimp_mentality]

After doing some more reading, here is my latest incarnation

Please Login or Register  to view this content.

it runs but nothing happens when i click save

[Pimp_mentality]

As it is clear that i am fumbling about I am humbly requesting someone more adept than myself to make the required modifications to my sample sheet so that I may learn from a working code so as to apply same to my much larger project.

[Norie]

As far as I can see this is going to reset all the controls.

Please Login or Register  to view this content.

If you can attach a workbook with a sample layout of the headers and some dummy data it would help.

[sanram]

I have been trying to use the method provided to loop through all control but I keep getting "block if without end if" error. Now to the best of my knowledge this means that I am missing an "end if" statement somewhere but from what I see I have left none out.

Please Login or Register  to view this content.

You missed one End If here. See the correction below:

Please Login or Register  to view this content.

If you don't use Exit Sub on the line of If statement, then you must have to use End If.

[sanram]

After doing some more reading, here is my latest incarnation

Please Login or Register  to view this content.

it runs but nothing happens when i click save

Here you have used Exit Sub after End if. You have put them before End If. See the below:

Please Login or Register  to view this content.

[sanram]

I think you are facing a problem with the usage of Exit Sub. If you use Exit Sub as below then you don't need End If:

Please Login or Register  to view this content.

But if you use as below then you need End If:

Please Login or Register  to view this content.

[Pimp_mentality]

To NORIE

I have attached another sample for your perusal and assistance.

Oh, I use multipage in my sample simply because my lager project utilizes a few multipages. i also included the image control as my larger project will account for images as well. Haven't quite figured out the image part yet, but one problem at a time I always saw

[Pimp_mentality]

To Sanram I have made the adjustments suggested re end if and exit sub lines. Still having issues.

The thing is as I am still learning the basics of looping and vba coding in general I have never used more than one control which i have some understanding of (see my very first post) as I have never tried to loop multiple controls and I have not to this point seen a complete userform(complete lines of code) which incorporates multiple controls. Though I have done much research and have gotten much useful advise (especially re tag properties), I have been getting just advise re specific lines of the entire code. So when I try in cooperating the parts I get the whole is not meshing. This is why I created a very light UserForm (sample3) which I hope a contributor would do me the favour of modifying my botched attempts at coding (i.e. a make it a working UserForm). That way I can see how its done from start to finish and use that as my point of reference going forward.

So hopefully that type of assistance I am requesting is not asking too much or crossing a line of which I am not aware (if it is, again, please let me know so I can adjust my forum conduct accordingly)

[Pimp_mentality]

Been doing some reading on tag properties and looping through multiple controls to save data to a work sheet but it seems that re looping through the controls and cant seem to find an actual example of a looping code just examples dealing with just a few controls (which wouldn't necessarily require a loop).

This was the best reading material I found http://www.globaliconnect.com/excel/...=79&Itemid=475.

trying to apply some of the concepts for my needs but having a rough go at it.

re tag properties this seems to be a concept for the advanced user as all materials i found assume that you are already quite verse with all concepts re vba coding

[sanram]

Please see the attached file. May be it can help.

[sanram]

If you need your desired sequence then you have to add those controls sequentially. See this attachment. I have reorganized your controls for that. Also I have set TextBox4 and ListBox1 Disable (Enable=False) from properties to exclude them from loop.

[Pimp_mentality]

Thanks a million sanram. I have been examining the code and now I see my errors and am having a much clearer understanding of how to Loop multiple controls.

I am now going to experiment with my codes for my edit button and my After_update event (my previous forms were written with an event(Sub) which allowed for the user to click on a name (from the many that populated the listbox1 on form initialization) and it would populate all the controls with the relevant data from the worksheet for the student selected.

Thanks again to all forum contributors and I sincerely hope i can call on you'll again when next i become stomped.

[Pimp_mentality]

Quick question can you please explain to me to reason for the highlighted portion of code (-3)

does it have to do with the column count in the userform initialization event?

Please Login or Register  to view this content.

[sanram]

Quick question can you please explain to me to reason for the highlighted portion of code (-3)

does it have to do with the column count in the userform initialization event?

Please Login or Register  to view this content.

It's feeling good that the code helps. The above code is for the age. On that listbox you have data as 2yrs. So I remove yrs during placing the data. As you have only one listbox enabled there, so you need not to check that which listbox is this. Let me know if you are not clear.

[Pimp_mentality]

Ok i see.

Another thing, I spent the better part of last night trying to get my other codes i.e. code for my edit data button and my 2 after update events. see pics

[Pimp_mentality]

The pics shows my code when it only had to account for textboxes but since the form has other controls to consider, especially the option buttons and check boxes I am lost re adjusting the codes to match so the end result is the same.

the "end result is" when the for loads Listbox1 highlights all students currentlly in the database. When you click on a student all controls are updated with the relevant data from the worksheet (this would ideally include the relevant checkboxes and combobxes being check automatically to correspond with the data (so when you click on a female the option box would select female and so on.

when it was just textboxes my code worked wonderfully but now with all the new controls being incooperated i'm now lost

[sanram]

I am not clear about your question.

[sanram]

The pics shows my code when it only had to account for textboxes but since the form has other controls to consider, especially the option buttons and check boxes I am lost re adjusting the codes to match so the end result is the same.

the "end result is" when the for loads Listbox1 highlights all students currentlly in the database. When you click on a student all controls are updated with the relevant data from the worksheet (this would ideally include the relevant checkboxes and combobxes being check automatically to correspond with the data (so when you click on a female the option box would select female and so on.

when it was just textboxes my code worked wonderfully but now with all the new controls being incooperated i'm now lost

I have updated the code as per my understand. Please let me know if this is all you want.

[Pimp_mentality]

Sir this is exactly what I wanted. i am again indebted to you

I am examining the code for the Listbox1 click event you wrote and I am now seeing the difference re handling the different controls.

My last 2 question are as follows.

You would notice in the sample I included an image control as the intent was to have an image of each student. So when you click the name in the listbox the image of the corresponding student is loaded along with all the other relevant info (as is now happening). My questions are:

1. How do I link the image to the data so it is loaded with all the other data on the Listbox1_click event.

2. And is there a way to, when inputting data via the UserForm, to input the image path via the userform (all the reading I have been doing on the subject seems to suggest that the image path must be inputted to the worsheet and loaded from the worksheet to the userform.

[Pimp_mentality]

Also my previous code used to update existing data looked like this

Please Login or Register  to view this content.

This allowed me to edit existing data and have it save it the same location within the worksheet without disrupting the order which they appeared.

This no longer works, obviously, I am currently examining the new codes to see how best to make alteration
to accomplish this goal as well

[Pimp_mentality]

Ok. this is the code i came up with but i cant seem to get it to do what i want it to do

Please Login or Register  to view this content.

[sanram]

Ok. this is the code i came up with but i cant seem to get it to do what i want it to do

Please Login or Register  to view this content.

Update PI will be same as Save PI. You have to change the code of a only.

[Pimp_mentality]

it seems like your statement was incomplete, could you explain again please.

[sanram]

it seems like your statement was incomplete, could you explain again please.

See the attachment.

[sanram]

See this attachment. It is a completed version of your all requirements.

[Pimp_mentality]

Sanram thanks a million, you have provided me with exactly what I need to finish my main project. You have also indulged my ignorance and for that I am also grateful.

There are other little feature that when I finish my project i would like to add like
Spell check
Printing forms etc.

But I have been doing extensive reading and should be able to implement those things when the time comes. hopeful if i again become stomped and again come hat in hand I will again find favour in your generosity

[Pimp_mentality]

ooooh one last thing, when you click on the image box and the file explorer opens up if you don't select a file and instead click cancel you get a "run time error". how can i prevent that from happening?

[sanram]

See this attachment. Here I have adjusted that error and on Address when you type it will check spelling.

[Pimp_mentality]

Thanks again Sanram, much appreciated. You have set me well on my way.

[sanram]

Thanks again Sanram, much appreciated. You have set me well on my way.

You are most welcomed.

[Pimp_mentality]

good day Sanram

I notice when going through the sample you provided when inputting data if no picture is inserted whenever you attempt to save the data to the sheet a runtime error appears. This is an issue as
not all data imputed will have a pic attached

[sanram]

You need little adjustment for that. You have to set the variable PicPath whenever it should like be. I am little busy now. I will send you the code after some time.

[Pimp_mentality]

Ok thanks whenever you are able

[sanram]

Here is the file.

[Pimp_mentality]

Thanks again much appreciated

[Pimp_mentality]

Good day Sanram,

I am having some issues i hope you could assist with.

1. Whenever you try to save an entry without an image entered into the image box you get an error message (the data is save to the sheet though).

2. When editing data is there away I could delete a photo and update, leaving the imagebox blank, maybe a way to clear the image box via button click?