Free Microsoft Excel 2013 Quick Reference

[Solved] VBA: How to control scrolling area dynamically

I have an interactive sheet that I want to control the scrolling area dynamically. I now have a code line
ActiveWorkbook.Worksheets("MySheet").ScrollArea = "A1:BY248" but I want to change the number of rows in the scroll area from 248 by code. I know how to get a count of the rows I want in the scroll area but don't know how to get that count into my line of code that defines the scroll area.

Post your answer or comment

comments powered by Disqus
A little help here please... I have problem in restricting scroll area in a sheet. I've tried in sheet properties (Alphabethic tab- scroll area) but it didn't work when I reopened the file. When I check the properties, it was restored to its original settings. I don't know how to save it. I think it is more reliable to use vb code than setting up the sheet properties. Thanks in advance...

I have found Worksheets(1).ScrollArea to restrict viewing areas but it will
only work with static A1 style of referencing cells.

I required code to the effect that the scroll area is restricted but
dynamically, since I cannot know the limits of the area beforehand.

Any hel will be appreciated.



i wonder how to control the size of a comment i added in a cell with vba.
i failed all,


Hello, All

Is anyone knows how to control the UserForm's position?
It seems that i couldn't to find the Screen.Width or Screen.Height property
in VBA.

how to calculate the area under the curve in the excel?

Hello experts!

Is it possible to control the chart properties from the worksheet?

I know it is possible to control legend texts (series names).

I am especially interested in controlling the ranges of series. How to
control the title and other texts on the chart would be also useful.

Best regards,


How to control EXPORT of tables from Access to Excel?

I have clicked export from Excel to Excel but tables seem some top row with fileds that are not supposed to be there. How to control this when I export to excel?


I heard that it is possible to control the excel
macro security level using vba scripts,
does anybody have any idea on how to do this?

Thank you in advance


I can control the scrollarea of my workbook with the scroll area properties. I'm currently using a template and once a new is created the scull area remains unchanged.

I'd like the scrollarea to be limited to A1:U65 for all sheets within the template.


Hi – I’m really hoping someone can help me with some code to display data in the format I’m looking for. I’m still learning about vba code and I’ve been stuck trying to find a solution, as the ranges in my data will change from week to week.

I have attached my data sample here. I have a range of values in column H that will change in my future data samples, so the code has to be based on the number of unique values in this dynamic range. The values in column A are also unique, they are listed vertically to correspond with each unique value in column H.

MY DESIRED GOAL: I am trying to have the vertical data in columns A, H-J output horizontally based on my dynamic range in row H. I used a formula to calculate the number of unique values in column H, but I still need to figure out how to make the formula dynamic (the formula is located in cell L1).

I created an example of what I want the data to look like in the end, you can see this to the right of column L. I have a grid where the unique values from column H are listed across the top starting in cell O1. Underneath that, there are two horizontal rows for each value in column A, one row is hard-coded titled “weekend” and the second row is hard-coded titled “weekday”. I used two different colors in attempt to better illustrate. The data from columns I-J should output horizontally in this grid, starting in cell O2 in my example. Once the first two rows of data have been populated in the grid, the code needs to know to move down to begin the next loop (i.e. move to O4-O5 to work on the data for the next unique value in column A, which starts in row 22 in my sample). This needs to continue until all rows (i.e. in my sample there are 1121 rows of data, but the number of rows will be dynamic in future data) have been outputted into the grid. The biggest problem I have is that this data sample will change from week to week, the only thing constant is that I know what type of data will be in each column, but the number of unique values in column H will always change.

Note: it doesn’t matter to me if the final data is outputted on the same tab like I have it now, or if it’s on a new tab.

CODE: I don’t have much code at this point since I’m not quite sure from what angle to come at this with, I just have a few very basic samples that I have come up with so far. Here is some static code for getting the values for O2-O3 based on the values in I2 and J2; this is just a model to work off of to build out the grid I need, again I need to figure out how to make it dynamic:

If Range("I2") = "No" Then 
    Range("O2").Value = "No" 
End If 
If Range("I2") = "No" And Range("J2") = "Yes" Then 
    Range("O3").Value = "Yes" 
End If 
If Range("I2") = "No" And Range("J2") = "No" Then 
    Range("O3").Value = "No" 
End If 
 'if it's a weekend event
If Range("I2") = "Yes" Then 
    Range("O3").Value = "No" 
End If 
If Range("I2") = "Yes" And Range("J2") = "Yes" Then 
    Range("O2").Value = "Yes" 
End If 
If Range("I2") = "Yes" And Range("J2") = "No" Then 
    Range("O2").Value = "No" 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I also have some code to transpose the number of unique values listed in column H to list them horizontally starting in O1, but my formula in L1 somehow needs to be modified to be dynamic.

