PDA

View Full Version : Solved: Populate userform from selection of another userform



Dodgeitorels
08-30-2009, 10:49 AM
Hi,
I have searched forums for an answer but not really sure what I am looking for.
I have a worksheet with all my data. I have created a userform called RBsonginfo. From this form I have a sort button which will bring up another form called sortby. From there I make a choice and that will bring up another userform depending on my selection. I need to populate textboxes in rbsonginfo userform by using appropriate data that matches choice from last userform. What I mean is if I click button named sort that will bring up sortby userform. On the sortby userform I select a radio button which will then bring up a corresponding userform, so If I select radio button Genre the sortbygenre userform will show and then I select say southern rock radio button. When I select that button I want all the data from worksheet that has southern rock in cells in column G to populate all textboxes on RBsonginfo userform. There is also a skill level section on rbsonginfo userform but I will worry about that part later. I hope I have been clear as to what I am trying to do. Attached is my file but I have removed the modules that I thought would do the trick. I don't want everything done for me. Just a sample and I think I can figure it out from there.
Thank you very much for any and all help.

1727

mdmackillop
08-30-2009, 03:16 PM
I've added some code for Genre/Alternative and a SpinButton. Not sure if this is the way you want to go though.

Dodgeitorels
08-30-2009, 03:31 PM
thank you mdmackillop, it is a start for me and I really appreciate your time and very quick response. Will let you know progress as I go along
Again, Thank You.

Dodgeitorels
08-31-2009, 01:28 PM
Ok, I have looked at what you have done for me and it is quite neat to me. However, instead or sorting info on worksheet I want to take corresponding info from worksheet and place into rbsonginfo userform textboxes. So, where you set genre and alternative I want to be able to use spin buttons to go through the list of only alternative songs (which will be displayed in textbox next to song on rbsonginfo userform), each time I stop at a song results in changing all textboxes to match info from worksheet. I want to have the rbsonginfo userform to be showing on a blank worksheet so we aren't seeing sheet2 in background.
It is probably asking too much but I really would like to learn to do this and would appreciate any guidance towards achieving this goal. Again thank you for what you have done and it is greatly appreciated as I have learned something new already.

mdmackillop
09-01-2009, 04:44 PM
You can populate all the textboxes when the spinbutton changes

Private Sub SpinButton1_spinup()
Dim Rw
Rw = arr(SpinButton1)
TextBox1 = Cells(Rw, 2)
TextBox2 = Cells(Rw, 3)
TextBox3 = Cells(Rw, 4)
'etc.
End Sub

Dodgeitorels
09-06-2009, 12:03 PM
again, ty very much. I have got all boxes loading properly with each selection. I do get an error 9 out of range when I get to end of any list when using scroll bars. Any idea what I would look for?

Dodgeitorels
09-09-2009, 09:09 AM
In Post above I meant to say spin buttons not scroll bars.My apologies. I am at wits end trying to figure this out. Please help me.

Dodgeitorels
09-10-2009, 09:27 AM
I think it has something to do with ReDim Preserve in module but just can't figure it out. I read that the spin up will be fine but the spin down cannot be redimensioned. How does that work?

mdmackillop
09-19-2009, 02:35 AM
Add a line to limit the Spin Button Max value

Sub GetRows()
Dim rng As Range
Dim i As Long
Set rng = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
ReDim arr(rng.Cells.Count)
For Each cel In rng.SpecialCells(xlCellTypeVisible)
arr(i) = cel.Row
i = i + 1
Next
ReDim Preserve arr(i - 1)
RBSongInfo.SpinButton1.Max = i - 1 'Added
End Sub

Dodgeitorels
09-19-2009, 04:13 AM
Thank you very much, once again. I was playing around with everything I could try last nite and found that if I counted the rows and then went in and manually changed the spin button Max property to match number of sorted rows it stopped the error. Then was fighting trying to find out how to do it in the macro with no success. Then, low and behold, a genius (mdmackillop) puts the answer here in the post.
I will add this line to my actual file when I get home from work and post here so anyone can have the results.
Again, thank you very much mdmackillop.

