Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Horizontal Bars Scroll Bars!


I am trying to derive the EMI for a Loan Amount and also chart a PIE graph for the same..

Instead of Manually feeding the values such as :

Variable 1 : Loan Amount : Min : Rs 50,000/- Max :Rs 5000000/-
Each Spin (1 Unit) -Rs 25000/-

Variable 2 : Interest Rate : Min : 5% Max : 30%
Each Spin (1 Unit) -0.25%

Variable 3 : Tenure/Duration : Min : 6 months Max : 30 years
Each Spin (1 Unit) - 6 months

I want to have a Scroll-Bar where the values can change Automatically but am not aware of using the same...

Can someone please help me with a small attachment with the above variables attached to it..The values drawn should be displayed as well above the Scroll-Bar and maybe can make the Min and Max more dynamic by storing it somewhere in the Sheet..

I am looking for Non-VBA solution to do the same..


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Control Scroll Bars: Remove The Scroll Bars From Just One Worksheet But When Unticked
In the menu via TOOLS-->OPTIONS-->VIEW I am trying to remove the scroll bars from just one worksheet but when unticked, this option affects all of the worksheets in my workbook. Is there a way to just nail this selection down to one sheet via properties?

View Replies!   View Related
Docking Command Bars? (tool Bars)
In the code below using msoBarTop places the menubar at the top but it seems to just place it in a new row at the right of all menus.

How or can I dock the menubar next to the last menubar?

View Replies!   View Related
Scroll Bars In A Form
What I need to do is add a scroll bar to my text box in my form. I used the properties of the text box to add a scroll bar but when you preview it you can't see it. When you click somewhere inside the form it appears and is workable but not when it hasn't been clicked on. How do I make the scroll bar appear from the start. this is a vertical one ad is on a text box within the form

View Replies!   View Related
Force Display Of Vertical Scroll Bars
I have created a form that has two textboxes on. It is used to edit existing data that is selected by a combobox on the same form.

Is there a way to force the textboxes to show scroll bars if the text exceeds the textbox height or just have them always shown?

It is difficult to distinguish on some of the data whether the data goes beyond what is visible. Without actually clicking into that textbox the scroll bars will not show.

Is this possible?

View Replies!   View Related
Hide & Show Scroll Bars For Frame Control
I have a form that is dynamically created at runtime. I have assigned the controls to a frame in VBA and also attached a horizontal scrollbar manually to that frame.

How can I control the scrollbar in VBA to appear, when columns of controls added to the frame exceeds 10. If not I want the scrollbar to be invisible?

Private Sub UserForm_Initialize()
Const cTextBoxHeight As Long = 16
Const cTextBoxWidth As Long = 40
Const cGap As Long = 10
Dim W As Integer
Dim a As Integer

View Replies!   View Related
Multiple Menu Bars
I have some code that creates multiple new menu bars for a user which each have there own controls. I need to know which control is selected by the user for onaction. The example below is where there are up to 20 new menu bars with names Tract 1, Tract 2, etc. For each Tract, there is a controlbutton titled Absoprtion and I need to know which "Absoprtion" button was selected to run the "ViewAbsorption". I need to use th For next function because the number of menu bars could vary.

Dim objMenu As Object
Dim objViewMenu As Object

For y = x To 39
Name = "Tract" & y - 19
With Application.CommandBars("Worksheet Menu Bar").Controls
Set objMenu = .Add(Type:=msoControlPopup, temporary:=True)
objMenu.Caption = "&" & Name
End With

With Application.CommandBars("Worksheet Menu Bar").Controls(Name).Controls
Set objMenu = .Add(Type:=msoControlButton, temporary:=True)
objMenu.Caption = "&Absorption Schedule"
objMenu.OnAction = "ViewAbsorption"
Next Y

View Replies!   View Related
Remove Title Bars
code to remove the titlebar of a userform in Excel/VBA.

Remove meaning the program user must not beable 2 see the titlebars..

View Replies!   View Related
Hide Command Bars
I have opened an Excel file (map1.xls) and changed some little things in that file.
after that I open a new Excel file with some VBA code in it.

