"OnCancel" syntax?

  • Thread starter Thread starter StargateFan
  • Start date Start date
S

StargateFan

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
 
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)
 
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 >
 
Norman Jones said:
Hi StargateFan,

Change:

Resume

To:

MsgBox "U Cancelled"


Regards,
Norman
Click to expand...

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!
 
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 said:
Norman Jones said:
Hi StargateFan,

Change:

Resume

To:

MsgBox "U Cancelled"


Regards,
Norman
Click to expand...

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!
Click to expand...
 
Hi Stargate,



In Sub insertrows2(), line 4 and the penultimate line, change

dontdothat:

To:

UCancelled

My sloppyness!
 
Norman Jones said:
Hi Stargate,



In Sub insertrows2(), line 4 and the penultimate line, change

dontdothat:

To:

UCancelled

My sloppyness!
Click to expand...

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

[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
 
Back
Top