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


Drawing A Line With VBA

How would i have VBA draw a line between a two ranges? say i have a named range of "start" and another name range "stop". i want to draw a red line between them. how can i do that with code. this is beggining my education for a much larger project I want to do later in the future.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Drawing A Line On A Userform
I know you can draw a line on a userform by providing the start and end co-ordinate of where you want it, but is it possible to have a UserForm appear on call, with the 1st co-ordinate already set (by the programmer) and the user clicks on some point of the UserForm to indicate where they want the end co-ordinate to be, and that a line would then be drawn to that point ?

View Replies!   View Related
VBA Drawing Objects
I want to use VBA to update a drawing object's color, such as a circle.

For example, pretend I am counting sheep and I need to know when I have reached the maximum count by the visual aide of a stoplight. The script in my head reads: If the max. count of the sheep is less than 5, then the stoplight is green. If the max. count of the sheep is equal to 5, then the stoplight is yellow. If the max. count of the sheep is greater than 5, then the stoplight is red.

Is there a way to do such a thing on excel and with VBA?

View Replies!   View Related
Code Too Long For One VBA Line
I have a large formula written in my VBA code (a sumproduct with numerous variables) which exceeds the line length in VBA and therefore a proportion of the code is shown on the next line.

However, the VBA treats this second line as an error (since it sees it as a new line of code which on its own doesnt work) What do I need to do so that it treats the two lines as a single line of code?

View Replies!   View Related
Read A Date Rather Than A Line VBA Macro
I have the following code which reads line 54 (row 54) in a spreadsheet and calculates the following using data in subsequent columns. Sometimes the data I need isn't always in row 54, I would rather have the macro read the data in Column A[AB] that is on line 54...sometimes on 53.

The date is Sep-09. How can I make it read this rather than AB54. I think I just need changes @ the top (ie Dim rng as Range, Set rng = [AB54]).

Sub Profile_info2()
Dim rng As Range
Set rng = [AB54]
rng.FormulaR1C1 = "=RC[-12]+R[-1]C[-12]+R[-2]C[-12]"
With rng.Offset(0, 2)
.FormulaR1C1 = "=((RC[-28]+R[-1]C[-28]+R[-2]C[-28])*4)/((RC[-25]+R[-1]C[-25]+R[-2]C[-25])/3)"
.NumberFormat = "0.0"
End With
With rng.Offset(0, 3)
.FormulaR1C1 = _
.Style = "Percent"
.NumberFormat = "0.0%"
End With
rng.Offset(0, 4).FormulaR1C1 = "=RC[-8]"
With rng.Offset(0, 5)
.FormulaR1C1 = "=(((RC[-17]-RC[-31])+(R[-1]C[-17]-R[-1]C[-31])+(R[-2]C[-17]-R[-2]C[-31]))*4)/((RC[-28]+R[-1]C[-28]+R[-2]C[-28])/3)"
.Style = "Percent"
End With
End Sub

View Replies!   View Related
Multiple Vba Commands On A Single Line
Is it possible to have multiple VBA commands on a single line with a separator ( ; or , ) of some kind?

I am trying to keep my code as short as possible (from a scrolling perspective) and would like to have something like the following:

Dim MyArray(1 to 20, 1 to 3)
MyArray(1,1) = "Sheet1" ; MyArray(1,2) = "D8" ; MyArray(1,3) = "D9"
MyArray(2,1) = "Sheet1" ; MyArray(2,2) = "C49" ; MyArray(2,3) = "w3"
MyArray(3,1) = "Sheet1" ; MyArray(3,2) = "A23" ; MyArray(3,3) = "AB12"
MyArray(4,1) = "Sheet1" ; MyArray(4,2) = "Z19" ; MyArray(4,3) = "W12"
MyArray(5,1) = "Sheet1" ; MyArray(5,2) = "Q32" ; MyArray(5,3) = "Q23"

