Prev: Scrollbar Repeats
Next: How to send email with no outlook and SMTP server ?
From: GS on 26 Apr 2010 20:22
Hi Peter,
I went looking again and found Helmut's post in the Excel NG. I didn't
see the solution you posted here as it's not the same code. I did see
JLGWHIZ's solution and I must admit it never occured to me to use that
technique. It's a way to achieve the same thing without selecting
anything, which is exactly what the OP wanted.

The best part is I also learned from it myself!
--
Garry


From: GS on 26 Apr 2010 20:29
I owe you an apology!
Turns out the OP finally did ask in the Excel NG. Also turns out
someone had a solution that involves setting window splits at the
row/column coordinates first and then set FreezePanes = True. I didn't
know that little trick! It's exactly what the OP wanted!
--
Garry


From: GS on 26 Apr 2010 21:33
After serious thinking Peter T wrote :
> "GS" <GS(a)discussions.microsoft.com> wrote in message
> news:uov0N2X5KHA.5880(a)TK2MSFTNGP04.phx.gbl...
>>> Thanks to all for your input.
>>> In this case the chance my program is affecting a user is very small.
>>> My program creates the new workbook at 6:00 in the morning, running
>>> on one computer. I was there, the shift changes at 6:00, there are some
>>> minutes when they don't use the computer, they tell their relief what
>>> happened in the night, what to do next, what to look for, ...
>>>
>>> However, I would have liked to use a function that does this without
>>> having to set the active cell first.
>>> I think the code provided by GS will eliminate the slight chance that
>>> my program will interfere with a user.
>>>
>>> Thanks again.
>>>
>>> Helmut.
>>
>> You're welcome!
>> Unfortunately, the specific property you are trying to work with uses the
>> active cell as its marker for setting the new "home" position of the
>> resulting scrollable area. That precludes that this position must be set to
>> the desired cell BEFORE setting its value to TRUE. Otherwise, Excel offers
>> no other way to determine the marker.
>
> I've just seen and responded to the question in excel.prog' and wandered over
> here . As I posted in reply to Helmut's Q asking if freezepanes can be set
> without activating appropriate workbook, sheet and cell -
>
> "You can, indeed should, do almost everything in Excel without use of
> activate or select. However there are just a few things with the Window
> object you do need to, and what you are trying to do is one of them. If you
> are using VB6 and automating a hidden instance, there's no need to make the
> application itself visible.
>
> code sample snipped
>
> There are also one or two settings that can only be set with at least one
> visible workbook.
> "
>
> I notice the suggestion earlier about reactivating the previous selection,
> easier this way
>
> dim rngPrev as excel.range
>
> on error resume next
> set rngPrev = xlApp.selection
> on error goto 0 ' or other handler
>
> ' activate required workbook, worksheet, cell, freeze panes, etc
>
> if not rngPrev is nothing then
> rngPrev.parent.parent.activate ' the workbook
> rngPrev.parent.activate ' the sheet
> rngPrev.activate ' the range
> end if
>
> The original selection might not be a range so maybe better to at least store
> the sheet and reactivate it (after its parent workbook)
>
> If app is visible to the user could temporarily disable xlApp.ScreenUpdating
>
> Regards,
> Peter T

Ya know.., I spend a lot of time actually reading posts in both forums
in the hopes of learning better ways to do things in general, or to
educate myself on stuff I want to learn. That precludes that I've seen
many different ways to get to the same result. I've also seen a lot of
people come and go. You've been around for some time and apparently
have a good deal of VBA/Excel programming knowledge and experience.
I've read many good posts by you.

You state here that you have an easier way to address the issue of
restoring the previous selection. I'm sorry to say but I just don't see
how your 9 lines of code qualifies as easier than my 3 lines in the
OP's context of filling the sheet with data only. I don't mean to
offend but my definition of easier seems to be different than yours.
(..most probably because it's formed from the viewpoint of me being a
newbie at this, and always looking for the best approach from the
examples I find, wherever I find them) The best solution for the task
at hand is usually considered a good approach. The long way around
(just in case of this, that, or something else) is never going to be
easier in my book. I would have thought someone with your years of
experience with Excel VBA would recognize that when you see it. Not
only does my 3 lines fit the context of the task at hand, it'seasier to
program, and they're more self-documenting and so easier to understand.

