lock first row and first column WITHOUT selecting Cell "B2" first?

Hi, 

I'm new to this group.
From a VB6-Program I create a new workbook with 1 worksheet.
After writing the title row I want to set the first column and the first
row non-scrollable. This is my actual code in the VB6 program:
    ws.Range("B2").Select
    ActiveWindow.FreezePanes = True

I tested it, it works. 

My Question: is there another way to do this WITHOUT selecting
the cell first? Doing it with an non-active window?

I've searched to no avail, but probably haven't asked the right questions
or used the correct terms. (English isn't my first language).

TIA,

Helmut.



0
Helmut
4/26/2010 6:22:17 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
759 Views

Similar Articles

[PageSpeed] 49

Sub FP()
    With ActiveWindow
      .SplitColumn = 0
      .SplitRow = 2
      .FreezePanes = True
    End With
End Sub



"Helmut Meukel" <NoSpam@NoProvider.de> wrote in message 
news:%23kl1n1W5KHA.3576@TK2MSFTNGP05.phx.gbl...
> Hi,
> I'm new to this group.
> From a VB6-Program I create a new workbook with 1 worksheet.
> After writing the title row I want to set the first column and the first
> row non-scrollable. This is my actual code in the VB6 program:
>    ws.Range("B2").Select
>    ActiveWindow.FreezePanes = True
>
> I tested it, it works.
> My Question: is there another way to do this WITHOUT selecting
> the cell first? Doing it with an non-active window?
>
> I've searched to no avail, but probably haven't asked the right questions
> or used the correct terms. (English isn't my first language).
>
> TIA,
>
> Helmut.
>
>
> 


0
JLGWhiz
4/26/2010 8:18:32 PM
Short answer is No!

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. Try this in VBA

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

There are also one or two settings that can only be set with at least one 
visible workbook.

Regards,
Peter T

"Helmut Meukel" <NoSpam@NoProvider.de> wrote in message 
news:%23kl1n1W5KHA.3576@TK2MSFTNGP05.phx.gbl...
> Hi,
> I'm new to this group.
> From a VB6-Program I create a new workbook with 1 worksheet.
> After writing the title row I want to set the first column and the first
> row non-scrollable. This is my actual code in the VB6 program:
>    ws.Range("B2").Select
>    ActiveWindow.FreezePanes = True
>
> I tested it, it works.
> My Question: is there another way to do this WITHOUT selecting
> the cell first? Doing it with an non-active window?
>
> I've searched to no avail, but probably haven't asked the right questions
> or used the correct terms. (English isn't my first language).
>
> TIA,
>
> Helmut.
>
>
> 


0
Peter
4/26/2010 10:20:28 PM
On Apr 26, 11:22=A0pm, "Helmut Meukel" <NoS...@NoProvider.de> wrote:
> Hi,
>
> I'm new to this group.
> From a VB6-Program I create a new workbook with 1 worksheet.
> After writing the title row I want to set the first column and the first
> row non-scrollable. This is my actual code in the VB6 program:
> =A0 =A0 ws.Range("B2").Select
> =A0 =A0 ActiveWindow.FreezePanes =3D True
>
> I tested it, it works.
>
> My Question: is there another way to do this WITHOUT selecting
> the cell first? Doing it with an non-active window?
>
> I've searched to no avail, but probably haven't asked the right questions
> or used the correct terms. (English isn't my first language).
>
> TIA,
>
> Helmut.

If you want to avoid select for speeding up the code you can use
following line

application.goto reference:=3Dws.Range("b2"),scroll:=3Dtrue
0
Javed
4/28/2010 6:50:31 AM
Reply:

Similar Artilces:

Exchange 2003 SP2, Outlook 2007 B2, RPC-HTTPS
Hi, I am reposting in this forum to see if anyone has had any luck configuring Outlook 2007 B2 for RPC over HTTPS in a single-server Exchange 2003 SP2 environment. I have an Outlook 2003 client configured on an XP SP2 and it works great from my home. On the same connection, I have a Outlook 2007 configured (tried both upgrading from Outlook 2003 and a fresh install with same results) and it will not connect. When I run outlook.exe /rpcdiag on the 2007 client, I can see if trying to connect to the server and then I can see it accessing our DC but it's always "Trying to conne...

Linking worksheets (ex. =sheet1!B2)
What I want to do seems so simple but Excel is doing some weird stuff and I'm stumped. I'm starting with two worksheets. The first is my raw data template. The second is my summary page, made up of simple formulas, many of which are referencing cells from the raw data worksheet such as(=detail!B2). This initial setup works fine. What I want to do now is re-use the detail worksheet to produce multiple summary pages for individual products. My method was this: 1. Make copy of summary worksheet 2. Rename new worksheet (new worksheet has the same formulas as original) 3. &qu...

Function isnt working, just displays the formula in cell?eg A2+B2
Try =A2+B2 with the equals sign (=) -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ru" <ru@discussions.microsoft.com> wrote in message news:DD92C6B6-6434-4438-856E-55FED982711D@microsoft.com... > ...

=SUMIF(B2:B13,"=Jan",C2:C13)
I'm looking to use the "sumif" formula listed above. However my data lists Jan-03 and Jan-04. How do I account for the years in the above formula? Thanks in advance!! If you want to sum January for both years and the data is actual text, not the date Jan 1, 2003, formatted to show Jan-03, =SUMIF(B2:B13,"Jan*",C2:C13) note the wildcard. On Wed, 26 Jan 2005 13:48:14 -0800, "a921" <anonymous@discussions.microsoft.com> wrote: >I'm looking to use the "sumif" formula listed above. >However my data lists Jan-03 and Jan-04. How do I...

Copying data from cells A1,B1,C1 on tab1 to tab2
I want to copy data from cells A, B and C on Tab 1 of my workbook to other tabs in my workbook dependant on which option is selected from a dropdown list in cell D of Tab1 Example: I have 'company name' in cell A, 'Account Number' in Cell B and 'Sales' in cell C on tab 1, I then select 'Blue' from a dropdown list in cell D on tab 1. I then want excel to copy the data in cells A, B and C, on tab 1 into another tab in the same workbook named 'Blue'. can anybody help please???? -- Thanks, Dan Select the sheet tab which you want to wor...

Outlook 2003 B2 does not show X2K "All public folders"
My Outlook 2003 Beta 2 is running with a local copy of the folders set. I can't see any of the Public Folders on my Exchange 2000 SP3 server. ...

all long column of cells equal to whatever I put in B2
For some reason I am getting nowhere trying to do a very simple thing. I have a column with many of the cells for a long stretch that I want to be equal to whatever number I put in B2. As I'm building this spreadsheet, it seems to me that it should be a very simple matter to "tell" all those cells to just be equal to whatever is in B2, they using format painter, etc.,.... they don't copy "=B2" part of the previous cells, but instead just copy whatever the number is .... which means that when I change the number in B2, it won't change them. Any ideas? th...

Pub2000 wont start without CD
I cannot start Publisher2000 without the windows installer calling for = me to "Insert the 'Microsoft Office2000 SR-1 Disk 2' disk and click OK." = I've tried repairing Office, and Installing features, and removing and = re-installing Publisher2000. I set everything to run from my computer, = but it still keeps asking for the Disk2 CD when I start Pub2000. Every = other Office2000 application starts OK. I'm running Win98SE, with all = the updates, and Office2000 with SP3 & all other updates.=20 --=20 Change the obvious in the address if replying = ..(JoeLaureano...

Deleting All Cells with Number formulas Ex. =2+3 not = A1+B2
Deleting All Cells with formulas with Numbers Ex. =2+3 not = A1+B2 I am trying to create a copy of workbook with only formulas, but some cells exist with =2+3 which I do Not wanted. How can I delete all of this in a single click (Not Individualy) this should do it Sub cleanformulas() For Each c In Selection If c.HasFormula And _ IsNumeric(Mid(c.Formula, 2, 1)) Then c.Clear Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "Samshun" <Samshun@discussions.microsoft.com> wrote in message news:2270ADD4-A736-4B10-837B-5FDADE3089AD@microsoft.com... >...

Problem with Outlook 2007 B2
Hi, I seem to have an issue with the new Outlook 2007 Beta 2. Standard setup (no changes made during install, no changes made from within Outlook). I am using an existing PST file from OL2003. All messages received prior to the installation are viewable within Outlook 2007. However, any new message received has the following: - The message header can be viewed in the Inbox - The message body can NOT be viewed in the reading pane (the reading pane contains the sender, subject, attachments, but no body). - If I enable quick preview in the Inbox folder view, the first fiew lines of th...

More than 7 If Functions Example is IF(A2="Bongaigaon", B2*40, ..
i want to use if Command in which 19 times If Function to be used for Example of the Function is =IF(A2="Bongaigaon", B2*40, IF(A2="Dhemaji", B2*100, IF(A2="Dhubri", B2*50, IF(A2="Dibrugarh", B2*65, IF(A2="Golaghat", B2*50, IF(A2="Guwahati", B2*20, IF(A2="hojai", B2*50, IF(A2="Jorhat", B2*60, If(A2="Kharupetia", B2*50, up to 19 times Hi - You really need to create a table with the 19 names in one column and, in the column to the right, the vaues associated with each name. That would ...

If B2=x, then for all values of A2, place y in C3
Is this a macro that I have to run, or can I perform this as an equation/function? I'm thinking I can do it with an array, but can't quite put it together. I'll give an example: A B C 1 1 Alex Oldest 2 2 Barbara Middle 3 3 Chloe Youngest 4 1 Oldest 5 3 Youngest 6 2 ...

Can the cell function return "B2" instead of "$B$2"?
Is there a way to get the Cell function to return just the column and row without the "$" signs? =cell("address",maxrate) --> "$B$2" I would like just "B2". hi, ! > Is there a way to get the Cell function to return just the column and row without the "$" signs? > =cell("address",maxrate) --> "$B$2" > I would like just "B2". is this a valid alternate ? =substitute(cell("address",maxrate),"$","") hth, hector. On Sat, 06 Sep 2008 20:33:46 -0700, Square Peg &l...

2007 B2 MSOO has encountered a problem
More often then not when I click to open an email in my inbox I get this MSOO pop-up with a checkbox, checked telling me MSOO has encountered a problem and needs to close. Has this been an issue for others and is there a fix. If you have Adobe Acrobat Pro 6/7 (not the reader), go into Control Panel, Add/Remove Programs, click change for Adobe Acrobat Pro and remove the Office & Outlook plugins. Patrick Schmid -------------- http://pschmid.net "Jax" <Jax@discussions.microsoft.com> wrote in message news:A74147F4-CD75-4FF8-98A7-5A18B01A8FDD@microsoft.com: > More of...

COUNTIF(B2:V2),"*" AND Font.ColorIndex=1??
I want to count any character in a row, only if it's font colour is black. Or put it another way, I want to count the number of characters in a row, only if their font colour is not light green (or whatever colour I choose). I do not want to count blank cells either. Any ideas out there much appreciated. Excel 2003, Office XP. see if this helps - Frank posted it earlier http://www.xldynamic.com/source/xld.ColourCounter.htm -- duan ----------------------------------------------------------------------- duane's Profile: http://www.excelforum.com/member.php?action=getinfo&userid...

Want to write formula
Hello I want an if which checks two conditions If it was in C++ would look like this: if(H3 == "TRUE") || (B2 < B3) //update cell contents to "TRUE" The || is like an OR - ie can be this or that. Hopw can I do this in Excel VBA? Angus Hello Angus, If (Range("H3").text = "TRUE") or (Range("B2").value < Range ("B3").value) then .... Regards, Bernd =IF(OR(H3=TRUE,B2<B3,TRUE,FALSE) This can be simplified to OR(H3,B2<B3) Not I have not use quotes as in "TRUE" since you can type TRUE into a cell an it is...

How to Delete Contents of D2 if B2 = "SD"
How do I Delete Contents of D2 if B2 = "SD". Then continue through every row in the file? You can't delete the contents using worksheet functions, but if D2 contains a formula, you can make the cell appear blank: D2: =IF(B2="SD","",<your formula here>) If D2 contains a constant/user entered value, or if you really want it blank, then you'll need to use an Event macro. If B2 will contain a user entry, put this in the worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target ...

if a2 = b2 then insert d2 in e2
Hi, I am trying to combine information in 2 different sheets. Sheet one has a column with numbers and a column with titles. Sheet two has a column with the same numbers and other information I would like to use. What I would like to do is pull the title information from sheet 1 into sheet 2 based on the unique numbers. Below the title to 3263 is superdog and I want to insert superdog in the column next to 3263 on sheet 2. Sheet 1 Sheet 2 A B A 1254 book 3263 3263 superdog ...

Trying to get a blank cell presented if B26=0
IF B26=0, how do I present a blank cell in another place, or put an amount if not true. I have used =IF(B26=0,0,Sheet1!E2) , but I get a $0.00 figure which I do not want. Even I if I used a blank cell for the true portion, I get then same $0.00 figure. I get the right amount for false. I am preparing an excel program for a small plowing business. Hi, Try this =IF(B26=0,"",sheet1!E2) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. ...

Need Formula Help Copying "Image" URL from B2 to A2
Hi all sorry if this is a repeated question in this ng. I just could not find one related my querry. I have sitmap list in excel. I want to separate the links with the word images in it. http://.........../display.asp?hs=7130g&tariff=tmobxnetflext35.18 http://............/display.asp?image=large&hs=7130g both links are in B1 and B2, I want to copy the the link with the word "image" in the middle to A2. what formula can I use in A2? any tip would be appreciated. Thanks in advance. tim try =IF(FIND("image",B2),B2,"...

Mouse dissappears in Outlook 2007 B2
Notice an annoying "feature", my mouse will dissappear in Outlook for some reason and have to drag it off the Outlook window to like the tastbar or desktop and back again to see it. Dang, was hoping 2007 would have a universal selection in turning off that $@%$# GROUP view and READING PANE. I want neither, but have to go through all my folders (26) to change them to "normal" viewing. Also, guess Beta testers know this, but the scanpst.exe doesn't work in B2. Keep getting "procedure entry point DDLUnintInstace@" errors in fixing on ..pst file Outlook ...

If B1 is not empty but B2 is ... conditional formatting "formula"?
I've set up a sheet that works great _except_ if B1 has a value in it but B2 doesn't. I've set up conditional formatting. When B1 is empty, no colour or font attributes in cells B3 to B14. Good. If B1 does have a value in it, the cell colour and font attributes in B3 to B14 are the same as if B1 was not empty but B2 was also not empty. I'd like to differentiate between the 2 in conditional formatting. If B1 is not empty yet B2 is, that will be my 3rd conditional formatting. However, nothing I've tried works. Here's what I have now ... Cond Form #1: =NOT(ISB...

In Pub2007 how to rotate pics without distorting their shape?
In Publisher 2007 when rotating pictures at anything other than 90 or 180 degrees - the pictures become distorted and are no longer square or rectangular. This never happened in my older version of the pgm. Anybody have a solution? I have contacted M.S. and so far have not received satisfactory answer. Violinrosie wrote: > In Publisher 2007 when rotating pictures at anything other than 90 or > 180 degrees - the pictures become distorted and are no longer square > or rectangular. This never happened in my older version of the pgm. > Anybody have a solution? I have contacte...

How to do
Microsoft office 2007 XL. How do I perform a validation where cell B2 is only valid if cell B1 is blank or zero. Have read the help text on this including the section on "what is allowed on the context of another cell". As a result I tried many different things but cannot get it right (if it can be done). The data validation only appears to have equal to, greater than, less than etc., for a field compared to the field being validated. It does not seem to have the scenario where a field is valid solely on the value of another field (i.e. if(And(B1<>0,B2<>0),e...

MMULT(A1:B2;TRANSPOSE(A3:B3))
Hello I try to multiply a matrix with a vector. The matrix is in A1:B2, the vector is in A3:B3. Thus I need to tranpose the vector first. I tried: =MMULT(A1:B2;TRANSPOSE(A3:B3)) However this results in "#VALUE!". What is wrong? Since the actual problem is more complex, I cant store the vector in another way into cells, so that it would be already in the correct form. Greetings Flo Did you hit ctrl-shift-enter and did you have a range of 2 rows by 1 column selected when you array entered the formula. Excel's help explains it more thoroughly. sensorflo@gmail.com wrote: >...