Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"OnCancel" syntax?
Some syntax given to me has error handling but I've found that that
doesn't work in a situation where the user can choose to cancel. Cancelling while performing an operation just causes the process to loop back so, in essence, the user is forced to continue. Can an additional option for when a user hits cancel be added to this great script (courtesy of JulieD; thanks Julie! <g). The error part doesn't handle a cancel by the user. ------------------------------------------------------------------------------------------------ Sub insertrows() Dim i As Long Dim j As Long Dim k As Long On Error GoTo dontdothat Do i = InputBox("How many rows do you want to insert?", "Insert Rows", 1) Loop Until i < 0 Do j = InputBox("At what row number do you want to start the insertion?", "Insert Rows", 1) Loop Until j < 0 ActiveSheet.Unprotect k = j + i - 1 Range("" & j & ":" & k & "").Insert shift:=xlDown j = j - 1 Range("A" & j & "").Select Selection.AutoFill Destination:=Range("A" & j & ":A" & k & ""), Type:=xlFillDefault ActiveSheet.Protect Exit Sub dontdothat: Resume End Sub ------------------------------------------------------------------------------------------------ Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"OnCancel" syntax?
Hi StargateFan,
Change: Resume To: MsgBox "U Cancelled" Regards, Norman "StargateFan" wrote in message ... Some syntax given to me has error handling but I've found that that doesn't work in a situation where the user can choose to cancel. Cancelling while performing an operation just causes the process to loop back so, in essence, the user is forced to continue. Can an additional option for when a user hits cancel be added to this great script (courtesy of JulieD; thanks Julie! <g). The error part doesn't handle a cancel by the user. -------------------------------------------------------------------------- ---------------------- Sub insertrows() Dim i As Long Dim j As Long Dim k As Long On Error GoTo dontdothat Do i = InputBox("How many rows do you want to insert?", "Insert Rows", 1) Loop Until i < 0 Do j = InputBox("At what row number do you want to start the insertion?", "Insert Rows", 1) Loop Until j < 0 ActiveSheet.Unprotect k = j + i - 1 Range("" & j & ":" & k & "").Insert shift:=xlDown j = j - 1 Range("A" & j & "").Select Selection.AutoFill Destination:=Range("A" & j & ":A" & k & ""), Type:=xlFillDefault ActiveSheet.Protect Exit Sub dontdothat: Resume End Sub -------------------------------------------------------------------------- ---------------------- Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"OnCancel" syntax?
You could try
Sub insertrows() Dim i As Long Dim j As Long Dim k As Long On Error GoTo dontdothat Do i = InputBox("How many rows do you want to insert?", "Insert Rows", 1) Loop Until i < 0 Do j = InputBox("At what row number do you want to start the insertion?", "Insert Rows", 1) Loop Until j < 0 ActiveSheet.Unprotect k = j + i - 1 Range("" & j & ":" & k & "").Insert shift:=xlDown j = j - 1 Range("A" & j & "").Select Selection.AutoFill Destination:=Range("A" & j & ":A" & k & ""), Type:=xlFillDefault ActiveSheet.Protect Exit Sub dontdothat: if i < 0 then Resume End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "StargateFan" wrote in message ... Some syntax given to me has error handling but I've found that that doesn't work in a situation where the user can choose to cancel. Cancelling while performing an operation just causes the process to loop back so, in essence, the user is forced to continue. Can an additional option for when a user hits cancel be added to this great script (courtesy of JulieD; thanks Julie! <g). The error part doesn't handle a cancel by the user. -------------------------------------------------------------------------- ---------------------- Sub insertrows() Dim i As Long Dim j As Long Dim k As Long On Error GoTo dontdothat Do i = InputBox("How many rows do you want to insert?", "Insert Rows", 1) Loop Until i < 0 Do j = InputBox("At what row number do you want to start the insertion?", "Insert Rows", 1) Loop Until j < 0 ActiveSheet.Unprotect k = j + i - 1 Range("" & j & ":" & k & "").Insert shift:=xlDown j = j - 1 Range("A" & j & "").Select Selection.AutoFill Destination:=Range("A" & j & ":A" & k & ""), Type:=xlFillDefault ActiveSheet.Protect Exit Sub dontdothat: Resume End Sub -------------------------------------------------------------------------- ---------------------- Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"OnCancel" syntax?
would it not be easier to show the user an inputbox with a refedit, so he
can POINT to the range? like in application.inputbox("Select the range",type:=8) keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool StargateFan wrote: Some syntax given to me has error handling but I've found that that doesn't work in a situation where the user can choose to cancel. Cancelling while performing an operation just causes the process to loop back so, in essence, the user is forced to continue. Can an additional option for when a user hits cancel be added to this great script (courtesy of JulieD; thanks Julie! <g). The error part doesn't handle a cancel by the user. ---------------------------------------------------------------------- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"OnCancel" syntax?
"Norman Jones" wrote in message
... Hi StargateFan, Change: Resume To: MsgBox "U Cancelled" Regards, Norman Oh, brother! <lol That was so easy to spot once you pointed it out. What about a "do nothing" type of deal? Rather than a msg box that will get annoying <g, if the cancel just aborts the operation, I think that would be straightforward enough. Thanks! "StargateFan" wrote in message ... Some syntax given to me has error handling but I've found that that doesn't work in a situation where the user can choose to cancel. Cancelling while performing an operation just causes the process to loop back so, in essence, the user is forced to continue. Can an additional option for when a user hits cancel be added to this great script (courtesy of JulieD; thanks Julie! <g). The error part doesn't handle a cancel by the user. -------------------------------------------------------------------------- ---------------------- Sub insertrows() Dim i As Long Dim j As Long Dim k As Long On Error GoTo dontdothat Do i = InputBox("How many rows do you want to insert?", "Insert Rows", 1) Loop Until i < 0 Do j = InputBox("At what row number do you want to start the insertion?", "Insert Rows", 1) Loop Until j < 0 ActiveSheet.Unprotect k = j + i - 1 Range("" & j & ":" & k & "").Insert shift:=xlDown j = j - 1 Range("A" & j & "").Select Selection.AutoFill Destination:=Range("A" & j & ":A" & k & ""), Type:=xlFillDefault ActiveSheet.Protect Exit Sub dontdothat: Resume End Sub -------------------------------------------------------------------------- ---------------------- Thanks! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
"OnCancel" syntax?
Hi Stargate,
Simply delete: MsgBox "U Cancelled" If you want to retain the loop until either a number is entered or the inputbox is cancelled, try: Sub insertrows2() Dim i As Variant Dim j As Variant Dim k As Long On Error GoTo dontdothat Do i = InputBox("How many rows do you want to insert?", _ "Insert Rows ", 1) If StrPtr(i) = 0 Then GoTo UCancelled Loop Until i < 0 Do j = InputBox("At what row number do you want to start" & _ " the insertion?", "Insert Rows", 1) If StrPtr(j) = 0 Then GoTo UCancelled Loop Until j < 0 ActiveSheet.Unprotect k = j + i - 1 Range("" & j & ":" & k & "").Insert shift:=xlDown j = j - 1 Range("A" & j & "").Select Selection.AutoFill Destination:=Range("A" & j & ":A" _ & k & ""), Type:=xlFillDefault ActiveSheet.Protect Exit Sub dontdothat: End Sub --- Regards, Norman "StargateFanFromWork" wrote in message ... "Norman Jones" wrote in message ... Hi StargateFan, Change: Resume To: MsgBox "U Cancelled" Regards, Norman Oh, brother! <lol That was so easy to spot once you pointed it out. What about a "do nothing" type of deal? Rather than a msg box that will get annoying <g, if the cancel just aborts the operation, I think that would be straightforward enough. Thanks! "StargateFan" wrote in message ... Some syntax given to me has error handling but I've found that that doesn't work in a situation where the user can choose to cancel. Cancelling while performing an operation just causes the process to loop back so, in essence, the user is forced to continue. Can an additional option for when a user hits cancel be added to this great script (courtesy of JulieD; thanks Julie! <g). The error part doesn't handle a cancel by the user. -------------------------------------------------------------------------- ---------------------- Sub insertrows() Dim i As Long Dim j As Long Dim k As Long On Error GoTo dontdothat Do i = InputBox("How many rows do you want to insert?", "Insert Rows", 1) Loop Until i < 0 Do j = InputBox("At what row number do you want to start the insertion?", "Insert Rows", 1) Loop Until j < 0 ActiveSheet.Unprotect k = j + i - 1 Range("" & j & ":" & k & "").Insert shift:=xlDown j = j - 1 Range("A" & j & "").Select Selection.AutoFill Destination:=Range("A" & j & ":A" & k & ""), Type:=xlFillDefault ActiveSheet.Protect Exit Sub dontdothat: Resume End Sub -------------------------------------------------------------------------- ---------------------- Thanks! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
"OnCancel" syntax?
Hi Stargate,
In Sub insertrows2(), line 4 and the penultimate line, change dontdothat: To: UCancelled My sloppyness! --- Regards, Norman "Norman Jones" wrote in message ... Hi Stargate, Simply delete: MsgBox "U Cancelled" If you want to retain the loop until either a number is entered or the inputbox is cancelled, try: Sub insertrows2() Dim i As Variant Dim j As Variant Dim k As Long On Error GoTo dontdothat Do i = InputBox("How many rows do you want to insert?", _ "Insert Rows ", 1) If StrPtr(i) = 0 Then GoTo UCancelled Loop Until i < 0 Do j = InputBox("At what row number do you want to start" & _ " the insertion?", "Insert Rows", 1) If StrPtr(j) = 0 Then GoTo UCancelled Loop Until j < 0 ActiveSheet.Unprotect k = j + i - 1 Range("" & j & ":" & k & "").Insert shift:=xlDown j = j - 1 Range("A" & j & "").Select Selection.AutoFill Destination:=Range("A" & j & ":A" _ & k & ""), Type:=xlFillDefault ActiveSheet.Protect Exit Sub dontdothat: End Sub --- Regards, Norman "StargateFanFromWork" wrote in message ... "Norman Jones" wrote in message ... Hi StargateFan, Change: Resume To: MsgBox "U Cancelled" Regards, Norman Oh, brother! <lol That was so easy to spot once you pointed it out. What about a "do nothing" type of deal? Rather than a msg box that will get annoying <g, if the cancel just aborts the operation, I think that would be straightforward enough. Thanks! "StargateFan" wrote in message ... Some syntax given to me has error handling but I've found that that doesn't work in a situation where the user can choose to cancel. Cancelling while performing an operation just causes the process to loop back so, in essence, the user is forced to continue. Can an additional option for when a user hits cancel be added to this great script (courtesy of JulieD; thanks Julie! <g). The error part doesn't handle a cancel by the user. -------------------------------------------------------------------------- ---------------------- Sub insertrows() Dim i As Long Dim j As Long Dim k As Long On Error GoTo dontdothat Do i = InputBox("How many rows do you want to insert?", "Insert Rows", 1) Loop Until i < 0 Do j = InputBox("At what row number do you want to start the insertion?", "Insert Rows", 1) Loop Until j < 0 ActiveSheet.Unprotect k = j + i - 1 Range("" & j & ":" & k & "").Insert shift:=xlDown j = j - 1 Range("A" & j & "").Select Selection.AutoFill Destination:=Range("A" & j & ":A" & k & ""), Type:=xlFillDefault ActiveSheet.Protect Exit Sub dontdothat: Resume End Sub -------------------------------------------------------------------------- ---------------------- Thanks! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
"OnCancel" syntax?
"Norman Jones" wrote in message
... Hi Stargate, In Sub insertrows2(), line 4 and the penultimate line, change dontdothat: To: UCancelled My sloppyness! I actually caught that! This is exciting for me, I'm finally getting a glimmer of the parallels between WordPerfect/Filemaker Pro macro/scripting to VB sometimes. I was able to figure out that the label (or whatever VB calls it) was not there. Still, it doesn't seem to do anything different than the other code did, and also, something really funny, instead of the number of lines I requested to be entered, 500 lines were! <lol I think I'll go back for now to the other syntax as it worked without the "resume" put in. I don't know enough yet about VB to figure out how the changes you made affected the macro. Thanks much, though. I really appreciate the help always! :oD --- Regards, Norman "Norman Jones" wrote in message ... Hi Stargate, Simply delete: MsgBox "U Cancelled" If you want to retain the loop until either a number is entered or the inputbox is cancelled, try: Sub insertrows2() Dim i As Variant Dim j As Variant Dim k As Long On Error GoTo dontdothat Do i = InputBox("How many rows do you want to insert?", _ "Insert Rows ", 1) If StrPtr(i) = 0 Then GoTo UCancelled Loop Until i < 0 Do j = InputBox("At what row number do you want to start" & _ " the insertion?", "Insert Rows", 1) If StrPtr(j) = 0 Then GoTo UCancelled Loop Until j < 0 ActiveSheet.Unprotect k = j + i - 1 Range("" & j & ":" & k & "").Insert shift:=xlDown j = j - 1 Range("A" & j & "").Select Selection.AutoFill Destination:=Range("A" & j & ":A" _ & k & ""), Type:=xlFillDefault ActiveSheet.Protect Exit Sub dontdothat: End Sub --- Regards, Norman "StargateFanFromWork" wrote in message ... "Norman Jones" wrote in message ... Hi StargateFan, Change: Resume To: MsgBox "U Cancelled" Regards, Norman Oh, brother! <lol That was so easy to spot once you pointed it out. What about a "do nothing" type of deal? Rather than a msg box that will get annoying <g, if the cancel just aborts the operation, I think that would be straightforward enough. Thanks! "StargateFan" wrote in message ... Some syntax given to me has error handling but I've found that that doesn't work in a situation where the user can choose to cancel. Cancelling while performing an operation just causes the process to loop back so, in essence, the user is forced to continue. Can an additional option for when a user hits cancel be added to this great script (courtesy of JulieD; thanks Julie! <g). The error part doesn't handle a cancel by the user. -------------------------------------------------------------------------- ---------------------- Sub insertrows() Dim i As Long Dim j As Long Dim k As Long On Error GoTo dontdothat Do i = InputBox("How many rows do you want to insert?", "Insert Rows", 1) Loop Until i < 0 Do j = InputBox("At what row number do you want to start the insertion?", "Insert Rows", 1) Loop Until j < 0 ActiveSheet.Unprotect k = j + i - 1 Range("" & j & ":" & k & "").Insert shift:=xlDown j = j - 1 Range("A" & j & "").Select Selection.AutoFill Destination:=Range("A" & j & ":A" & k & ""), Type:=xlFillDefault ActiveSheet.Protect Exit Sub dontdothat: Resume End Sub -------------------------------------------------------------------------- ---------------------- Thanks! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
"OnCancel" syntax?
"StargateFan" wrote in message
... Some syntax given to me has error handling but I've found that that doesn't work in a situation where the user can choose to cancel. Cancelling while performing an operation just causes the process to loop back so, in essence, the user is forced to continue. Can an additional option for when a user hits cancel be added to this great script (courtesy of JulieD; thanks Julie! <g). The error part doesn't handle a cancel by the user. [snip] Here's the code that seems to be working perfectly! Column A has =ROW()-1 in it. This macro (thanks to JulieD for this great code) will insert however many lines user requests into spot user says with that column in the cell(s) in column A. Or if user realizes at any point that s/he needs to cancel, it just stops the operation. Really neat stuff! (Unprotect/protect code taken out because I'll be leaving the workbooks unprotected as too many problems occurred with unavailable functionality.) ---------------------------------------------------------------------------- -------------- Sub InsertROWS() Dim i As Long Dim j As Long Dim k As Long On Error GoTo dontdothat Do i = InputBox("How many rows do you want to insert?", "Insert Rows ", 1) Loop Until i < 0 Do j = InputBox("At what row number do you want to start the insertion?", "Insert Rows", 10) Loop Until j < 0 k = j + i - 1 Range("" & j & ":" & k & "").Insert shift:=xlDown j = j - 1 Range("A" & j & "").Select Selection.AutoFill Destination:=Range("A" & j & ":A" & k & ""), Type:=xlFillDefault Exit Sub dontdothat: End Sub ---------------------------------------------------------------------------- -------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
correct syntax for nesting "if", "and", and "vlookup"....if possib | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
syntax for "IF" commend to check for multiple empty cells bf comma | Excel Worksheet Functions | |||
Syntax to "OR" 3 "ISERROR" conditions | Excel Worksheet Functions | |||
what is syntax for if(between range of dates,"Q1","Q2")? | Excel Worksheet Functions |