You also state that the original selection might not be a range. That
could very well be the case if an object is selected. I hadn't
considered that since the OP states he starts with a new workbook every
day, and that he just wanted to lock Row1/Col1 from scrolling. He
didn't mention anything about objects on the sheet, stating he was
inserting data only. That said, your suggestion to put the selection
into an object variable is definately a better idea in general<IMHO>.
Thanks for making that point! It does require more coding but hey, ..if
it fits the task at hand I'm all for it! I might explore putting the
code into a reusable function though, because it looks like a good
candidate for that.

In the end, JLGWHIZ's solution finally gives the OP what he was looking
for and so at least he's happy. I know I learned yet some more from
both suggestions and so I'm happy too.

Kind regards,
Garry
--


From: Peter T on 27 Apr 2010 04:00
"GS" <GS(a)discussions.microsoft.com> wrote in message
> After serious thinking Peter T wrote :
>> "GS" <GS(a)discussions.microsoft.com> wrote in message

<snip>
>
> Ya know.., I spend a lot of time actually reading posts in both forums in
> the hopes of learning better ways to do things in general, or to educate
> myself on stuff I want to learn. That precludes that I've seen many
> different ways to get to the same result. I've also seen a lot of people
> come and go. You've been around for some time and apparently have a good
> deal of VBA/Excel programming knowledge and experience. I've read many
> good posts by you.

Hope some of those posts were useful! FWIW I'm an Excel MVP, I lurk in this
group to learn, ask the occasional question and perhaps answer the odd Excel
topic.

> You state here that you have an easier way to address the issue of
> restoring the previous selection. I'm sorry to say but I just don't see
> how your 9 lines of code qualifies as easier than my 3 lines in the OP's
> context of filling the sheet with data only. I don't mean to offend but my
> definition of easier seems to be different than yours.

"Easier" was perhaps not the right word, besides it was late (my time) and I
hadn't studied the OP's entire scenario. Considering now all purpose generic
use (hope not too far beyond the scope of interest here!)

..ActiveSheet.Range(sCurrentSelection).Select

that alone would be fine if -
a) the sheet and/or workbook had not been previously deactivated and
b) the selection was a range
c) the range has an address of under 255 characters, ie not a multiple
selection of say 15+ areas (not typical but possible).

A "better" approach would be to store the sheet and the selection as two
objects

dim objSht as Object ' (cater for chart-sheet & worksheet)
dim objSelection ' any type of selected object

set objSht = .ActiveSheet
set objSelection = .Selection

' dostuff, now restore

if objSht Is .ActiveSheet then
objSelection.select
else
objSht.Parent.Parent.Activate
objSht.Parent.Activate
' no need to re-select the old selection
end if

Of course this is more than your 3 lines and might not be necessary. But my
own restore function is probably closer to 100 lines. Even the above doesn't
cater for everyting, eg multiple windows, if a chart-item was selected,
disable events & screenupdating and not least error handling.

<snip>
> In the end, JLGWHIZ's solution finally gives the OP what he was looking
> for and so at least he's happy. I know I learned yet some more from both
> suggestions and so I'm happy too.

Not sure why you couldn't also see my post (as ou said in an adjacent
post), here's the code example I posted there (I didn't post it here before
as on seeing the thread I didn't think it needed)

Sub test()
Dim xl As Excel.Application, wb As Excel.Workbook, wn As Excel.Window

Set xl = New Application
Set wb = xl.Workbooks.Add

wb.Worksheets(2).Activate
wb.Worksheets(2).Range("D4").Activate

xl.Windows(1).FreezePanes = True
xl.Visible = True

' might want to do xl.usercontrol = true

End Sub

Regards,
Peter T


