Asking user to continue?

[StargateFanNotAtHome]

I don't believe this one has ever come up? I have a script that I
want to ask the user if it's okay to continue. It clears the data so
would like to give user to make sure that's what they want to do, esp.
since when one does this sort of thing programmatically, my
understanding is that there is no way to retrieve deleted information.

Here is the script (XL2003):
*******************************************
Sub ClearDATA()
'
ActiveSheet.Unprotect 'place at the beginning of the code
Range("B2100").Select
Selection.ClearContents

Range("J2:J100").Select
Selection.ClearContents

Range("M2:M100").Select
Selection.ClearContents

With ActiveSheet
.Rows("2:" & .Rows.Count).AutoFit
End With

Range("B2").Select

ActiveSheet.Protect 'place at the end of the code
End Sub
*******************************************

Thanks! D

 

[Jim Thomlinson]

Asking before you delete is pretty standard practice... Try something like
this...

Sub ClearDATA()
'
if msgbox("Delete can not be undone. Continue???") = vbno then exit sub

with ActiveSheet
.Unprotect 'place at the beginning of the code
.Range("B2100").ClearContents
.Range("J2:J100").ClearContents
.Range("M2:M100").ClearContents
.Rows("2:" & .Rows.Count).AutoFit
'.Range("B2").Select
.Protect 'place at the end of the code
end with
End Sub

PS... didn't you used to be StarGateFanFromWork?

 

[Rick Rothstein \(MVP - VB\)]

I would change this line...

if msgbox("Delete can not be undone. Continue???") = vbno then exit sub

to this...

If MsgBox("Delete can not be undone. Continue???", _
vbYesNo Or vbDefaultButton2) = vbNo Then Exit Sub

Rick

 

[StargateFanNotAtHome]

Asking before you delete is pretty standard practice... Try something like
this...

Sub ClearDATA()
'
if msgbox("Delete can not be undone. Continue???") = vbno then exit sub

with ActiveSheet
.Unprotect 'place at the beginning of the code
.Range("B2100").ClearContents
.Range("J2:J100").ClearContents
.Range("M2:M100").ClearContents
.Rows("2:" & .Rows.Count).AutoFit
'.Range("B2").Select
.Protect 'place at the end of the code
end with
End Sub

Darn, much neater than mine!! Beautiful. Mine is very simplistic
compared to this <g>.

Thanks. D

PS... didn't you used to be StarGateFanFromWork?

<vbg> Hey, you picked up on that, that's great <g>. Yup, that's me,
too. But I'm also just plain StargateFan when I'm posting from home.
In this new contract, they use LotusNotes instead of Outlook so even
Outlook Express is not working so I put my newsreader on my USB stick.
The 3rd name makes it 3 ways that I access the ngs which is what I use
to help doing google searches on these groups. I don't post anywhere
near as much as I used to as I'm able to do a lot more by myself
thanks to the previous help of everyone here. And part of that is

 

[StargateFanNotAtHome]

I would change this line...


to this...

If MsgBox("Delete can not be undone. Continue???", _
vbYesNo Or vbDefaultButton2) = vbNo Then Exit Sub

Thanks! This one goes into my tips folder, too, for sure!

 

[StargateFanNotAtHome]

Asking before you delete is pretty standard practice... Try something like
this...

Sub ClearDATA()
'
if msgbox("Delete can not be undone. Continue???") = vbno then exit sub

with ActiveSheet
.Unprotect 'place at the beginning of the code
.Range("B2100").ClearContents
.Range("J2:J100").ClearContents
.Range("M2:M100").ClearContents
.Rows("2:" & .Rows.Count).AutoFit
'.Range("B2").Select
.Protect 'place at the end of the code
end with
End Sub

PS... didn't you used to be StarGateFanFromWork?

Darn, darn, darn. For last few days using this sheet, I've felt it
would be too easy to delete the cell contents despite the caution box
so decided it would be safer to move this button to its own sheet at
the end of the workbook. However, all the codes I've ever used are
for active sheet. I found what I thought was best code from the
archives and modified it to suit my workbook requirements. Here's
where the modified code stands now:
*****************************************************************************
Sub ClearDATAinSUPPLIESsheet()
If MsgBox("You will be deleting the entire supply data from this
sheet! " & vbCrLf & vbCrLf & _
"Do you wish to continue???", _
vbYesNo Or vbDefaultButton2) = vbNo Then Exit Sub

