Thread: help with macro
View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Rick Rothstein \(MVP - VB\) Rick Rothstein \(MVP - VB\) is offline
external usenet poster
  
Posts: 2,202
Default 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 ***


Reply With QuoteReply With Quote