"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!

"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!

"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!

"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.

----------------------------------------------------------------------

"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!

"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!

"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!

"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!

"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
----------------------------------------------------------------------------
--------------