This code deleted a cell from another sheet!

[MarkLyons2]

This code:

MsgBox ("Entering Sub SearchButton - Data!A2 is " & Range("Data!A2"))
Sheets("Settings").Select
Sheets("Settings").Activate
Sheets("Settings").Cells.Select
Selection.ClearContents
Sheets("Settings").Range("A1").Select
MsgBox ("Just before advanced filter - Data!A2 is " & Range("Data!A2"))

Will erase my search criteria on a *completely different* sheet.

Am I overlooking something obvious? Your help is very much appreciated. Wonderful forum you folks have here.
-Mark

 

[VoG]

I don't know (not sure I understand the question) but you don't need all that selecting.

Sheets("Settings").Cells.ClearContents

 

[Von Pookie]

Not sure if I am followoing this correctly, but you are not referencing the search data correctly.

Unless you somehow have a range specifically named "Data!A2", using Range("Data!A2") is incorrect. VBA does not use the same cell references as worksheet formulas.

If you want to reference A2 on a sheet named Data:
Sheets("Data").Range("A2")

 

[MarkLyons2]

Just asking about deleting cells in other sheets.

msgbox (Range("MySheet!A2")) works just fine. Try it.

 

[MarkLyons2]

I don't know (not sure I understand the question) but you don't need all that selecting.

Sheets("Settings").Cells.ClearContents

Originally I did not have it. I did that to *be sure* it could not reference the other sheet.

 

[Von Pookie]

msgbox (Range("MySheet!A2")) works just fine. Try it.

It would appear you are correct about that, sorry. I have never seen that coded that way before. I'm not going to start using it, though.

At any rate: There is nothing in the code you have *posted* that would delete anything on any other sheet. Is Data!A2 somehow linked to a cell on the Selection sheet? Say A2 contains the formula =Selection!$A$1. When you delete the contents on the Selection sheet, A2 gets wiped out as well since there is no value to display.

 

[VoG]

Re: Just asking about deleting cells in other sheets.

msgbox (Range("MySheet!A2")) works just fine. Try it.

I'm amazed But Von Pookie's syntax is the correct one to use.

Originally I did not have it. I did that to *be sure* it could not reference the other sheet.

The code I posted cannot possibly change anything on any other sheet.

 

[MarkLyons2]

msgbox (Range("MySheet!A2")) works just fine. Try it.

It would appear you are correct about that, sorry. I have never seen that coded that way before. I'm not going to start using it, though.

At any rate: Is Data!A2 somehow linked ...Say A2 contains the formula.... When you delete the contents on the Selection sheet, A2 gets wiped out as well since there is no value to display.

A2 is on a completely different sheet - "Data"
Is has an ordinary numeric value. It gets wiped out. I'm just going to make a global variable. Thanks anyway guy's. I can't expect you kind folks to explain microsoft - they work in mysterious ways.