Reply to thread

Message: [QUOTE="Axel, post: 6244214"] Is there an easier way to write this macro, because am about to make one with 162 cellreference, and not looking forward to do it. this is a Userform with a combobox for selecting rownumber(A4:A53), a textbox for displaying the value in the cells to the left for the rownumber cells(B4:B53), and two textboxes for write a new number to the righ for the row number(B4:B53)(C4:C53) Grateful for all help! Private Sub ComboBox1_Change() Dim iCtr As Integer iCtl = ComboBox1.Value Select Case [iCtl] Case Is = 1 TextBox1 = Range("B4") Case Is = 2 TextBox1 = Range("B5") Case Is = 3 TextBox1 = Range("B6") Case Is = 4 TextBox1 = Range("B7") Case Is = 5 TextBox1 = Range("B8") Case Is = 6 TextBox1 = Range("B9") Case Is = 7 TextBox1 = Range("B10") Case Is = 8 TextBox1 = Range("B11") Case Is = 9 TextBox1 = Range("B12") Case Is = 10 TextBox1 = Range("B13") Case Is = 11 TextBox1 = Range("B14") Case Is = 12 TextBox1 = Range("B15") Case Is = 13 TextBox1 = Range("B16") Case Is = 14 TextBox1 = Range("B17") Case Is = 15 TextBox1 = Range("B18") Case Is = 16 TextBox1 = Range("B19") Case Is = 17 TextBox1 = Range("B20") Case Is = 18 TextBox1 = Range("B21") Case Is = 19 TextBox1 = Range("B22") Case Is = 20 TextBox1 = Range("B23") Case Is = 21 TextBox1 = Range("B24") Case Is = 22 TextBox1 = Range("B25") Case Is = 23 TextBox1 = Range("B26") Case Is = 24 TextBox1 = Range("B27") Case Is = 25 TextBox1 = Range("B28") Case Is = 26 TextBox1 = Range("B29") Case Is = 27 TextBox1 = Range("B30") Case Is = 28 TextBox1 = Range("B31") Case Is = 29 TextBox1 = Range("B32") Case Is = 30 TextBox1 = Range("B33") Case Is = 31 TextBox1 = Range("B34") Case Is = 32 TextBox1 = Range("B35") Case Is = 33 TextBox1 = Range("B36") Case Is = 34 TextBox1 = Range("B37") Case Is = 35 TextBox1 = Range("B38") Case Is = 36 TextBox1 = Range("B39") Case Is = 37 TextBox1 = Range("B40") Case Is = 38 TextBox1 = Range("B41") Case Is = 39 TextBox1 = Range("B42") Case Is = 40 TextBox1 = Range("B43") Case Is = 41 TextBox1 = Range("B44") Case Is = 42 TextBox1 = Range("B45") Case Is = 43 TextBox1 = Range("B46") Case Is = 44 TextBox1 = Range("B47") Case Is = 45 TextBox1 = Range("B48") Case Is = 46 TextBox1 = Range("B49") Case Is = 47 TextBox1 = Range("B50") Case Is = 48 TextBox1 = Range("B51") Case Is = 49 TextBox1 = Range("B52") Case Is = 50 TextBox1 = Range("B53") End Select End Sub Private Sub CommandButton1_Click() ActiveSheet.Unprotect Password:="" Application.ScreenUpdating = False If TextBox2.Text = "" Then GoTo errorline On Error GoTo lastline 'shows the row number to the serialnumber Dim iCtr As Integer iCtr = ComboBox1.Value Select Case [iCtr] Case Is = 1 Range("B4") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line1 Range("C4") = Me.TextBox3 line1: Case Is = 2 Range("B5") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line2 Range("C5") = Me.TextBox3 line2: Case Is = 3 Range("B6") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line3 Range("C6") = Me.TextBox3 line3: Case Is = 4 Range("B7") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line4 Range("C7") = Me.TextBox3 line4: Case Is = 5 Range("B8") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line5 Range("C8") = Me.TextBox3 line5: Case Is = 6 Range("B9") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line6 Range("C9") = Me.TextBox3 line6: Case Is = 7 Range("B10") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line7 Range("C10") = Me.TextBox3 line7: Case Is = 8 Range("B11") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line8 Range("C11") = Me.TextBox3 line8: Case Is = 9 Range("B12") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line9 Range("C12") = Me.TextBox3 line9: Case Is = 10 Range("B13") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line10 Range("C13") = Me.TextBox3 line10: Case Is = 11 Range("B14") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line11 Range("C14") = Me.TextBox3 line11: Case Is = 12 Range("B15") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line12 Range("C15") = Me.TextBox3 line12: Case Is = 13 Range("B16") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line13 Range("C16") = Me.TextBox3 line13: Case Is = 14 Range("B17") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line14 Range("C17") = Me.TextBox3 line14: Case Is = 15 Range("B18") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line15 Range("C18") = Me.TextBox3 line15: Case Is = 16 Range("B19") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line16 Range("C19") = Me.TextBox3 line16: Case Is = 17 Range("B20") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line17 Range("C20") = Me.TextBox3 line17: Case Is = 18 Range("B21") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line18 Range("C21") = Me.TextBox3 line18: Case Is = 19 Range("B22") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line19 Range("C22") = Me.TextBox3 line19: Case Is = 20 Range("B23") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line20 Range("C23") = Me.TextBox3 line20: Case Is = 21 Range("B24") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line21 Range("C24") = Me.TextBox3 line21: Case Is = 22 Range("25") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line22 Range("C25") = Me.TextBox3 line22: Case Is = 23 Range("B26") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line23 Range("C26") = Me.TextBox3 line23: Case Is = 24 Range("B27") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line24 Range("C27") = Me.TextBox3 line24: Case Is = 25 Range("B28") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line25 Range("C28") = Me.TextBox3 line25: Case Is = 26 Range("B29") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line26 Range("C29") = Me.TextBox3 line26: Case Is = 27 Range("B30") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line27 Range("C30") = Me.TextBox3 line27: Case Is = 28 Range("B31") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line28 Range("C31") = Me.TextBox3 line28: Case Is = 29 Range("B32") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line29 Range("C32") = Me.TextBox3 line29: Case Is = 30 Range("B33") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line30 Range("C33") = Me.TextBox3 line30: Case Is = 31 Range("B34") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line31 Range("C34") = Me.TextBox3 line31: Case Is = 32 Range("B35") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line32 Range("C35") = Me.TextBox3 line32: Case Is = 33 Range("B36") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line33 Range("C36") = Me.TextBox3 line33: Case Is = 34 Range("B37") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line34 Range("C37") = Me.TextBox3 line34: Case Is = 35 Range("B38") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line35 Range("C38") = Me.TextBox3 line35: Case Is = 36 Range("B39") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line36 Range("C39") = Me.TextBox3 line36: Case Is = 37 Range("B40") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line37 Range("C40") = Me.TextBox3 line37: Case Is = 38 Range("B41") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line38 Range("C41") = Me.TextBox3 line38: Case Is = 39 Range("B42") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line39 Range("C42") = Me.TextBox3 line39: Case Is = 40 Range("B43") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line40 Range("C43") = Me.TextBox3 line40: Case Is = 41 Range("B44") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line41 Range("C44") = Me.TextBox3 line41: Case Is = 42 Range("B45") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line42 Range("C45") = Me.TextBox3 line42: Case Is = 43 Range("B46") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line43 Range("C46") = Me.TextBox3 line43: Case Is = 44 Range("B47") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line44 Range("C47") = Me.TextBox3 line44: Case Is = 45 Range("B48") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line45 Range("C48") = Me.TextBox3 line45: Case Is = 46 Range("B49") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line46 Range("C49") = Me.TextBox3 line46: Case Is = 47 Range("B50") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line47 Range("C50") = Me.TextBox3 line47: Case Is = 48 Range("B51") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line48 Range("C51") = Me.TextBox3 line48: Case Is = 49 Range("B52") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line49 Range("C52") = Me.TextBox3 line49: Case Is = 50 Range("B53") = Me.TextBox2 If TextBox3.Text = "" Then GoTo line50 Range("C53") = Me.TextBox3 line50: Case Else errorline: MsgBox "Ikke gyldige parameter!" lastline: End Select ActiveSheet.Protect Password:="", DrawingObjects:=True, Contents:=True, Scenarios:=True Unload ShngSrlNbrUsrFrm End Sub *** Sent via Developersdex [URL]http://www.developersdex.com[/URL] *** [/QUOTE]

Verification