From: GS on 27 Apr 2010 16:04
<snip>

Peter T brought next idea :
> "GS" <GS(a)discussions.microsoft.com> wrote in message
>> After serious thinking Peter T wrote :
>>> "GS" <GS(a)discussions.microsoft.com> wrote in message
>
> <snip>
>>
>> Ya know.., I spend a lot of time actually reading posts in both forums in
>> the hopes of learning better ways to do things in general, or to educate
>> myself on stuff I want to learn. That precludes that I've seen many
>> different ways to get to the same result. I've also seen a lot of people
>> come and go. You've been around for some time and apparently have a good
>> deal of VBA/Excel programming knowledge and experience. I've read many good
>> posts by you.
>
> Hope some of those posts were useful! FWIW I'm an Excel MVP, I lurk in this
> group to learn, ask the occasional question and perhaps answer the odd Excel
> topic.
I know exactly who you are, and yes, your posts were usually very
helpful! I've been visiting the XL programming NG since shortly after I
began programming (Thanksgiving weekend in 2003 I opened my first
programming book. -Been hooked ever since) I started with Walkenbach
books but now I'm a devotee of the "Bovey, Bullen, Green" movement
after connecting with Rob Bovey in June '04. I consider Rob my XL
programming mentor, and I can't say enough good things about the
tremendous help and support he's given toward my learning process<g>

I'm definitely not an XL MVP but I do consider myself somewhat of an
'intermediate level' Excel user. I've been working with XL since v4
(when it shipped separately in a dozen or more floppies) after
migrating from Lotus123. (The college I was teaching at switched to
M$O, forcing us to follow along. That was easily done since they
provided the software to us for free via the ProDev Fund, which (at the
time) was a college paid program initiated to keep us up to date with
technology. This program was cancelled some time ago and so staff have
to pay for their software upgrades now.<g><Geez, ..nowing their mindset
I can imagine how really upset they must be about that!>

I hang out here mostly nowadays because I'm trying to learn as much as
I can about VB6. Rob turned me onto Karl E. Peterson's website back
when he first encouraged me to get VS6. -I've been hooked on VB6 ever
since!

I only visit the XL NG when I need to look for answers on something I
need help with. My approach there (and here) is only if I can't find a
posted solution I'll ask. Usually I'll email Rob, but sometimes I'll
want to inquire further about suggestions posted by others directly to
them.

>
>> You state here that you have an easier way to address the issue of
>> restoring the previous selection. I'm sorry to say but I just don't see how
>> your 9 lines of code qualifies as easier than my 3 lines in the OP's
>> context of filling the sheet with data only. I don't mean to offend but my
>> definition of easier seems to be different than yours.
>
> "Easier" was perhaps not the right word, besides it was late (my time) and I
> hadn't studied the OP's entire scenario. Considering now all purpose generic
> use (hope not too far beyond the scope of interest here!)

Yeah, I've done that myself, only to go back after having slept and
read what I posted. After the initial shock I end up posting apologies
and redo's. (I hate when that happens!)<g> I try not to do that
anymore.

>
> .ActiveSheet.Range(sCurrentSelection).Select
>
> that alone would be fine if -
> a) the sheet and/or workbook had not been previously deactivated and
> b) the selection was a range
> c) the range has an address of under 255 characters, ie not a multiple
> selection of say 15+ areas (not typical but possible).

True enough. Obviously you've built a reusable proc that you can use
under a variety of scenarios within your XL projects. Always good to
have!

>
> A "better" approach would be to store the sheet and the selection as two
> objects
>
> dim objSht as Object ' (cater for chart-sheet & worksheet)
> dim objSelection ' any type of selected object
>
> set objSht = .ActiveSheet
> set objSelection = .Selection

