Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
answer to Rick
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
answer to Rick
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
answer to Rick
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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
answer to Rick
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)... 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: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
answer to Rick
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 *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
answer to Rick
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 *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
answer to Rick
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 *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
answer to Rick
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 "Axel" wrote in message ... 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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with IF, if it is the answer.. | Excel Discussion (Misc queries) | |||
Calculator Answer Doesn't Match Excel Answer | Excel Discussion (Misc queries) | |||
plz answer | Excel Discussion (Misc queries) | |||
Question for Rick Rothstein | Excel Discussion (Misc queries) | |||
i cant get the exact answer e.g answer is 13.49% i got 13.00% | Excel Discussion (Misc queries) |