In ThisWorkbook I have this ...

View Replies!   View Related
Command Bars - How To Test If Exist?
Occasionally, I lose my "Custom Menu". Usually while trying out different things with macros.

View Replies!   View Related
Bargraph With Grouped And Stacked Bars?
This is the data:

jan09 feb09 mar09 .....
Division 1
Division 2
Division 3
Division 4
Division 5

jan08 feb08 mar08 .....
Division 1
Division 2
Division 3
Division 4
Division 5


The graph would be a bar graph with 2 bars next to each other per month. each bar would be stacked by division. The goal is to compare for example sales in 08 with sales in 09 showing 2 bars next to each other split by division.

View Replies!   View Related
Hide Menu Bars In Only 1 Workbook
I have a workbook that I created as an interface for my workplace. I disabled the File, Menu, Edit, etc...menu bars through .Control("x").Visible = False and .Enable = False as well

Now when I open a new workbook all the menus are there still which is good but when I open a previously saved workbook all the menus are missing. Is there a way where I can have ONLY my workbook have the menus gone and for them to appear in other workbooks that were saved?

View Replies!   View Related
Hide Standard Menu Bars
This is the code I use to temporarily remove menu barsfor a spreadsheet that is managed by user forms. I have code at the close of the program to replace the menu bars. The code work well although and it places a list of enabled bars in the range C1:C50 on the sheet "Bars". The range C1:C50 is not locked before the sub routine 'Hide Menus' runs. However after the sub routine 'Hide Menus' runs, the range C1:C50 is always locked and I don't understand why this is so, I would prefer it not to be.

Sub HideMenus()
Application. ScreenUpdating = False
i = 0
Worksheets("bars").Range("c1:c50").Clear
On Error Resume Next
For Each Allbars In Application. CommandBars
If Allbars.Visible = True Then
i = i + 1
With Worksheets("bars")
.Cells(i, 3) = Allbars.Name
If Allbars.Name = "Worksheet Menu Bar" Then
Allbars.Enabled = False
Else
Allbars.Visible = False
End If
End With
End If
Next
Application.DisplayFormulaBar = False
On Error Goto 0
Application.ScreenUpdating = True
Opening.Show
End Sub