iNumEvents = Range("L1") + 1 
Range(Cells(2, 8), Cells(iNumEvents, 8)).Select 
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ 
False, Transpose:=True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be truly appreciated. This is my first time posting so please forgive anything I may have overlooked.

Hi all, long time listener, first time caller...

I have a single project in Access that basically runs SQL against our Oracle data warehouse and populates/formats the data into various reports in Excel. I have one module ('Utilities') that contains the subs that are used across all types of reports, ie 'Open_Excel', 'Get_Report_SQL', 'Populate_Data', 'Add_Totals_Line', 'Format_Header', etc. Additionally, I have separate modules for each 'family' of reports, ie 'New_Product_Reports', 'Sales_Commission_Reports', 'Profit_Reports', etc. Each of the 'report modules' contains the specifics (ie the headings/SQL/formatting particulars) for the various reports, and then calls the subs in the 'utility module' to build the spreadsheet reports.

There are similar steps in each of the 'report modules', for example each one of these contains a sub called 'Report_SQL' which builds a SQL statement for a particular report. Creation of an actual report involves running a series of subs, some existing in the report module and some in the utility module. The issue I'm having is identifying for the utility module which of the same-named subs it's supposed to be calling. In other words, since I have 'Report_SQL' in (for example) 10 different report modules, within the utility module I can't refer to 'Report_SQL', I have to refer to 'New_Product_Reports.Report_SQL', 'Sales_Commission_Reports.Report_SQL', etc. I'm currently doing this via a Case statement, ie

Case "New_Product_Reports" 
Case "Sales_Commission_Reports" 
End Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What I would like to be able to do is come up with something like:

CallStatement$ = CallingModule & ".Report_SQL" 
Call CallStatement 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
...but this won't compile, as VB is looking for a sub, function, etc, rather than a text string to be invoked from the 'Call' command.

I'm sure there's a way to do this, I've searched this and other forums and been able to figure out how to directly derive the module name without having to hardcode it, but I don't know how to dynamically call it from within the 'utility' procedure. Sorry for the rambling and if this is a basic issue, I'm a SQL developer learning VB by fire...


I'm using some code from the site and it kinda works.

I'm trying to limit the scroll area.

This is the code

Private Sub Worksheet_Activate()
Me.ScrollArea = "a1:m25"
End Sub

Problem is when the sheet is the only sheet in a workbook it doesn't fire the worksheet_activate event and you can scroll anywhere. If I insert a sheet and then select the sheet with the macro it works.

If the sheet is the active sheet when the workbook is open the macro doesn't work either.

Am I missing something???


What VBA code is used to 'control' how information in a MsgBox appears?

I want up to two lines to appear, then skip a line (i.e. leave line blank), then more lines to appear.

Also, how do you 'center' lines in a MsgBox?

Much thanks for any help!

I rather use my keyboard than my mouse. I know how to select a rectangular
area on a worksheet, using only the keyboard.

I often want to select several areas on a worksheet. Using the mouse, it's
simple: select an area by dragging, then hold control while selecting the
next area.

How do I do this *without* the mouse?

I am looking for help with page setup macro. My worksheet (excel 2003) contains lots of details and requires different page setup for printing e.g.
(a) Cells B3 to R3 downwards: Data details, which user may add more rows of records
(b) Cell S1-Z20: Data summary (formula) linked with (a)
(c) Cell AA1-AK15: Pivot Table linked with (b)

Therefore, I set buttons, linked with 3 macros to do page setup for the different ranges. However, I had some problems i.e.

1. For print area (a), how to define print area parameter in a macro to check the last row of data. i.e. check for last "blank cell" of column "C-E" (others columns contain formula before input the data)

2. In the future, if I or user insert new columns in area (a), is it possible to setup macro that have print area for (a) - (c) shifted without changing the parameter in the macro?


Hey Guys
I was looking about how to control IE with VBA.
And in all the previous posts it suggets you have to activate Microsoft Internet Control, in the references menu in vba.

Is it weird that i cant find it? i'm running Excel on vista?
Basically i'm trying to do what Leith Ross showed here.
I cant run the code cause it shows an error, and I bet its cause I haven't activated this reference. Is it renamed something else?


Hi guys,

