call sub from userform

[AnnetteTR]

Hi
I have made a userform witch I use to populate a sheet in excell.
In the userform it is possible to scroll through the existing rows with a set of buttons (eg. next and previous).
I often use this code (eg. in cmdNext and cmdPrevious)

   With Sheets("Ventilation i rum")
         txtSal.Value = .Cells(lCurrentRow, 1).Value
         txtRumnr.Value = .Cells(lCurrentRow, 2).Value                   
         txtKategori.Value = .Cells(lCurrentRow, 3).Value               
         txtFunktion.Value = .Cells(lCurrentRow, 4).Value              
         cbxVentUnit.Value = .Cells(lCurrentRow, 5).Value      '
         txtAreal.Value = .Cells(lCurrentRow, 6).Value                     
         txtHøjde.Value = .Cells(lCurrentRow, 7).Value         '           
         txtLuftskifte.Value = .Cells(lCurrentRow, 8).Value    '         
         txtAntalPers.Value = .Cells(lCurrentRow, 9).Value
         txtLuftPers.Value = .Cells(lCurrentRow, 10).Value               
         txtSik.Value = .Cells(lCurrentRow, 11).Value    '                 
         txtSikMin.Value = .Cells(lCurrentRow, 12).Value    '
         txtSikMax.Value = .Cells(lCurrentRow, 13).Value    '
         txtLaf.Value = .Cells(lCurrentRow, 14).Value    '
         txtLafMin.Value = .Cells(lCurrentRow, 15).Value    '         
         txtLafvol.Value = .Cells(lCurrentRow, 16).Value    '
         txtKS.Value = .Cells(lCurrentRow, 17).Value    '
         txtKSvol.Value = .Cells(lCurrentRow, 18).Value    '
         txtSS1.Value = .Cells(lCurrentRow, 19).Value    '
         txtLugeEtMin.Value = .Cells(lCurrentRow, 20).Value    '
         txtLugeEtMax.Value = .Cells(lCurrentRow, 21).Value    '
         txtSS2.Value = .Cells(lCurrentRow, 24).Value    '
         txtLugeToMin.Value = .Cells(lCurrentRow, 25).Value    '
         txtLugeToMax.Value = .Cells(lCurrentRow, 26).Value    '
         txtPS.Value = .Cells(lCurrentRow, 30).Value    '
         txtPSmin.Value = .Cells(lCurrentRow, 31).Value    '
         txtPSvol.Value = .Cells(lCurrentRow, 32).Value    '
         txtAndet.Value = .Cells(lCurrentRow, 33).Value    '
         txtAndetMin.Value = .Cells(lCurrentRow, 34).Value    '
         txtAndetMax.Value = .Cells(lCurrentRow, 35).Value    '
         txtBemærk.Text = .Cells(lCurrentRow, 39).Value
         cbxGMO.Value = .Cells(lCurrentRow, 42).Value    '
         cbxIsotop.Value = .Cells(lCurrentRow, 43).Value    ''
         cbxBioAgKl.Value = .Cells(lCurrentRow, 44).Value   ''
         txtAtex.Value = .Cells(lCurrentRow, 45).Value    ''
         txtRenhed.Value = .Cells(lCurrentRow, 46).Value    ''
         txtIngen.Value = .Cells(lCurrentRow, 47).Value    ''
    End With

to populate the userform with the right values. This works fine, but if there are changes I have to remember to change several places.

The question is:
How do I create a sub to do the same?

I did make a macro :

Sub VisAktuelRække()
With Sheets("Ventilation i rum")
 
 
      txtSal.Value = .Cells(lCurrentRow, 1).Value
      txtRumnr.Value = .Cells(lCurrentRow, 2).Value                  
      txtKategori.Value = .Cells(lCurrentRow, 3).Value              
      txtFunktion.Value = .Cells(lCurrentRow, 4).Value '             
      cbxVentUnit.Value = .Cells(lCurrentRow, 5).Value      '
      txtAreal.Value = .Cells(lCurrentRow, 6).Value                     
      txtHøjde.Value = .Cells(lCurrentRow, 7).Value         '         
      txtLuftskifte.Value = .Cells(lCurrentRow, 8).Value    '        
      txtAntalPers.Value = .Cells(lCurrentRow, 9).Value
      txtLuftPers.Value = .Cells(lCurrentRow, 10).Value             
      txtSik.Value = .Cells(lCurrentRow, 11).Value    '                  
      txtSikMin.Value = .Cells(lCurrentRow, 12).Value    '
      txtSikMax.Value = .Cells(lCurrentRow, 13).Value    '
      txtLaf.Value = .Cells(lCurrentRow, 14).Value    '
      txtLafMin.Value = .Cells(lCurrentRow, 15).Value    '          
      txtLafvol.Value = .Cells(lCurrentRow, 16).Value    '
      txtKS.Value = .Cells(lCurrentRow, 17).Value    '
      txtKSvol.Value = .Cells(lCurrentRow, 18).Value    '
      txtSS1.Value = .Cells(lCurrentRow, 19).Value    '
      txtLugeEtMin.Value = .Cells(lCurrentRow, 20).Value    '
      txtLugeEtMax.Value = .Cells(lCurrentRow, 21).Value    '
      txtSS2.Value = .Cells(lCurrentRow, 24).Value    '
      txtLugeToMin.Value = .Cells(lCurrentRow, 25).Value    '
      txtLugeToMax.Value = .Cells(lCurrentRow, 26).Value    '
      txtPS.Value = .Cells(lCurrentRow, 30).Value    '
      txtPSmin.Value = .Cells(lCurrentRow, 31).Value    '
      txtPSvol.Value = .Cells(lCurrentRow, 32).Value    '
      txtAndet.Value = .Cells(lCurrentRow, 33).Value    '
      txtAndetMin.Value = .Cells(lCurrentRow, 34).Value    '
      txtAndetMax.Value = .Cells(lCurrentRow, 35).Value    '
      txtBemærk.Text = .Cells(lCurrentRow, 39).Value
      cbxGMO.Value = .Cells(lCurrentRow, 42).Value    '
      cbxIsotop.Value = .Cells(lCurrentRow, 43).Value    ''
      cbxBioAgKl.Value = .Cells(lCurrentRow, 44).Value   ''
      txtAtex.Value = .Cells(lCurrentRow, 45).Value    ''
      txtRenhed.Value = .Cells(lCurrentRow, 46).Value    ''
      txtIngen.Value = .Cells(lCurrentRow, 47).Value    ''
End With
End Sub

but how do I get the value lCurrentRow into the sub?

Please help.
Regards Annette

 

[jim may]

Maybe (??)

lCurrentRow = Activecell.row

 

[AnnetteTR]

Sorry
I didnt mention lCurrentRow has been given a value before I want to call the sub.

So I want to do something like this:

lCurrentRow = value
Run("VisAktuelRække(lCurrentRow)")

But it doesnt work.
I guess it is the declaration or something that is wrong.

Regards Annette

 

[jim may]

Maybe....

lCurrentRow = value
Run("VisAktuelRække",lCurrentRow)

 

[jim may]

Maybe I should have said...


lCurrentRow = value
Application.Run("VisAktuelRække",lCurrentRow)

 

[AnnetteTR]

Hi Jim
When I write
Application.Run("VisAktuelRække",lCurrentRow)
in my vba I get a fault when I change line:

Compile error:
Expected :=

Maybe it is my definition of the sub?


Hey now it works!
I moved my sub to the userform and then I just wrote
VisAktuelRække

Thank you for your help.
Annette<!-- / message --><!-- sig -->