help with macro

A

Axel

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 ***
 
C

Chip Pearson

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 said:
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 ***
Click to expand...
 
T

Trevor Shuttleworth

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 said:
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 ***
Click to expand...
 
R

Rick Rothstein \(MVP - VB\)

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 said:
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 ***
Click to expand...
 
R

Rick Rothstein \(MVP - VB\)

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:
Click to expand...
Click to expand...

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) said:
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 said:
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 ***
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

answer to Rick 7
Impleted the following code 0
MsgBox. problem to continue the macro 6
Initializing Userform not working 4
How calculate a difference btwn 2 TextBox and insert the result inthe appropriate cell 4
Data Modification 1
User defined data type (losing its data) 2
activate optionbutton in a userform based o a condition formatting 0

Top