The title of this thread does not probably reflect what I'm trying to do but it's hard to explain in a few words.
Basically, I have a model (probably one of the most complex I've ever built) with one spreadsheet "Parameters" where I enter the 5 different scenarios I would like to run and compare. On this same spreadsheet there is a cell that picks up which scenario as been selected on the user interface (from a simple cell with a drop down list and a listing Scenario 1, 2, 3, 4 and 5) and redisplays the selected scenario into a table that will be used for the final display on the user interface. For future reference, I'll call this cell "selector"

So, on the user interface, we can pick the scenario and see how it impacts on our situation.

Tricky part is here: there is another spreadsheet with a graph based on the selected scenario. At the moment, if I want to see what Scenario 1 looks like against Scenario 2, I have to go on the user interface and flick between the scenarios in the drop down list and then go back to the graph. I would like to be able to do this change from the graph spreadsheet so we can really quickly see the impact of the data on the plot.

I basically need a way to control the "selector" cell from the graph spreadsheet as well as the user interface. I know how to build financial models quite easily but I don't know anything about VBA. I've been thinking that if there was a way to do this, it would have to be under VBA unfortunately, and I have no idea where to start.

Your help would be greatly appreciated!


hey all

this is my first post and i will try make my post as clear as possible. for the record, i am a beginner at vba programming so this might come as a basic question to some and i actually don't have a code for this yet. so any tips how to solve this would be welcome. i can solve this by making some pivots but that makes the whole thing a bit heavy so i am seeking something more light. however, here is what i want to do with the following data:

date from account to account amount
01-01-11 dynamic id 1 dynamic id 3 1
01-01-11 dynamic id 2 dynamic id 1 1
01-01-11 dynamic id 3 dynamic id 4 1
01-01-11 dynamic id 4 dynamic id 1 1
01-01-11 dynamic id 1 dynamic id 3 1
01-01-11 dynamic id 2 dynamic id 1 1
01-01-11 dynamic id 3 dynamic id 4 1
01-01-11 dynamic id 4 dynamic id 1 1
01-01-11 dynamic id 1 dynamic id 2 1
01-01-11 dynamic id 2 dynamic id 4 1

i want to sort pr date and pr unique 'dynamic id #' and sum in that way that if the amount is 'from account' it is a negative amount and if the amount is 'to account' it is a positive amount. the output i want i something like this:

date account sum
01-01-11 dynamic id 1 -1
01-01-11 dynamic id 2 2
01-01-11 dynamic id 3 0
01-01-11 dynamic id 4 -1

any tips, tricks and help is welcome thanks in advance....

ps: sorry, but i cant align the columns of data neatly. hoe you can see where i am going at...

l would like to lock scroll area in sheet1 in range A1:H10 when sheet 1 i activate
the code is given under

    Worksheets(1).ScrollArea = "A1:F10" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

    Call LockArea 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

but problem is when file is close & again open this file scroll area is unlock so i first select sheet2 & then select sheet1 then scroll area is lock

i like when every time file is open the sheet 1 scroll area is lock whithout select sheet2


Does anyone know how to add a DriveListBox in VBA for Excel? Any help ASAP would be great!!

With VBA (in Excel) I want to read serveral text files. These files allways have 2 lines. I want to sort and write new files, where the first line of each text file is leading and must be on alphabeticaly order.

So I have:
testfile0001.txt -> line 1: "Thanks" and line 2 "100"
testfile0002.txt -> line 1: "Many" and line 2 "50"

And the result has to be:
testfile0001.txt -> line 1: "Many" and line 2 "50"
testfile0002.txt -> line 1: "Thanks" and line 2 "100"

to read the testfiles I have made this:

    Dim line1(100000) As String 
    Dim line2(100000) As String 
    Dim tmp As String 
    Dim i As Integer 
    Dim b As Integer 
    files = "C:test" 
    With Application.FileSearch 
        .LookIn = files 
        .SearchSubFolders = False 
        .FileName = "testfile" 
        .MatchAllWordForms = True 
        .FileType = msoFileTypeAllFiles 
        If .Execute() > 0 Then aantal = .FoundFiles.Count 
        i = 1 
        For b = 1 To aantal 
            Open .FoundFiles(b) For Input As #1 
            Line Input #1, tmp 
            Line Input #1, tmp2 
            line1(i) = tmp 
            line2(i) = tmp2 
            i = i + 1 
            Close #1 
        Next b 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This does read all the textfiles ok, but now I don't know how to sort them (line1) and to write them. (line1 and line2) Anybody can help me with this?

Hi i am new in VBA.Needs urgent help
I want to assign a macro to a button. but its says formula too complex .

I also do not know how to define the event in the code window.


I do not know how to attach procedures to menu items created by myself(VBA coding). I can manually attach it by editor but i find it can not be saved. I know I need write a VBA code to solve this problem. But I do not find good examples on the web.

Therefore, can some one give me some examples or information links?


Hi all,

i have some question to ask you all.
1. May i know how to create Addin (.xla) file at VBA ( Excel) ?
2. And are it have coding to create it ?
3. What is Addin file use for ? Coding different ?



No luck finding an answer? You could always try Google.