I agree. I might do this a bit different since I tend to write my XL
code in VBA before importing to the VB6 project I use to automate XL.
In this respect I try to always code fully qualified refs so the only
changes I have to make after importing to VB6 are minimal. So doing a
Find/Replace on "Application" to use my "appXL" object in its place, or
"ActiveWorkbook" and "ActiveSheet" with "wkbXL" and "wksXL",
respectively is usually all that's required. I also do some of the
global XL objects by adding the appropriate VB6 line of code while
coding in VBA. For example, a line of code with "ThisWorkbook.Path"
will follow with a commented identical line using "App.Path". Recently,
I've been using global variables for that kind of stuff, which get
initialized at startup in InitGlobals(). The code then refrains from
using "ThisWorkbook" or "App", and uses the global variable "gsAppPath"
instead.

>
> ' dostuff, now restore
>
> if objSht Is .ActiveSheet then
> objSelection.select
> else
> objSht.Parent.Parent.Activate
> objSht.Parent.Activate
> ' no need to re-select the old selection
> end if
>
> Of course this is more than your 3 lines and might not be necessary. But my
> own restore function is probably closer to 100 lines. Even the above doesn't
> cater for everyting, eg multiple windows, if a chart-item was selected,
> disable events & screenupdating and not least error handling.
>
> <snip>

Nevertheless, I do like your idea of storing the selection in an object
variable. The why of that is obvious to you and me and so needs no
explanation. The OP is always the one who needs clarity of the how&why.
In this respect I was surprised that you would suggest all that 'dot
processing'. I think it would have been easier coding, and easier to
read/understand if you simply ref each object and directly act on it.

Example:
wndXL.Activate: wkbXL.Activate: wksXL.Activate: objUserSel.select

Just "git-r-done"! <IMO>There's no need for the If..Else construct.
It's not only extraneous coding that's harder to understand (ie: dot
hiarchy used) but it adds a performance hit every time it has to
evaluate the criteria. A simple error handler could take care of any
object related issues there. <FYI>I had a major back&forth with Rob
(+Rob and Stephen) about this where they proved to me beyond dispute
that (depending on where the data sits in the stack) this type of
coding can take volumes longer to process than the direct approach. As
a result I now avoid using it unless absolutely necessary!

Here's my point:
<a>
As a general rule, using unnecessary dot processing is inefficient
coding to begin with.

<b>
The OP has to evaluate through the dot hiarchy to understand what's
going on for every line. This might be easily understood by you and I
but not someone who's trying to learn. Sure, it's acceptable as a
"here: do-this" solution to run with, but my understanding of how
learning occurs says this isn't doing much for the learning process. My
understanding of the purpose for these NGs is to compliment the
learning process.

Not saying it's a big deal always, but if our app uses this type of
coding a lot it certainly adds up to costing in terms of overall
performance. One thing I learned from the session with Rob/Stephen was
to use a timer in procs (when developing) to test which approach is
best when in doubt.

>> In the end, JLGWHIZ's solution finally gives the OP what he was looking for
>> and so at least he's happy. I know I learned yet some more from both
>> suggestions and so I'm happy too.
>
> Not sure why you couldn't also see my post (as ou said in an adjacent post),
> here's the code example I posted there (I didn't post it here before as on
> seeing the thread I didn't think it needed)

I finally did see Helmut's post after reading your initial reply here.
His post was not there when I looked earlier (via website UI). I saw
your code when I revisited. It's basically what I suggested, difference
being mine was in context to the OP's stated scenario.

Fact is, we both failed to answer his Q, which was how to do it without
selecting anything. JLGWHIZ provided a nice little trick to accomplish
that. -Something that never occured to me to do. Now I know better!<g>

>
> Sub test()
> Dim xl As Excel.Application, wb As Excel.Workbook, wn As Excel.Window
>
> Set xl = New Application
> Set wb = xl.Workbooks.Add
>
> wb.Worksheets(2).Activate
> wb.Worksheets(2).Range("D4").Activate
>
> xl.Windows(1).FreezePanes = True
> xl.Visible = True
>
> ' might want to do xl.usercontrol = true
>
> End Sub
>
> Regards,
> Peter T


First  |  Prev  | 
Pages: 1 2 3 4
Prev: Scrollbar Repeats
Next: How to send email with no outlook and SMTP server ?