Dear Torstein
I think something like this will help.
Make a Userform and add to it one Command Button, one Label and one Combobox. (Keep the default names until you are happy that it works)
Then add one Image control (set its Stretch property to True) and then copy it 25 times. (Try 3 to start with). This will create Image1, Image2, Image3 and so on. Make sute when copying that there are no gaps in the sequence of numbers. Place them somewhere on the right out of the way.
Set up each Image with the matrix picture you want. You can paste images directly or you can load a file from the Image property in the Properties box.
Paste this code into a module. :-
Option Explicit
Const QUESTIONS = 25 ' you must have this number of images
Const IMAGEROOT = "Image"
Private Sub UserForm_Activate()
Label1.Caption = 1
SetUpQuestion
End Sub
Private Sub CommandButton1_Click()
Dim nQuestionNumber As Long, nAnswer As Long, nRow As Long
nAnswer = Val(ComboBox1.Value)
If nAnswer < 1 Then Exit Sub
nQuestionNumber = Val(Label1.Caption)
CurrentImage(nQuestionNumber).Tag = nAnswer
If nQuestionNumber < QUESTIONS Then
Label1.Caption = nQuestionNumber + 1
SetUpQuestion
End If
If nQuestionNumber = QUESTIONS Then
With Sheets("Sheet1")
.Cells.Clear
For nRow = 1 To QUESTIONS
.Cells(nRow, "a") = nRow
.Cells(nRow, "b") = CurrentImage(nRow).Tag
Next nRow
End With
MsgBox "Finished"
End
End If
End Sub
Sub SetUpQuestion()
Dim oControl As Control, nIndex As Long
ComboBox1.Clear
ComboBox1.List = Array(1, 2, 3, 4, 5)
For nIndex = 1 To QUESTIONS
CurrentImage(nIndex).Visible = False
Next nIndex
With CurrentImage(Val(Label1.Caption))
.Visible = True
.Move 10, 50, 200, 200 ' image size here. remember Stretch
End With
End Sub
Function CurrentImage(nIndex As Long) As Control
Dim oControl As Control
For Each oControl In Controls
If oControl.Name = IMAGEROOT & nIndex Then
Set CurrentImage = oControl
Exit Function
End If
Next oControl
End Function
It's a bit basic, once you understand it you can add forward and back buttons etc and some labels etc.
Note the use of the Image Tag property to store the user inputs.
Regards
Derek Turner
England
+++
>________________________________
> From: Bhuvesh Devnani <bhuveshd@yahoo.com>
>To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
>Sent: Wednesday, 3 July 2013, 20:57
>Subject: Re: Vedr: Vedr: [ExcelVBA] Re: loop with userform
>
>
>
>
>Hi Torstein,
>
>Earlier, I was unable to understand whether you wanted to input through the user-form or through the sheets. But, later on I got your point, and, I must say that the project you are working is very much Great and I found it very Interesting. :-).
>
>God will help you in finishing your project.
>
>Regards,
>
>Bhuvesh.
>
>
>PSave a tree...please don't print this e-mail unless you really need to.
>A mail from Bhuvesh Devnani.
>
>________________________________
>From: Torstein Johnsen <sejohnse@yahoo.no>
>To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
>Sent: Thursday, 4 July 2013 12:20 AM
>Subject: Vedr: Vedr: [ExcelVBA] Re: loop with userform
>
>
>
>Thanks Lisa, thats an interesting solution.
>
>So far I'm OK with one picture on each sheet, but I will consider your way of doing it, if I'm going to do further changes!
>
>Torstein
>
>________________________________
>Fra: Green <1z@compuserve.com>
>Til: ExcelVBA@yahoogroups.com
>Sendt: Onsdag, 3. juli 2013 16.13
>Emne: Re: Vedr: [ExcelVBA] Re: loop with userform
>
>
>Torstein,
>
>You can put pictures on a userform. Do you need the users to see the pictures on the sheet? How about presenting the pictures one at a time on the userform and asking them to fill in checkboxes 1 - 5.
>The userform could have Previous - Next and Cancel buttons. The answers could be stored mabe in a global array or probably better on the sheet itself.
>
>Lisa
>
>Sent: Wed, Jul 3, 2013 2:32 pm
>
>Subject: Vedr: [ExcelVBA] Re: loop with userform
>
>Thank you Steve, your explanation gave me some new knowledge about userforms.
>
>I'ts hard for me to describe "my problem" in my poor english, but my concern was
>wether the loop continued running when the code from the commandbutton was
>started.
>
>And to Derek and your confusion:
>
>I want to show the user 25 pictures each on one sheet (by using a loop) , and
>have them giving an answer 1-5 based on that picture.
>
>I have made that using an inputbox, and my question was how to replace that
>inputbox with a userform which is easier to adapt.
>
>Perhaps I have increased the confusion - altogether I have learned something
>about userforms and that I have to do further studies on them!
>
>regards
>
>Torstein
>
>[Non-text portions of this message have been removed]
>
>[Non-text portions of this message have been removed]
>
>[Non-text portions of this message have been removed]
>
>
>
>
>
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (14) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar