Thanks Igold!
I made a 'problem workbook' to explain the problem.
The program opens with the first Userform. clicking the button 'Generate random series' does so in a row on the worksheet and the frame 'Indexed series preferred?' activates. pushing 'Yes' lists the periods above each of the random numbers generated (already in the row above the series) in the list beneath. Clicking a period in the list indexes the random number series on the random number of that period and set the series in a column on the worksheet.
Here is the problem! The procedure 'Private Sub lstIdxJaar_Click()' executes only once.
Private Sub lstIdxJaar_Click()
mkIdxReeksen (lstIdxJaar.List(lstIdxJaar.ListIndex))
lstIdxJaar.Clear
Range("B6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("S9").Select
If Not IsEmpty(Range("T9")) Then
Selection.End(xlToRight).Select
Set rangex = Selection
Else
Set rangex = Selection
End If
Range(Cells(9, rangex.Column + 1), Cells(9, rangex.Column + 1)).Select
' Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Invulbladcp.Hide
nogGrafofSluiten.Enabled = True
nogGrafofSluiten.knpNogGraf.Enabled = True
nogGrafofSluiten.knpEnd.Enabled = True
nogGrafofSluiten.Show
End Sub
Continuing my explanation. Pushing the the 'No' option in the frame 'Indexed series preferred?' sets the series in a column on the worksheet.
The button 'Another random series?' on the 2nd Userform activates the 1st Userform again and a new series can be generated. But, after indexing a series once, it is impossible the index another series because the procedure 'Private Sub lstIdxJaar_Click()' executes but once! How is that possible?
Thanks a lot if you can help me!
Now I show the userforms and the code directing the program.
Private Sub knpSeries_Click()
RndmReeks
fraIndex.Enabled = True
optIdxJa.Enabled = True
optIdxNee.Enabled = True
fraIndex.SetFocus
End Sub
Private Sub lstIdxJaar_Click()
mkIdxReeksen (lstIdxJaar.List(lstIdxJaar.ListIndex))
lstIdxJaar.Clear
Range("B6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("S9").Select
If Not IsEmpty(Range("T9")) Then
Selection.End(xlToRight).Select
Set rangex = Selection
Else
Set rangex = Selection
End If
Range(Cells(9, rangex.Column + 1), Cells(9, rangex.Column + 1)).Select
' Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Invulbladcp.Hide
nogGrafofSluiten.Enabled = True
nogGrafofSluiten.knpNogGraf.Enabled = True
nogGrafofSluiten.knpEnd.Enabled = True
nogGrafofSluiten.Show
End Sub
Private Sub optIdxJa_Click()
vulLstIdxJaar
lstIdxJaar.Enabled = True
lstIdxJaar.SetFocus
' fraIndex.Enabled = False
End Sub
Private Sub optIdxNee_Click()
Dim rangex As Range
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("A9").Select
If Not IsEmpty(Range("B9")) Then
Selection.End(xlToRight).Select
Set rangex = Selection
Else
Set rangex = Selection
End If
Range(Cells(9, rangex.Column + 1), Cells(9, rangex.Column + 1)).Select
' Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Invulbladcp.Hide
nogGrafofSluiten.Enabled = True
nogGrafofSluiten.knpNogGraf.Enabled = True
nogGrafofSluiten.knpEnd.Enabled = True
nogGrafofSluiten.Show
End Sub
Private Sub userform_Initialize()
Invulbladcp.Enabled = True
knpSeries.Enabled = True
knpSeries.SetFocus
fraIndex.Enabled = False
optIdxJa = False
optIdxJa.Enabled = False
optIdxNee = False
optIdxNee.Enabled = False
End Sub
Private Sub knpEnd_Click()
nogGrafofSluiten.Hide
End
End Sub
' n o g G r a f o f S l u i t e n n o g G r a f o f S l u i t e n
Private Sub knpNogGraf_Click()
'1- initialize arrGraf
arrGraf = resetGraf
'2- initialize userform
'3- en nog even het aantal grafieken (nieuwe tabs in het grafiekenwboek)
' tellen
GrfTel = GrfTel + 1
' Invulblad.ActiveControl
Invulbladcp.Enabled = True
Invulbladcp.fraIndex.Enabled = False
Invulbladcp.optIdxJa.Enabled = False
Invulbladcp.optIdxNee.Enabled = False
Invulbladcp.optIdxJa = False
Invulbladcp.optIdxNee = False
Invulbladcp.knpSeries.Enabled = True
Invulbladcp.knpSeries.SetFocus
nogGrafofSluiten.Enabled = False
nogGrafofSluiten.Hide
Invulbladcp.Show
End Sub
And here is the in a module with procedures to let the program work.
Sub RndmReeks()
Range("B2").Select
ActiveCell.FormulaR1C1 = "=RAND()*1000"
Range("B2").Select
Selection.Copy
Range("C2:BI2").Select
ActiveSheet.Paste
Range("B2:BI2").Select
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
False
End Sub
Sub vulLstIdxJaar()
Dim i As Integer
Dim sprong As Integer
With Invulbladcp.lstIdxJaar
For i = 1324 To 1384
.AddItem i
Next
End With
Invulbladcp.lstIdxJaar.ListIndex = -1
Invulbladcp.lstIdxJaar.Enabled = True
Invulbladcp.lstIdxJaar.SetFocus
End Sub
Sub mkIdxReeksen(idxjaar As Long)
Dim idxwr As Double ' jaarcijfer ophalen van het gekozen indexjaar
Dim tl As Integer ' doorloopt geselecteerde reeksen
Dim tlper As Integer ' doorloopt de periodes in de reeks
Dim klbegin As Long ' de beginkolom van de reeks in 'grafwb' bepalen
idxwr = ActiveSheet.Cells(2, idxjaar - 1322)
For tl = 2 To 61
ActiveSheet.Cells(6, tl) = (ActiveSheet.Cells(2, tl) / idxwr) * 100
Next
End Sub
Now I see that I didn't manage to show the pictures of the userforms. So probably my question gets not understandable.
If my question isn't understandable, is it possible to send the program to you?
Thanks once more!
Regards,
Gust