Dodgeitorels
09-19-2009, 02:56 PM
Okay, thanks to you mdmackillop, I almost have what I am after. I still have 2 things that are baffling me. First is, when I run rbsonginfo userform (whether using sort button or just starting out using spin buttons) textboxes are filled with data from second row in list. I have tried changing everything I can think of which just causes me errors. The second thing is a bit more complicated, probably way to complicated for someone of my skill level. I have a sheet called images that houses 8 images. on sheet2 I have columns J thru N which have numbers matching image names. When I use rbsonginfo userform I would like to take the value of whichever cell I am sorting by and display the corresponding images to the image boxes on rbsonginfo userform.
So if I sort by song and select 123 I should see the image in all 5 image boxes on userform.
Sorry if I am not clear as I have confused myself trying to explain this. Any help would be greatly appreciated. Thanx in advance. And yes I know some of my coding is probably the long way around but it works so I dont want to mess with it till I learn more (a whole lot more) about VBA. :) well I have also found 1 more prob. When I try to sort by location I cannot get it to use the criteria asked for such as just RB1 or RB2 or DLC.

Dodgeitorels
09-20-2009, 02:09 PM
Is this the basic idea for the image problem I have? This was taken from another post that you had responded to mdmackillop.



Private Sub TextBox1_AfterUpdate()
For i = 1 To 12
Me.Controls("Image" & i).Visible = False
Next
tmp = (CLng(TextBox1) Mod 12)+1
Me.Controls("Image" & tmp).Visible = True
End Sub

Dodgeitorels
09-21-2009, 07:07 AM
Still haven't figured out the image problem. See sheet "Images" in attached file to see basic idea of what I am trying to accomplish. Thanx for any and all help. I see you have been busy helping a lot of others mdmackillop, so I don't expect a rapid response. I admire how you are there to give everyone help. I have been looking through posts to try to give back some of the help I have received but, let me tell ya, all the posts I have seen are way beyond my comprehention. Someday I will be able to give back though. :)

mdmackillop
09-21-2009, 05:36 PM
A partial solution
This should show the first item in the form
Problem Pictures
1 You need to save them as bmp files
2 For some reason LoadPicture is causing an error, so I've commented it out. It may work for you but I suspect too many controls/too much code on the Userform.

mdmackillop
09-21-2009, 05:37 PM
A simpler Pics file that does work

Dodgeitorels
10-03-2009, 12:49 PM
Ok, after much work and scratching my head, I decided to start all over since I couldn't solve the image issue. I finally solved the image issue and have everything working except for the "sort by location" part. Any way I try it will sort it proper on work sheet but will not load userform with sorted info. Thank you very much mdmackillop for all your help. you gave me some answers straight out and definitely pointed me in the right direction for the rest. If I can solve the sort by location issue I will be able to mark this thread as "SOLVED". The following files are the working files. The images must be placed in "C:\RBPics" folder for userform to work properly. If anyone can help me solve the "sort by location" issue I would be greatly indebted to you. Thank a heap in advance.

Dodgeitorels
10-07-2009, 10:20 AM
mdmackillop, I would like to take this opportunity to thank you for all the great help you have unselfishly given. At times I thought I was being ignored for asking a stupid question, but that just made me try that much harder and was able to overcome all of my issues with VBA. I am indebted to you my friend. Attached is my finished working file (which may not be much to most but to me it was a great accomplishment). The images must be place in a folder called "RBPics in C drive to work in userform. Again thank you very much and I hope this file may hold some answers for others that need them. I understand that there is a lot of tweaking that can be done to this file such as shortening things up and such but for now it is good enough for me. :beerchug: :friends:

mdmackillop
10-07-2009, 02:24 PM
Glad you got it working. I had a look at the problem, but was short of time. One small point, you might want to reset the Spinner to the first visible row value for "neatness" when you close the Sort form.

Dodgeitorels
10-07-2009, 04:28 PM
I understand the time thing, seems everywhere I look you are answering questions :bug:

I will try to figure out what you are talking about and get it sorted. Keep up the good work. and again..... Thank you very very much.:friends:

Dodgeitorels
10-08-2009, 05:39 AM
ok, so after 10.5 hours of searching and trying different codes in different areas I am unable to reset the Spinner to the first visible row value for "neatness" when I close the Sort form. Please give me some guidance. Thanks in advance.

Dodgeitorels
10-09-2009, 04:53 AM
I figured out how to reset spinner so all is neat now. Attached is working file and pics. Pics must be placed in a folder called "RBPics" in "C" drive. Thanks again mdmackillop.