ThisWorkbook.Worksheets("Supplies").Unprotect

With ActiveSheet
' .Unprotect 'place at the beginning of the code
.Range("B2100").ClearContents
.Range("J2:J100").ClearContents
' .Rows("2:" & .Rows.Count).AutoFit
.Range("B2").Select
' .Protect 'place at the end of the code
End With

Sheets("SUPPLIES").Cells.EntireColumn.AutoFit

ThisWorkbook.Worksheets("Supplies").Protect

End Sub
*****************************************************************************

But not sure what is right or not in my modifications since I'm still
getting the same 1004 error as when I just moved the button:

" Run-time error '1004':
Cannot change part of a merged cell."

There are no merged cells in the target sheet. There is on the one
the button is located on now.

Anyway, if I go ahead and debug, this line is highlighted:

.Range("B2100").ClearContents

? I double-checked ranges and what is seen in the script above is
valid. All these cell can have contents removed. They're not
protected and not merged cells.

Any clues?

 

[StargateFanNotAtHome]

With ActiveSheet
' .Unprotect 'place at the beginning of the code
.Range("B2100").ClearContents
.Range("J2:J100").ClearContents
' .Rows("2:" & .Rows.Count).AutoFit
.Range("B2").Select
' .Protect 'place at the end of the code
End With
[snip]

? I double-checked ranges and what is seen in the script above is
valid. All these cell can have contents removed. They're not
protected and not merged cells.

Oh, well, D'UH!!! It took reading it in the blue text in my
newsreader for the problem finally stood out! D'uh! Active sheet
again. I missed one completely <lol>.

Okay, going back to fix that.

Sorry. Wish I'd seen it before posting. <g>

I'll report back. D

 

[StargateFanNotAtHome]

With ActiveSheet
' .Unprotect 'place at the beginning of the code
.Range("B2100").ClearContents
.Range("J2:J100").ClearContents
' .Rows("2:" & .Rows.Count).AutoFit
.Range("B2").Select
' .Protect 'place at the end of the code
End With
[snip]

? I double-checked ranges and what is seen in the script above is
valid. All these cell can have contents removed. They're not
protected and not merged cells.

Oh, well, D'UH!!! It took reading it in the blue text in my
newsreader for the problem finally stood out! D'uh! Active sheet
again. I missed one completely <lol>.

Okay, going back to fix that.

Sorry. Wish I'd seen it before posting. <g>

I'll report back. D

Yes, that seems to have been the problem.

The code is inelgant and messy again but here's what I did:

****************************************************
Sub ClearDATAinSUPPLIESsheet()
If MsgBox("You will be deleting the entire supply data from this
sheet! " & vbCrLf & vbCrLf & _
"Do you wish to continue???", _
vbYesNo Or vbDefaultButton2) = vbNo Then Exit Sub

ThisWorkbook.Worksheets("Supplies").Unprotect

ThisWorkbook.Worksheets("Supplies").Range("B2100").ClearContents
ThisWorkbook.Worksheets("Supplies").Range("J2:J100").ClearContents
' ThisWorkbook.Worksheets("Supplies").Rows("2:" &
..Rows.Count).AutoFit
' ThisWorkbook.Worksheets("Supplies").Range("B2").Select

ThisWorkbook.Worksheets("Supplies").Protect

End Sub
*****************************************************

The only lines that did work are the commented-out ones:
' ThisWorkbook.Worksheets("Supplies").Rows("2:" &
..Rows.Count).AutoFit
' ThisWorkbook.Worksheets("Supplies").Range("B2").Select

Is there a way to get them to? The first one is to autofit the rows
so that after the data is deleted, they shrink back to standard size.

Also, it would be nice to be in cell B2 when I switch back to the
SUPPLIES sheet.

By the way, is there a command to do that, to travel to another sheet?
I don't recall anything like that.

Thanks! D