View Replies!   View Related
Line Insert After A Change In Column H (VBA)
I currently have a macro that does several thing, i want to add another task.

I need a row inserted everytime there is a change in column H from "0" to "F" only. The infromation will always start in row 8 but the last row is not always the same, it will be different every time.

ABCDEFGHI112345678223456783#N/A#N/A#N/A#N/A#N/A#N/A#N/A45B=Bi-WeeklyID type 2ID Type 308 GAPamount due6HCCompanyS= Semi-MonthlyCO#NAMEFORMER (F) or Current (0)78546WalreBbr55a234amy01009524WalreSn32m56daniel05001041WalreS589653jode0100011945WalreBef343456caryF2001212WalreSg5365M5665mikeF40013Grand Total2,200.00

View Replies!   View Related
VBA To Pass Command Line Parameter
I found the following link that got me started [url], so now I have my code for reading/analyzing comand line parameters. Here is an example of the parm I'm passing in ....

View Replies!   View Related
Write A Line Into A HTML File With VBA
Say there is a HTML file at C:DataFile.html

I'd like to use Excel VBA to write this meta tag into that HTML file:

****** http-equiv="refresh" content="600">
That's to make it refresh every 600 seconds.

The most elegant way would be to locate the "head" tag and write it right after it, but the refreshing meta tag seems to work where ever it is, even at the very end of the file after the "/html" tag! So locating the "head" tag is not that important, if it's difficult.

So, how do I use VBA to write lines into a HTML (or any text) file?

View Replies!   View Related
Line Numbers In VBA Macro Code
If I number my lines of code and an error occurs, the use of Erl in the VBA will then return the exact VBA line number that the error occurred on. Is it possible to automatically capture or record that same effect when the macro process leaves its current sub and goes to another?

Reason: I'm trying to create a Call Stack that can be reported through an Error Handler that will include the exact location and process that the error occurred to better troubleshoot and understand where and why the error occurred.

The best I can come up with so far is manually putting in bookmarks along way so I know how far along the macro went before the error. From all of my searching I believe retrieving the Excel Call Stack is not possible and so one must be manually created.

Enclosed is an example of what I have so far. It goes through several macros and logs the Call Stack. Itís a work in progress so it is a little sloppy looking but it is functional. If a Sub finishes it is then taken out of the Call stack.

Several "BookMarks" are placed to give an idea of how far along the Macro has gone within that Sub. The Code for the Erl example is:

Sub SampleErrorWithLineNumbers()
Dim dblNum As Double
10 On Error Goto PROC_ERR

' Errors if table doesn't exist
20 Select Case Rnd()
Case Is < 0.2
30 dblNum = 5 / 0
40 Case Is < 0.4
50 dblNum = 5 / 0
60 Case Is < 0.6
70 dblNum = 5 / 0
80 Case Is < 0.8
90 dblNum = 5 / 0
100 Case Else
End Select
110 Exit Sub

120 MsgBox "Error Line: " & Erl & vbCrLf & vbCrLf & _
"Error: (" & Err.Number & ") " & Err.Description, vbCritical
End Sub

View Replies!   View Related
Vba Find Pattern Matching Whole Line
Using the Find Function in VBA with pattern matching, I want to find the End statements in my code. How do I stop it finding the End If statements, of which there are hundreds maybe thousands. I tried End[ ][ ][ ] and End[!I] etc but they exclude the End statements as well.

View Replies!   View Related
VBA - Filter On Next Line In Autofilter Dropdown List
if anyone knows some VBA code to filter on the next line down in an autofilter list.

My spreadsheet has a column with the names of people, which can appear multiple times in no particular order.

I have a macro that I run several times -- once for each individual in the column. The way that I currently do this is by manually using the autofilter on that column to select a person from the autofilter dropdown list. After I've manually filtered on a person, I click a button which runs the assigned macro. I then manually select the next person in the autofilter list, and click the macro button -- over and over until I've done this for everyone in the autofilter list.