View Replies!   View Related
Display Floating Bars On Same Line
Is it possible to display all of the bars from a floating bar chart on the same line. The effect would be a single horizontal bar that turns "On" and "Off" from left to right. I'm not sure of the application, but it might represent when a room is occupied over time. I think one can create this effect using Andy Pope's "Column Chart with Varying Width Bars" charting technique (provided on his web site http://www.andypope.info/charts.htm ), but is there a more direct approach?

View Replies!   View Related
Create Three Stack Bars Chart With Different Data
Here is my scenario, I have the following data:

Data Series 1

a=3
b=3
c=4
Tot 10

Data Series 2

d=2
e=2
f=2
g=1
h=1
i=1
j=1
Total 10


Data series 3
k=4 (d+e)
l=3 (f+g)
m=2 (h+i)
n=1 (j)

I want the three stack bars together, for the series 2 & 3 I want them without any gap so I can see how data series 3 is created.

I can't find a way to create a three stack bars chart that don't have the same number of data series, I don't want percentages.

View Replies!   View Related
Deleting BuiltIn Command Bars VBA
I have the code below. But When I run it, the line marked * throws up an error.
I do have custom built toolbars in my spreadsheet. It should delete them all.
I am using Excel 2003.

Sub DelCommandBarsCustom()

'Del all command bars of specific criteria

For Each objCommandBar In Application.CommandBars
If objCommandBar.BuiltIn Then
* objCommandBar.Delete
End If
Next objCommandBar


End Sub

View Replies!   View Related
Custom Error Bars On Bar Charts
I created a bar chart with horizontal (category) labels: winter, spring, summer, fall. Y-axis label is percent. For each category there is data for blk rats, Norway rats, Mice. For each species and category the standard error is different because it is based on several years of data for a given season.

When I try to display the std. error (using custom, fixed value) it treats each species as a series and only lets me choose one value. in other words the std. error would be the same for blk rats in winter, spring, etc. How can I use the individual values?

View Replies!   View Related
Bar Charts - Change Bars To The Category Name
I have a list of salesmen and their performance to date (on a percentage basis) which is sorted lowest percentage down to highest. What I would like to do is to graph this as a bar graph (no problem there) but for the bars to be replaced by the names themselves! So, I have data like this:........

View Replies!   View Related
Dynamic Ranges For Error Bars In Charts
I am using defined names to build a chart. This is working fine, but I also would like to use a defined name to control the +/- fields for the error bars. The defined names I have set up are correct, and I am able to enter them into the sorce data form but Excel does not display them.

View Replies!   View Related
Format Chart Bars Based On Cell Reference
I'm trying to get the bars of a chart to change colour depending on the selection from a drop down in a cell. The drop down is validated to be one of four options which changes the figures displayed in the graph using index matching.I would really like the colour to change to represent what the option being selected is.

I used some VBA code before for changing the cell colour depending on the selection made to get over the 3 condition limit. Could this be easily edited to change a bar graph colour? All of the references I can see on line refer to changing the colours of bars based on their value, I don't want to do this, more an independant cell value!

My previously used code is below:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iColor As Integer

If Not Intersect(Target, Range("d9:d129")) Is Nothing Then
Select Case Target.Cells(1)

Case "Commercial Final State"
iColor = 49
Case "Commercial Interim"
iColor = 31

View Replies!   View Related
Apply Conditional Formatting To Bars In Bar Charts
I have created an excel file that contains a lot of worksheets which contain a lot of raw data and charts. There are 5 different tabs for 5 different companies. Each tab currently contains only 1 year of raw data for that company. Then I have another excel worksheet that has nothing but charts on it. Each chart is a bar chart that shows each of the 5 companies. It also shows the current rate for the month for each company and the 12 month average for each company. On the chart there are also 3 lines. There is a standard line that we try to meet each month. There is also a line for the upper control limit and a line for the lower control limit. Here is my problem:

The 2 bar charts (current rate for the month & the 12 month average) are created and updated by the raw data in each of the 5 data sheets.

Based upon the values in each of the cells that create and update the bars on the charts themselves:
I want the bars to be RED if the values are below the lower control limit.

And I want the bars to be GOLD if the values are above the upper control limit.

And I want the bars to be green if the values are in between the upper and lower control limits.

View Replies!   View Related
Scrol Bars To Only Move The Form To The Edge Of The Sheet Instead Of Ten Or More Cell Beyond
I have forms and want the scrol bars to only move the form to the edge of the sheet instead of ten or mor cell beyond.

View Replies!   View Related
Encourage Enabling Of Macros: Disable Macros When Opening Then The Worksheet Menu Bar And Other Command Bars Are Still Available
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.

View Replies!   View Related
Restricted Scroll Areas Won't Scroll
I am using the toolbox/ properties method to restrict the scroll area in the sheets of my workbook. However, for some sheets when i keep the right hand arrow pressed, the screen will not scroll to the last column. (i.e. the screen display will not move with the selected cell box)

View Replies!   View Related
Vlookup Horizontal Return
I am simply trying to do a standard Vlookup but since there are multiple returns for any given match the best results for me are to return them horizontally on the same line in seperate columns.

My original formula holds all the same information, nothing is different except where I want the values to be returned. Here is the original...


=VLOOKUP(K2,'Open PO'!$A$1:$D$13000,3,0)
and...


=VLOOKUP(K2,'Open PO'!$A$1:$D$13000,4,0)
I have been putting formula 1 (Single Return) into Column X but need the multiple returns to return to X, Y, Z, and AA

I have been putting formula 2 (Single Return) into Column AB but need the multiple returns to return to AB, AC, AD, and AE

There should not be more than 4 returns per match so if anyone could help me with a formula to return each of these formulas horizontally like this.

View Replies!   View Related
How Do I Paste In A Horizontal Manner
Assuming i copied a list of cells that are vertical [column format], how do i paste it such that it will become horizontal [horizontal format]

View Replies!   View Related
Search Horizontal And Vertical
What kind of formula do I need that looks for a date and find all the names that have the same character on that particular day

In the example below cell R4 kooks for date R3 in range A1:L15 and find all cells that have a "G" and displays all the corresponding names from C1:L1

I prefer non array-formulas

View Replies!   View Related
Finding Most Frequent Value (horizontal)
I have some data that looks like this:

Columns c through H

#N/A030016030037030037030037030016030037

I want to find the most frequently occuring value. These are not always numbers, so I can't use mode. Also, some of the columns may contain errors.

My normal formula for extracting the most frequent value would be:

=INDEX(C2:H2,MATCH(MAX(COUNTIF(C2:H2,C2:H2)),COUNTIF(C2:H2,C2:H2),0))

But this doesn't work for a horizontal list because match looks for the rownum.

I've tried using offset to get it to work, but either that doesn't work, or I don't know how to do it properly.

View Replies!   View Related
Charts - Horizontal Axis
I recently started keeping a new chart to track several different stocking measures. After running the data this morning, I noticed my horizontal axis had blank position holders for data over the weekend. How would I set the chart so that if there is no data in my data range, it doesn't put a place holder in the axis? Sample attached.

View Replies!   View Related
Adjust Horizontal Pagebreaks
attached a sample, with the page settings, print area set, row height, etc... all set to my needs. I would like vba to adjust the horizontal pagebreaks so that a "box" is never split into two parts. in attached sample the pagebreaks should therefore go below row30, row54, row124 and row128. Setting pagebreaks is easy, but still want VBA to do it for me.

View Replies!   View Related
Convert Data Horizontal To Vertical
i have a data in columns and would like to convert into rows

e.g.
11 A B C D E F G H
22 1 2 3 4 5 6 7 8
33 2 3 4 5 6 7 8 9
44 3 4 5 6 7 8 9 0

and result should be
A 22 1
A 33 2
A 44 3
B 22 2
B 33 3
B 44 4
C 22 3
C 33 4
C 44 5
and so on ..

View Replies!   View Related
Make Horizontal Data Go Vertical
a_____b____ c_____ d_____ e______ f______ g_____ h_____ i
1 - 12/1--| 385 -| 15000 -| --etc--|| --etc--|| --etc--|| --etc--|| --etc--|
2 - 12/5--| 256- | 20000 -|
3 - 12/6--| 356- | 11000 -|
4 - 12/5--| -89--| -1000 --|
5 - 12/9--| 500- | 9000 ---|

Where d1:d5 e1:e5 f1:f5 and g1:g5 h1:h5 i1:i5 all have similar data.

Is there a way to have the data from def and ghi fall in line underneath abc with the condition being that there has to be something in the cell?


View Replies!   View Related
Display Data In Horizontal Format
I need to show data across the page, currently the data is arranged in column's, I need it displayed in row's.
Each material has a number of components, it is the components which I want to show across the page, with the different headings.

I have attached the current format and the required format.

Note the spreadsheet I will be working on has thousands of materials and hundreds of components.task.xls

View Replies!   View Related
Transpose Names From Horizontal To Vetical
XL 2003

Am I tired so don't see solution?

Transpose names without blanks:

Book1.xls

View Replies!   View Related
Running Count In Horizontal Row?
Is there any way to create a running count in one horizontal thread? I can create a running count in vertical columns,but not horizontal threads?

I have a spreadsheet that I want to keep track of how many rounds mulitple weapons have fired. I have a current rounds cell, a last fired cell and total rounds cell. The problem is that when I enter the number of rounds shot in the "last fired cell", the sum of the total rounds cell goes up (via the sum function) however, is there a way to keep that number there, even though I will replace the last fired cell with a new number the next time I fire the weapon?

View Replies!   View Related
Vertical To Horizontal Data Extrapolation
How am I able to extrapolate data from vertical to columns?

I.e.

From:

ColumnA ColumnB

Field 1 Result 1
Field 2 Result 2


To:

Column A Column B Column C Column D
Row 1: Field 1 Result 1 Field 2 Result 2

This is different than Transpose.

View Replies!   View Related
Changing Entries From Horizontal To Vertical
I have a list with entries in one column like this:

sourceText
empty cell
targettext

sourceText2
category2
targettext 2

and I want to change it like this:

sourceTextempty cell target text
sourceText2category2target text 2

Attached sample data. Sheet one as it is. Sheet 2 as I want them to be (I don't need any of the formatting in Sheet 2).

View Replies!   View Related
SUMIF With Both Vertical And Horizontal Data
I need a solution for the equivalent of a SUMIF combining both vertical and horizontal data. The vertical cells align to the horizontal ones, but they're in a different table.

My attempted formula is: =SUMIF($H$22:$H$30,"TRUE",D7:L7)
*note that this is just an example set of data...my real data set is much larger (both rows and columns)

I need to be able to do this without transposing any of my data.

Things I've tried:
- Another option I tried was making D7:L7 a named range and using the transpose function (as an array) within the SUMIF formula above. I received an error.
- I tried using a bunch of IF statements added together (i.e. =IF(H22=TRUE,D7,0)+(H23=TRUE,E7,0)...); this actually works properly, but I get the "formula too long for cell" error when I put them all in (too many characters)

I'm using excel 2003 and windows XP professional.

View Replies!   View Related
Lookup For Vertical And Horizontal Corresponding Values.
I have a problem that lookup vertical and horizontal corresponding values when there was duplicate values as it's only returning the first value found. What I want was to lookup the vertical and horizontal corresponding values on the left most & top most column based on the largest values column and also to return the duplicate values under the vertical and horizontal value column in ascending order if it's a duplicate values.

View Replies!   View Related
Transpose Horizontal Rows To Vertical Columns
Acct #11019145036954564872011019125101521103192103103904205851103192415102


The file is broken down by revenue codes on the first row of the account and then units on the second row. (450= Revenue Code and 1=Units associated with that revenue code) Then a new account number starts and it is revenue codes on the first first row and units on the second row.

I want the file to look like this:

Acct #Rev CodesUnits1101914501110191390311019168021101917203110191110411031939431103193921511031954510110319648

View Replies!   View Related
Chart Which Has Horizontal Gradients In Increments Of 500
I have a chart which has horizontal gradients in increments of 500. For example, $500, $1000, $1500, etc. Is it possible to make one of the horizontal gradient lines which run across the chart, darker than the other horizontal gradient lines? For example, if a "goal" is to reach $2000, can I make that horizontal line in the chart darker than the lines for $500, $1000, $1500, etc.? I am not talking about the bars that run vertically for each data, but the lines that run horizontally across the chart.

I dont know if this can even be done in a chart or not.

View Replies!   View Related
Change Vertical Rolling Hierarchy To Horizontal
The accounts listed on this report change on a monthly basis. I am attempting to create a macro or write a formula that can but used each month to fill in this list automatically. On Sheet 1 is a short example of what the report looks like when exported into excel. The numbers on the left are the numerical hierarchy. The long set of numbers are the account numbers and the others are the categories that each are located under.

Sheet 2 is what I need the end result to look like. One of the problems I am running into is how to handle the fact that the hierarchy re-uses numbers. The hierarchy always runs vertical, therefore "5" always falls under the "4" that is directly above but when there is another "4" it should be ignored and continue on up the chart until it finds the first "3" and place that in the cell to the right then find the first "2" and so on.

View Replies!   View Related
ComboBox RowSource From Horizontal Row Range
Normally the combo box will reading the data range from top to bottom. Is there any way to allow the combo box to reading the data from left to right? when i cover, it only reading the first cell of data range.

View Replies!   View Related
Call Horizontal Data Into A Vertical Layout With Additions
I am trying to reference data that is currently horizonal in excel and would like to reference that data to get output horizontally with some additions. I have pasted a screenshot of my test spreadsheet. how to do it step by step as I am completely a newbie in excel. As displayed below my data is in Cells A1 thru E3. I want to get output in Column H for this data but with text additions. For eg: in A1 I have Rachel 1/1 however in H1 that should become "My name is Rachel 1/1"....

View Replies!   View Related
Checking If Inside Vertical/horizontal Boarders Exist
Part of my code formats a selected range, including boarders. I needed the vertical and horizontal boarders to be set to 'xlThin', but some times the range that is selected may only be one row high or one column wide, so this is throwing up and error. I am currently using the code below to do this.

View Replies!   View Related
Find Data Based On Horizontal And Vertical Criteria
I have a spreadsheet that I am trying to create a formula for that will bring back the data found when you compare an X and Y axis. A sample is attached as the data is huge and I figured what ever you all created I could modify.

I need it to bring back the data found when I run my finger down the column till I hit the appropriate row.

View Replies!   View Related
Add Horizontal Line To Chart Without Creating New Series
I have a line Chart and want to create a static horizontal/indicator line.

I cannot create a new series to plot this horizontal line as I'm using an external data source (OLAP cube) as source data for the chart.

View Replies!   View Related
Custom Toolbar Orientation. Horizontal & Vertical
How do you control the orientation of a floating toolbar? The code below puts the elements horizontally, while I'd like to make the toobar have a vertical format (one macro per line). I'd also be interested if there's a way to do sub-menus to organize the toolbar buttons better. how to revise the code below, and/or a pointer to where I can read about this. I tried searching google and this forum to no avail.

Option Explicit
Public Const ToolBarName As String = "My Toolbar"
Sub Auto_Open()
Call CreateMenubar
End Sub
Sub Auto_Close()
Call RemoveMenubar
End Sub
Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(ToolBarName).Delete
On Error Goto 0
End Sub
Sub CreateMenubar()......................

View Replies!   View Related
Convert Vertical & Horizontal Data To 2 Lists
I was wondering is there a way to convert the format of one excel spreadsheet to a different format in another. I have attached a spreadsheet which illustrates what I'm trying to convert from and to. Perhaps VB Script? I have no idea how this could be resolved.

View Replies!   View Related
How To Create Macro To Move Multiple Horizontal Data To Vertical
I need to create a macro to move variable multiple horizontal data to vertical format with certain infomation on horizontal will be duplicated following that variables. It's looks like below where you can see variables data in column F, G, H and I are moved vertically and at the same time column A, B, C, D and E will be duplicated following the variables allocation. I've tried to use transpose but it too manual and now looking suitable macro to help on this function

Original DataAccountDim 3Dim 4AmountCurrencyV20228V20242V20211V202044006003300BXXX 9.4USD0.591.923.343.554006003400BXXX 88.17USD5.5118.0331.3233.314006003500BXXX 7.27USD0.451.492.582.75Process to automateAccountDim 2Dim 3Dim 4AmountCurrency400600V202283300BXXX 0.59USD400600V202283300BXXX 1.92USD400600V202283300BXXX 3.34USD400600V202423300BXXX 3.55USD400600V202423400BXXX 5.51USD400600V202423400BXXX 18.03USD400600V202113400BXXX 31.32USD400600V202113400BXXX 33.31USD400600V202113500BXXX 0.45USD400600V202043500BXXX 1.49USD400600V202043500BXXX 2.58USD400600V202043500BXXX 2.75USD

View Replies!   View Related
Add In Another Option To Select Whether To Print The Page As Horizontal Or Landscape
I'm using the select sheets to print code from: http://j- walk.com/ss/excel/tips/tip48.htm. I need to add in another option to select whether to print the page as horizontal or landscape. I've been able to add the checkbox, but am having a hard time with the loop to process correctly. I also have the landscape changing code as well. Here is my
Option Explicit
Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim TopPos1 As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox
Application.ScreenUpdating = False
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If
Set CurrentSheet = ActiveSheet............

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved