initialise declaration
In a general module (insert=Module in the VBE) at the top
Public rng_source As Range
Public rng_x As Range
in the Sheet1 Module (assume the ranges refer to ranges in shee1)
Private Sub Worksheet_Activate()
Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")
End Sub
in the thisWorkbook module
Private Sub Workbook_Open()
With thisworkbook.sheet1
Set rng_source = .Range("B3:J11")
Set rng_x = .Range("L3:T11")
End With
End sub
--
Regards,
Tom Ogilvy
"Joe" wrote:
Thanks Tom,
Infact I had not got that correctly as I said before.
I had forgot to blank the original code.. so it was working from
there..
But I tried to implement what Tom has told about defining as range.
still I have problem.
I try to put what the current situation is.
All my code was in the Object "Sheet1".
As Bob suggested I wanted to put that in the Object "ThisWorkBook"
under the event, "Private Sub Worksheet_Activate()"
So it looks like this..
*************
Private Sub Worksheet_Activate()
Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")
End Sub
*************
It shows error.. :(
Then I tried to put the variable declaration also into "ThisWorkBook"..
and later in "sheet1"
********
Public rng_source As Range
Public rng_x As Range
***********
Both resulted in Error :(
I dont know if this info is enough to identify the problem.. But Hope
it is..
Really appriciate ur helps..
Thanks
Joe
Tom Ogilvy wrote:
Since you said:
2. If I declare that way, will it be visible in the Module Functions
and Bob didn't address that, just to be sure,
Public rng_source as Range, rng_x As Range
should be placed at the top of a general module, not in the sheet module
where the activate event is located. Also, each variable should be typed
separately. As you originally wrote it, rng_source was declared as a variant
while only rng_x was declared as a range reference.
--
regards,
Tom Ogilvy
"Joe" wrote:
Thanks a lot Bob..
It worked perfect!
Regards
Joe
Bob Phillips wrote:
Private Sub Worksheet_Activate()
Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")
End Sub
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Joe" wrote in message
oups.com...
Hello guys,
My basics of VBA are poor..
I would like to know How I can Initialise certain things.. One time..
What I do now is..
' ***********
Public rng_source, rng_x As Range
Private Sub CommandButton1_Click()
n_Mat = 9
Set rng_source = Range("B3:J11")
Set rng_x = Range("L3:T11")
....
end sub
' ************
1. I want to decalre it outside the function, may be when the sheet is
loaded.. Pls guide me..
2. If I declare that way, will it be visible in the Module Functions
Thanks all,
Joe
|