I'd like to add some code at the end of my macro so that the macro ends by automatically filtering on the next person (line) in the autofilter list.

View Replies!   View Related
Vba To Switch Columns To Rows In A Line Chart
vba to switch columns to rows in a line chart
I have the following

View Replies!   View Related
VBA - Insert New Line At Top Of Predefined Table
I currently have a 200 row table spanning 10 columns. There is one numeric column and the rest are free text. The table charts the different contents on one of the shared drives here at work. The drive is frequently in use with things being added and removed from it.

I have an excel table already which can sort the contents in order of size (numeric row) or name of the folder (the first text column). I have two buttons at the top.
The first inserts a row, which I did by recording a macro and then copying the code into the button.

The second I would like to do the following: When a user selects a cell in a row, the button, then clicked, deletes the entire row. Not just the data in it, but excises the row itself and stitches the surrounding materials together. The code to delete a row I could obtain easily, but I can't work out to say "find the selected cell, and take out the row it belongs to".

View Replies!   View Related
Copy/Paste With Single Line Using VBA (2007)
I had the following code that worked great with Excel 2003, but I receive an error with Excel 2007.

Dim Master As Workbook
Set Master = Workbooks("Master.xls")

For Each wb In Workbooks
If wb.Name Like "S04*" Then _
If Not Range("A2") = Empty Then _
Range(Cells(Rows.Count, 1).End(xlUp), Range("A2")).EntireRow.Copy _
Master.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1)
Next wb

I receive error 1004 "application defined or object defined error" when it tries to paste to the "Master" sheet using the the above lines.

Is there something in the formatting that has changed with 2007? I'm guessing I have to slightly change the line in bold.

View Replies!   View Related
Place 1 Line Of VBA Code Into Mutiple Lines
I stumbled on this and wasn't sure why the code was placed on different lines and how the highlighted sections in red affect the basic round function.

