answer to Rick

[Axel]

Sorry, but it was so much scrolling, that it's better to answer you in a
new post.
The point is; -if nothing is written by user in textbox3, the existing
text in the cell that relate to textbox3 will not be changed.
if this still is confusing, I can send u the file
Thanks very mush for your help.
Aksel

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


*** Sent via Developersdex http://www.developersdex.com ***

 

[Rick Rothstein \(MVP - VB\)]

Sorry, but it was so much scrolling, that it's better to answer

you in a new post.

I don't think it is better to do that at all. This message, and any answers
it receives, will not be connected to your previous thread in the archive (I
am thinking Google here); so anyone following the original thread will not
know it has been continued and may not see any follow ups. It is always
better to stick with the original thread.

The point is; -if nothing is written by user in textbox3, the existing
text in the cell that relate to textbox3 will not be changed.
if this still is confusing, I can send u the file

No, I really wouldn't have the time to study/learn your spreadsheet just for
a single answer. But I would like to clarify my previous post. I had made an
assumption that wasn't true, but your code is still flawed (but not
harmfully so, at least I don't think it is harmful). Here is my problem. You
wrote this 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:
.....<<rest of code snipped>>.....

Let's assume iCtr is 1. When you enter the Select Case block, iCtr meets the
first Case statement's condition, so that code executes. It sets B4 equal to
the contents of TextBox2. THEN, it looks at the contents of TextBox3 and IF
there is nothing in that TextBox, it jumps to the 'line1' label in order to
continue execution. However, NO ADDITIONAL CODE inside the Select Case block
will be executed... the next statement that will be execute is the one that
comes after the End Select statement. You have labels in front of each of
your Case statements and Goto statements that attempt to direct code
execution to them... and my question is why? The code in a Case statement's
block will natually fall through to the End Select statement when all the
code in the block has been executed.... there is no need, or benefit, trying
to direct the execution to other locations within the Select Case block.
Here is a short demo to show you the problem I am trying to highlight.
Consider this code....

Dim iCtr As Long
iCtr = 1
Select Case iCtr
Case Is = 1
GoTo line1
MsgBox "Will I be executed?"
line1:
Case Is = 2
MsgBox "I won't be executed if iCtr = 1"
End Select

Neither one of the MsgBox statements will be executed. Do you see the
parallel? If iCtr equals 1, the GoTo line1 statement will do what your empty
TextBox3 will do, send execution to the 'line1' label; HOWEVER, no other
code in the Select Case block will be executed, especially the "I won't be
executed if iCtr=1" MessageBox that is in the Case block that the GoTo
statement appears to be sending code execution to.

Rick

 

[Axel]

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

Well. the code works fine. I tryed all 50 cases with and without text in
textbox3. if I take away the: "If TextBox3.Text = "" Then GoTo line50".
the code will set for eksample: Range("C53") = Me.TextBox3. that means
it will be executed even if user dont write anything in textbox3.
But I want the existing text to stay in the cell if the user dont write
anything in textbox3

I am a low level hobby programmer, so please be patient to my answer. I
can't explain why it work.

Thanks

Aksel

*** Sent via Developersdex http://www.developersdex.com ***

 

[Rick Rothstein \(MVP - VB\)]

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

Well. the code works fine. I tryed all 50 cases with and without text in
textbox3. if I take away the: "If TextBox3.Text = "" Then GoTo line50".
the code will set for eksample: Range("C53") = Me.TextBox3. that means
it will be executed even if user dont write anything in textbox3.
But I want the existing text to stay in the cell if the user dont write
anything in textbox3

Okay, then you are using the GoTo statements to deliberately skip over
statements. You could have accomplished that by using only one label and
GoTo'ing that one label from all of your If-Then statements. However, I
don't recommend your doing that. You can simply restructure your If-Then
statement instead. Here is a snippet from the middle of your code (same
technique would apply in all of your Case statement blocks)...

Restructure them to look like this...

Case 1
Range("B4") = Me.Textbox2
If TextBox3.Text <> "" Then Range("C4") = Me.TextBox3
Case 2
Range("B5") = Me.Textbox2
If TextBox3.Text <> "" Then Range("C5") = Me.TextBox3

Notice what I have done... I changed your test from "is it blank" to "does
it contain something". Study what I have just done until it makes sense to
you... the technique of looking at your condition from the "other side" is a
powerful one and come up often. As a general rule, GoTo is a frowned upon
techique to skip over code because it make code hard to read (especially
when you come back in 6 months to try and modify it). Now, with that said,
we can replace your entire Select Case block... the WHOLE thing... with this
code...

Range("B" & CStr(iCtr)) = Me.TextBox2
If TextBox3.Text <> "" Then Range("C" & CStr(iCtr)) = Me.TextBox3

This is just an extension of the technique offered you in your original
thread for your first Select Case block. Now, you will want to put in some
error checking (for example, test whether TextBox2 greater than 50, the
highest Case test you have in your original code), but the above should get
you started.

Rick

 

[Axel]

Again!
Thank you for all the time you used on this question, I will post a
reply later when I tried it out your solution.

*** Sent via Developersdex http://www.developersdex.com ***

 

[Axel]

Again!
Thank you for all the time you used on this question, I will post a
reply later when I tried it out your solution.

*** Sent via Developersdex http://www.developersdex.com ***

 

[Axel]

your solution worked perfect. final code below
Thank you Rick

Aksel

Private Sub ComboBox1_Change()
Dim iCtr As Integer
Firstline:
On Error GoTo Errorline
iCtl = ComboBox1.Value
TextBox1.Text = Range("B" & CStr(3 + ComboBox1.Value))
GoTo lastline
Errorline:
MsgBox "kun tall mellom 1 og 50 er gyldig i Radnummer"
Unload ShngSrlNbrUsrFrm
lastline:
End Sub

Private Sub CommandButton1_Click()
If ComboBox1.Value > 50 Then GoTo line1 Else GoTo Line2
line1:
MsgBox "kun tall mellom 1 og 50 er gyldig i Radnummer"
GoTo lastline
Line2:
ActiveSheet.Unprotect Password:=""
Application.ScreenUpdating = False
Dim iCtr As Integer
iCtr = ComboBox1.Value
Range("B" & CStr(iCtr + 3)) = Me.TextBox2
If TextBox3.Text <> "" Then Range("C" & CStr(iCtr + 3)) = Me.TextBox3
lastline:
ActiveSheet.Protect Password:="", DrawingObjects:=True, Contents:=True,
Scenarios:=True
Unload ShngSrlNbrUsrFrm

*** Sent via Developersdex http://www.developersdex.com ***

 

[Rick Rothstein \(MVP - VB\)]

Well, that code IS somewhat shorter than what you posted originally, isn't
it?<g>

Your are quite welcome. I am glad everything worked out for you. Good luck
with the rest of your project.

Rick