| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
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 http://www.developersdex.com *** |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
help with macro
Try something like
TextBox1.Text = Cells(iCtl+3,"B").Value -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Axel" wrote in message ... 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 http://www.developersdex.com *** |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
help with macro
TextBox1 = Range("B" & iCtl+3)
Do some error checking to make sure iCtl is an acceptable value, for example, 0 and less than maximum. Take a similar approach for TextBox2 Range("B" & iCtl+3) = Me.TextBox2 Regards Trevor "Axel" wrote in message ... 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 http://www.developersdex.com *** |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
help with macro
You can replace the assignment to the iCtl variable as well as your
**entire** Select Case structure with this one line... TextBox1.Text = Range("B" & CStr(3 + ComboBox1.Value)) Rick "Axel" wrote in message ... 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 http://www.developersdex.com *** |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
help with macro
My answer still holds, but I didn't realize how much code you had... it
holds for the ComboBox1 Change event. As for your CommandButton1 Click event, I have a question. Consider this snippet of your code... 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: If iCtr is 1, it falls into the first Case statement. If TextBox3.Text is the empty string, you have it go to the "line 1" label (and to "line 2" label if it fails there, and so on). Why? If your code fell into the first Case statement, then iCtr equals 1, meaning it can't pass the second (or any of the other) Case statement conditions. All of this passing on through the various labels will never have a positive hit. I really am not sure what you are attempting here. Can you clarify what you think your code is doing? Rick "Rick Rothstein (MVP - VB)" wrote in message ... You can replace the assignment to the iCtl variable as well as your **entire** Select Case structure with this one line... TextBox1.Text = Range("B" & CStr(3 + ComboBox1.Value)) Rick "Axel" wrote in message ... 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 http://www.developersdex.com *** |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
help with macro
Thank you very much Rick, Trevor and Chip. This saved me a lot of unnecessary code. *** Sent via Developersdex http://www.developersdex.com *** |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
| Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
| Macro not showing in Tools/Macro/Macros yet show up when I goto VBA editor | Excel Programming | |||
| how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
| Start Macro / Stop Macro / Restart Macro | Excel Programming | |||
Hybrid Mode