ColorCompare = Round( _
(111111 + Blue + Red + Green) / (Green + LenPaint) _
, 0 _

View Replies!   View Related
Drawing Grid
I want to have 2 cells where I can enter 2 numbers (length & height) so that a grid can be plotted. For example, a big sheet of paper size is length 30" x height 8", if first number is 10" (length) is entered, it will plot 3 lines across 30" (cos 30" divided by 10" equals to 3) and if I enter 2" (height) on 8", it will then cut it into 4". Attached a file for better understanding.

View Replies!   View Related
Cant See Drawing Toolbar
I've recently got a new computer and this problem is driving me nuts.
When I click view!toolbars!drawing, nothing happens. No drawing toolbar appears at the bottom of the screen where it used to be. There's a small "drawing toolbar" icon on the main toolbar, but whether this is depressed or not makes no difference either.

I understand this may be changed in the 2007 version, but Iím using Excel 2000

View Replies!   View Related
Drawing Lines In A Macro
I want to draw a (time) line from the top left corner of a cell (say C3) to top left corner of a cell (say i10) in a Macro. Both start and end cells (column & rows) will vary depending on certain input values which I can convert into a cell reference. Line must overlap on both ends by two column widths.

View Replies!   View Related
Drawing Lines With Macro
I am having trouble writing a macro that will draw a line relative to what cell is currently selected. I can only get the macro to draw a line in one specific spot.

View Replies!   View Related
How Can I Tell Which DRAWING Textbox Is Active
I have a sheet with 30 textboxes made with the drawing toolbar. The user enters data, then clicks on a button which formats the page to adjust row size to match amount of info. I am having trouble finding the code to store the currently selected textbox in a variable so that I can place the focus back to the same textbox when the macro ends. Can anyone help me with the code to get the active textbox?

View Replies!   View Related
Hide Drawing Objects
I'm looking to write two small macros that will either hide or show all lines, freeform lines, and autoshapes on a page. In my extreme competence, I determined a for/next loop would be appropriate... but other than that, I can't figure out where to start.

View Replies!   View Related
VBA - Adding Border Line To Bottom Row Of Set Print Area
I am using the following macro to set print area which I found on the net. I am trying to modify it as I would also like to place a border line on the bottom row but cant find anything to assist me despite searching through several threads.

Dim myrange As String
With ActiveSheet.Range("A:A")
myrange = .Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Address
End With
ActiveSheet.PageSetup.PrintArea = "$K$1:" & myrange
End Sub

View Replies!   View Related
TextBox (Drawing) Text In Find
I have a macro that runs when a text box is clicked and searches in another excel sheet for a cell with a specified value. My problem is that I specify the string to search for in the code and I would like it to search for the string in the text box that gets clicked.

Here is what I have right now: ...

View Replies!   View Related
Prevent Some Drawing Objects Being Modified
How do i preventing drawing objects from being modified/ deleted/added on an excel worksheet? edit: but i want people to be able to interact with them i.e. people shud be able to enter text into textboxes.

View Replies!   View Related
Drawing Shapes Behind Plots On Graph
I'm currently drawing rectangle shapes with transparency on top of a graph to highlight certain depth ranges in vba. Rectangles span entire graph width. Is it at all possible to draw the shapes behind the series plots?

View Replies!   View Related
Change Drawing Object With Cell
I want to draw an object and then change the object shape by making subsequent changes in the cell.I don't know how to do the same.

View Replies!   View Related
Circling Text Within A Cell Without Drawing Tools
I can't find anywhere where there may be some VB code to allow text within a cell in Excel to be circled. I don't want drawing objects, because many times I need to modify 30-40 sheets at one time and Excel doesn't allow you to alter drawing objects when you have multiple sheets selected. Therefore, I just want to be able to apply a circle around some text in a cell the same way you would underline it, for example. I figured my only hope is VBScript?

View Replies!   View Related
Link Drawing Toolbar TextBox To Cell
Is there anyway to automatically update a value in a cell to a texbox?

In sheet1, there is a value of 2.3 in cell A1.
In sheet2, I have a textbox created from the Drawing Toolbox.
The content in the textbox would always be " Total (Sheet1.A1.value) Months"

View Replies!   View Related
Retrieve Drawing Numbers Associated With Part Number
I have a list of part numbers and a new drawing number and old drawing number. I'm am trying to put this list on the second page of a workbook and write a code on thie first page that allows a person to enter the part number and the old and new drawing numbers will be displayed.

View Replies!   View Related
Drawing A Blank On How To Sum Cells With Like Data
I have a worksheet with 27,000+ rows. Item numbers are listed in column J and quantities in column E. The rest of the data on the sheet is not needed. An item number may be listed multiple times. I'm trying to create a new sheet that lists each item number once with the sum of all the quantities associated with that number. The data is sorted so all "like" item numbers are listed in consecutive rows.

View Replies!   View Related
Cell Values In A TextBox From Drawing Toolbar
I know that I can enter =$A$1 in a text box. But is there any way to concatenate more data with it. I would like to hase something like.

="Bill " & $A$1 & " Gates"

but Excel will not allow.

View Replies!   View Related
Put All Values Of A Specifice Line In That Matrix In A Line
I'm working with matrix in vba and i'm trying to put all values of a specific line in that matrix in a line in excel. For example:

Matrix =
1 2
3 4

and i want Range("B1:B2") = 3 | 4 (the second line of that matrix).
Is there any way of doing this without perfoming a loop?
[something like Range("B1:B2").value = 'range'(matrix(0,0) , matrix(0,1)) ]

View Replies!   View Related
Convert Macro In 1 Line To Multiple Line Code
i hv following code

(i use generate macro)

my question is how to arrange the code from one line to multiple like :-

following code show in excel macro environment is one striaght line.

' Create new var on yr , and replace 2006 to CY06.

ActiveCell.FormulaR1C1 = _

View Replies!   View Related
Line Graph Not To Continue The Line If There Is Nothing In Row 10 Of The Table
With the attached spreadsheet i cannot workout how with the line graph not to continue the line if there is nothing in row 10 of the table.

for eaxmple if there is no value in cell M10, i would like the actual addtion line to stop at the last value on the graph that was in L10.

View Replies!   View Related
Report A List Line By Line Based On Name Criteria
Currently I am using Excel 2000. I did a search and couldn't seem to find the answer I was looking for. Hopefully someone can help. It would be greatly appreciated since I have been trying to figure out how to do this for days.

I am trying to create a worksheet that will pull info from a row on one sheet to another sheet based on the name; then continue to list the info on each line afterward until there is no more of the that particular criteria. See below (this is just an example to simplify the data but would need the same process):

This would be the data on sheet 1:

Name Bonus Commision

Tom $45 $50
Mary $25 $75
Mary $30 $80
Tom $60 $50
Tom $90 $25

What I would like to do is have 2 more sheets. One would be Tom's sheet and the other would be Mary's sheet and it would look like this:

Tom's Sheet:

Tom $45 $50
Tom $60 $50
Tom $90 $25

Mary's Sheet:

Mary $25 $75
Mary $30 $80

I am not completely familiar with all of the functions in Excel and compared to you guys I am a complete novice.

Is this possible to do in Excel without using a macro?

View Replies!   View Related
Table Of Contents - Drawing Info From Various Cells On Different Sheets
I have a "Generate Table of Contents" button that launches a macro. This macro builds the Table of Contents, creates (3) columns (Patient Name, Date of Pickup, and Frequency), finds all of the sheets in the work book (already renamed to a patient's name), sorts them alphabetically, and lists them under the Patient Name column.

This code works great - The next challenge:

On each sheet (which corresponds to a patient in the database), there is a cell that is filled in that states which day the patient picks up their medication. There is another cell that designates how often they come to pick it up.

I am at a loss as to how to direct the macro to: For each sheet, go to the specific cell, and then report it in the table of contents.

It somehow needs to bind the information together... meaning the patient name, date of pickup, and frequency must be displayed correctly each and every time on the same row.

View Replies!   View Related
Drawing Straight Lines In A Chart And Mark Intersections
Drawing Straight Lines In A Chart And Mark Intersections. I want to make a graphic like this in Excel.

View Replies!   View Related
Change Source File Of Linked Drawing Object
I've inserted a Solidworks drawing into a worksheet & wish to change the displayed image using VB. I suspect this could be done by changing it's source file, but can't get the code to work.

View Replies!   View Related
Delete A Line.or Rather A Line On A Line
I have a spreadsheet where there is a white line that is over a row line. No matter what I do I cannot seem to delete it.

I zoom in but I cannot select it.

I change the border of the cells and it will not disappear.

I am completely lost as to what else it could be, no doubt it is a simple answer that is staring me in the face. I inherited the spreadsheet so not sure what was done in the past.

View Replies!   View Related
Line Graph: (line Graph To Display An Amount Over Time)
I want to use a line graph to display an amount over time - that's the easy part. On the other hand, I would like to have to group the lines based on a value.

A short example:
Imagine you own 3 different stores and you're selling oranges. So your table looks like this:

Now I'd like to have one graph (3 different graphs won't work as the rows increase -I need to select the whole column as data source):

Date on the x axis,
Oranges sold on the y axis,
and one line per store (e.g. a green one for store A, a red one for B and a blue one of C, doesn't matter).

View Replies!   View Related
Add A Line
I have a very simple spreadsheet. Two columns: Date and Comment. I would like my employees (2) to go in and leave each other messages. I have setup conditional formatting so that when they enter their names the row will format with a different color for each person.

What I would like to do is:

That when they hit the enter key at the end of their comment that a new line is added above this line.

What I am trying to do is have their comments entered in ascending order so that the latest comment is always at the top. These two employees are completly excel illiterate and I really dont want to do the sort function.

They will be entering data on a daily basis.

I did do a search but couldnt find anything that helped me.

The rows start at A4 through B50.

View Replies!   View Related
Replicationg A Line
I have a list of names, and a list of number beside the names.

What I need to do is replicate the name in cell a1 by the number specified in b1 and then move on to the next row.

jim 3
alan 2
bob 5

what I need to end up with is



View Replies!   View Related
Getting The Line To Loop
How do I adjust this code to loop through 100 columns eliminating the need for the same line 100 times

Sub loopthroiugh()
Range("D4").End(xlDown).Offset(1, 0).Value = "Blah"
Range("E4").End(xlDown).Offset(1, 0).Value = "Blah"
Range("F4").End(xlDown).Offset(1, 0).Value = "Blah"
Range("G4").End(xlDown).Offset(1, 0).Value = "Blah"
Range("H4").End(xlDown).Offset(1, 0).Value = "Blah"
Range("I4").End(xlDown).Offset(1, 0).Value = "Blah"
Range("J4").End(xlDown).Offset(1, 0).Value = "Blah"
Range("K4").End(xlDown).Offset(1, 0).Value = "Blah"
Range("L4").End(xlDown).Offset(1, 0).Value = "Blah"
Range("M4").End(xlDown).Offset(1, 0).Value = "Blah"
End Sub

View Replies!   View Related
Certain Line Only Calculations
I am making a spreadsheet that tells us the following information about prints that we do:

View Replies!   View Related
Line Charts
Has anyone seen this problem before;

I used a "MID" formula to extract some data and for some reason the chart I'm trying to use won't recognize the data. Here is a copy of what the data looks like. Non of the vaules will show up on the chart.
DPJE2DD2CE2CJan1.2784.8984.8997.65Feb1.2697.6597.6591.79B10==MID(B$3,8,5)Mar1.3091.7991.79154.7Apr1.28154.7154.7147.6May1.29147.6147.687.78Jun1.4387.7887.78112.6Jul1.39112.6112.6115.2Aug1.42115.2115.293.6 Sep1.3393.6 93.6 138.9Oct1.28138.9138.9NovDec115.6YTD1.32115.6115.6

View Replies!   View Related
Line Code
I have the following line from my code of which I am having trouble with I get an error:

If Left(IRange, 1).Value "1" Then
I am trying to find the first string in cell range ...IRange ... I know the Left function = Left(string, length) but can I reference a Range or perhaps cell location within the string argument??? This can be done in excel formulas, but can it be done with VB code??

IRange = Cells (iRow,iCol) and value in cells are Long

My goal is to try and find out how I can use my excel formula:

Left(D3,Find("1",D3)) and impliment this to my above VB line

View Replies!   View Related
Cant Get The Boxes To Line Up
I have a form where there are a lot of check boxes and there is a print version button. It is so when you complete the form you click print preview and it removes all the unused lines and formats the form to be ready for print. Everything was working fine until I had to add another line of check boxes and now first row skips a cell after formatting.

View Replies!   View Related
Line Up Cells
I have a workbook with 2 groups of data on it side by side. Goal here is to have them line up. The problem is one side has more data then the other OR the smaller side may have data thats not in the larger side.


Accnt #, Name, address Acct#, Name, address

I would like:

1234 Mike Thomas 1234 Mike Thomas
1234 Jayne Thomas 1234 Jayne Thomas
1235 Chris Beaton
1236 Susan Hamel 1236 Susan Hamel

View Replies!   View Related
New Line In Msgbox
How can I create a return / new line in a MsgBox? I'd seen documentation to use the @ symbol to trigger a new line, but that doesn't seem to do anything.

View Replies!   View Related
Command Line
Is there any way to run a command line from excel?

E.g. i want excel to call this command:

ping and then return the result to a txt file?

View Replies!   View Related
Copyright © 2005-08, All rights reserved