how can I see if an array contain a certain variable?

[Ronaldo]

Hi,

At least do I belive I should use arrays to solve it, but did sleep during
the array part of the course

In an Worksheet_Deactivate event, I want to compare if the Target range is
into one of my known sheets or if it is an unknown sheet. This will make me
know if Target is another workbook, since there will be no more sheets in my
workbook. I thought of running a loop through my array with 13 woorksheets
and see if the "ActiveWorksheet" is there.

I have started to give name the arrays like:

shtArray(0) = Sheet1.Name
shtArray(1) = Sheet2.Name
:
and thought of running a Loop like:
"If ActiveSheet.Name <> shtArray" Then

I has been thinking about using woorkbook_deactivate event, but can't find
out how i can get the Target range and continue with my code in a right
way.

Better ways to solve it are of course welcome, but maybe this give me a
reason to learn arrays once and for all


- Regards -

 

[Frank Kabel]

Hi
but why do you want to check this?. If you only want to make sure that
it is not the same workbook why not compare the workbook objects?

 

[Bob Phillips]

You've lost me mate. What is the Target Range in this instance?

If you just wasn't to see if a worksheet contains a specific worksheet,
there are easier ways.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ronaldo]

Hi, thank you both for the reply. Here is a background reasons. Please note
that I still need the help as asked in "Possible Solution" here below.

REASON
With every change in some sheets, I do a number of changes both in the
active sheet (trigger = worksheet_change) and in a couple of other sheets.
When I do a DRAG AND DROP from the sheets to an other workbook, the
activesheet is the target sheet in the new workbook.

If I use some events like worksheet_deactivate in ThisWorkbook module, I
can't see how I will be able to use my current procedures that is built on
Worksheet_Change(ByVal Target As Range) .

There is also two more specific applications: one use ActiveCell and I
havn't created it myself (contributor is Jim Recht) and feel a bit afraid
of starting to mess with it. This I had to change to work with a Range/Cells
instead of ActiveCell as now. It's a procedure that correct the row height
when the Target is Merged cells: SEE "AutoFitMergedCellRowHeight" below.

The other is a commonly used procedure I use to be able to restore selected
range and activated cell after I run a procedure that has to copy paste
(can't do that another way, Excel is the limit) cell formats. In the
beginning of the trigger event "Worksheet_Change" I send the locations to
the static variables by just Call "SetSaveLoc" and pick them up in the end
of the procedure with Call "GetSaveLoc". SEE the three subs SetSaveLoc,
GetSaveLoc & SaveLocation. When pasting outside the Workbook, it Set the
locations of the new Workbook instead.

There might be workarounds, but in total I thought that it might be easier
to define the worksheets and put a little trigger in the worksheet_Change
event to act if all of a sudden the Target is in wrong woorksheet.

POSSIBLE SOLUTION
Actually, maybe I should put the trigger in the "SaveLocation" procedure to
check if it is the right workbook ("Wb") - but I still need to compare with
a list of "allowed worksheets" in my woorkbook, doesn't I? It is 10
worksheets - isn't looping through arrays the best thing to do? I like other
ways of course - I have lived my life this far avoiding arrays...


- Regards -


REFERENCE SUBS:
Public Sub AutoFitMergedCellRowHeight()
'Used in WS_Change in Sheet 1 to 8
'*********************************
Debug.Print "Grund AutoFitMergedCellRowHeight"
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single

If ActiveCell.MergeCells Then
Application.ActiveCell.EntireRow.AutoFit

With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + _
MergedCellRgWidth
Next

.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.Cells.Locked = False
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

Public Sub SetSaveLoc()
'*************************
SaveLocation (False)
End Sub

Public Sub GetSaveLoc()
'*************************
SaveLocation (True)
End Sub

Public Sub SaveLocation(ReturnToLoc As Boolean)
' When (True) it "Set in memory" WB, WS, R
' When (False)it Activate workbook, worksheet, range
'**************************************
Static Wb As Workbook
Static ws As Worksheet
Static R As Range

If ReturnToLoc = False Then
Set Wb = ActiveWorkbook
Set ws = ActiveSheet
Set R = Selection
Else
Wb.Activate
ws.Activate
R.Select
End If
Exit Sub

End Sub

 

[Bob Phillips]

Why not build the code into procedures in a standard code module and call
them from Worksheet_Change and Worksheet_Deactivate?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ronaldo]

Well, that sounds like what I was proposing to do if I put the trigger in
the "SaveLocation" procedure - but that is not optimal either since nearly
every change you do will pass through this code, just for a few occantions
of a possible drag- and drop to an other workbook.

And just by putting code into workbook_deactivate will not stop the
worksheet_change code to run, I guess?

I continue with a new thread since the question doesn't fit the headline
more.

Kind regards for your support

 

[Alan Beban]

Hi,

At least do I belive I should use arrays to solve it, but did sleep during
the array part of the course

In an Worksheet_Deactivate event, I want to compare if the Target range is
into one of my known sheets or if it is an unknown sheet. This will make me
know if Target is another workbook, since there will be no more sheets in my
workbook. I thought of running a loop through my array with 13 woorksheets
and see if the "ActiveWorksheet" is there.

I have started to give name the arrays like:

shtArray(0) = Sheet1.Name
shtArray(1) = Sheet2.Name
:
and thought of running a Loop like:
"If ActiveSheet.Name <> shtArray" Then

I has been thinking about using woorkbook_deactivate event, but can't find
out how i can get the Target range and continue with my code in a right
way.

Better ways to solve it are of course welcome, but maybe this give me a
reason to learn arrays once and for all


- Regards -

You might want to consider adding a reference to Microsoft Scripting
Runtime and using something like

Sub qwerty1()
Dim x As Dictionary, i As Long
Set x = New Dictionary
For i = 1 To Worksheets.Count
x.Add Item:=Worksheets(i).Name, key:=CStr(Worksheets(i).Name)
Next
If x.Exists("Sheet3") Then
'Do whatever
Else
'Do whatever else
End If
End Sub

Alan Beban