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


VBA Data Label Format

I have a plot that shows the position of a drifting buoy in the ocean. I have created a second series that is just the most recent position of that buoy. Everyday, using VBA, I update the chart 2nd series to point to the location of the new lat and long. For this second series, which is only one point, I have data labels turned on. I replace the data label text with the date and time of that latest position. Here is my problem. I can not find a way to format the date. No matter what I try, it has the format mm/dd/yyyy hh:mm AM/PM. I really just want it to be a 24 hour clock (mm/dd/yy hhmm).

I have tried forcing the format of the variable date_of_latest_pos, I have tried changing the cell format that contains the actual date and time...

ActiveChart.SeriesCollection("Latest Position").ApplyDataLabels AutoText:=True, LegendKey:= False, ShowSeriesName:=True, ShowCategoryName:=False, ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False
ActiveChart.SeriesCollection("Latest Position").Points(1).DataLabel.Select
Selection.Characters.Text = date_of_latest_pos
Selection.AutoScaleFont = False
With Selection.Characters(Start:=1, Length:=23).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Label Making (take The Information And Incorporate It Into A Label Format)
I have a spreadsheet with a customers information and various parts we make for them. I need to be able to take this information and incorporate it into a label format. I need something quick and easy as there can be 150 parts per customer

Customer: ABCD
Customer PO: 12345

Part Number Quantity
AB 1
** 15
EF 22
GH 14

and I need it to output:

PO: 12345 QTY: 1

PO: 12345 QTY: 15

PO: 12345 QTY: 22

PO: 12345 QTY: 14

View Replies!   View Related
Userform Label Format
I am trying to format a label as a number with two decimals. I have searched this and other forums and see code such as:

Label18.Caption=Format(sheet1.Range("A!").Value, "0.00")

It always gives me an error: "Wrong number of arguements or invalid property assignment"

The word "Format" is highlighted.

I have tried putting the code in userform Initialize and also Activate. I am using Excel 2002.

View Replies!   View Related
Format Label Control
I would like to format a Label, such that it displays numbers with only 2 or 3 figures after the dot. Apparently it's not obvious for Labels.

View Replies!   View Related
Userform Activate Label In Correct Format...
Userform Activate Label in correct format. I have the following code in a userform activate

View Replies!   View Related
Convert / Transpose Label Format To Rows
I need a list with a row of column headers (optional but recommended) and then rows of data. An example of what I'm working with is as follows:


1524 29TH AVENUE

1851 10TH STREET
OAKLAND, CA 94606...........

View Replies!   View Related
Data Label Position
I am experiencing a 1004 run-time error on the following code....

View Replies!   View Related
Data Label Overlap
trying to update pie charts via vba. This is working fine, but I have a problem with the data labels. The number of categories can change each time the macro / data is run. This is as it will be for different customers that takle different products.

When the data labels are remapped, those with a small percentage end up placed untop of one another. I have already played with grouping these small percentage together into an 'Other' Category which works fine, but I know I will be asked to split this out.

Is it possible to add a condition that states 'DATA LABELS MUST NOT OVERLAP' - or is there some way of working around this to the same effect...

View Replies!   View Related
Look For Label And Data In Adjacent Row
I have a spreadsheet with rows of data. Presently, there are three pairs of columns. The first column in each pair has a label (E, F, R). The second column represents the number assigned to the label (e.g. 30, 40, 60, 70). The labels may appear in the first column of any of the three pairs. I would like to reorganize the data in three columns, which are headed, E, F, R, with the associated number underneath the heading. I have attached a sample spreadsheet showing the desired results.

View Replies!   View Related
Display Data Using Label On Userform
anyone know how to display data using label on userform? I tried using offset but it doesnt work. The data I want to display is in column G. How do i go about that?

View Replies!   View Related
Data Label To Move Ahead
move my data labels to next data point on my charts, for only those series which are already labelled.

View Replies!   View Related
Value Data Label Overlaps With Another Series
I have one series on my chart set as column type with data value labels. I have another series as line type to create a benchmark line across the chart. The chart is interactive where user can select 1 of 20 units.
On some of the units, the labels are overlapping the line type chart and causing a poor display

View Replies!   View Related
Charts: Display Last Data Label Value In Each Series
I would like to display the latest data label value for a data series So if I update my table for period 2 the chart should update to display only this value. Then period 3 and so on Workbook attached

View Replies!   View Related
Charts, Series Name, Legend Key, Data Label
I am applying for a job as an executive assistant and before the interview I have to complete a "test." I am having trouble with one direction. Here is the question:

"10.Apply series name and legend key in data labels to the Star data series. "

I have included what I think is the correct answer, but I am not sure. It is under the column chart sheet.

View Replies!   View Related
Conditionally Label Chart Data Series
I think that this is probably rather simple, but the answer is eluding me, I cannot find anything that helps online either. I have a set of data that looks similar to the sample below, however it is quite a bit longer. I would like the 'Effect' as the X axis, with the 'Score' as Y. This basically shows three columns, which is fine. However I would also like to use the 'ID' as the label against each plot point and I'd like each plot point to look the same.

The problem I am having is that to get the labels to show, I am having to add each series manually, which is rather time-consuming. Is there a correct (faster) way of doing this as I think I'm missing something obvious!


View Replies!   View Related
Label Scatterplot Chart: Plot Two Values Against Each Other Using A Scatterplot, And Label Each Service
I'm working for a local authority who have been given a mass of survey data. In this particular task, residents of each small district within our area have been asked their levels of satisfaction with a service, and how important they think that service is. I want to plot these two values against each other using a scatterplot, and label each service.

Excel does not automatically allow this so I used a very good sheet from the forums here: Attach labels with names to the points in a scatter plot. It's the top file, and works well. However, I can't seem to customise it for my own data.

Problems include:

- Excel often freezing when I try to run it
- Not all the data being picked up for the chart
- Incorrect labels being picked up..........

View Replies!   View Related
Excel Not Showing 0 Amounts As Data Label In Charts
I am building automated solutions where graphs source data is based on outcome of formulas. In case of line graphs I use #N/A as result if no data is available or formula results into an error - this way the data point and data label will not be shown in the graph.

However this does not work for bar graphs - with #N/A, #DIV/0, 0 or "" the bar itself is not shown but the data label is shown (as #N/A or 0). How can I set-up my formulas so that if result is 0 or formula is in error that the graph does not display the data label.

Attached excel file shows same data in 2 charts - 1 line chart (=OK) and 1 bar chart (=not ok). The data for chart is pulled from 2 other tabs (week&month) and merged into 1 data source for graphs.

View Replies!   View Related
Format Climatic Data From A Weather Station Into A Desired Format
I need to format some climatic data from a weather station into a desired format. There are a lot of cut&paste and transposing involved. I think it will be easy but tedious for anyone trained in VB to write a macro but unfortunately I am not. I've tried recording a macro but it turns out that it is not general enough to deal with all the spreadsheets that I've got.

I've attached a spreadsheet which shows the original format (in sheet 1) and the desired format (in sheet 2). To briefly describe, I only need the temperature data for 8am and 2pm, the rest of the information in sheet 1 is useless to me.

View Replies!   View Related
Date Format- Data Retrieved Via Import Data Tools
I have the following data retrieved via import data tools ...

View Replies!   View Related
Data Manipulation Reproduces The Data In A Format
I have a small table, 6 rows, 2 columns which looks something like:

Apples 3
Pears 1
Bananas 0
Cherries 2
Oranges 1
Grapes 1

The total sum of column B will always be 8.

What I want to produce is a range, say d1:d8 which reproduces the data in a format like:


i.e. responding to the number of items in the former table specified.

I'm sure it can be done with some long stringed If statements mixed with counts, but do any of you have any good ideas for formulas in d1:d8?

View Replies!   View Related
Label Animation
how to make the text in a Control Tool Box "Label". On Load, show text one letter at a Time?

View Replies!   View Related
Label Printing
I need to print quite a few mailing labels for Christmas cards from an Excel 2000 Worksheet. Could someone list the steps I must take to do this?

View Replies!   View Related
Add Text To Label
I have a textbox with a label & he will type in his name in the textbox & it appears on the label. Is there any way to put the word "Hi" on the label & then have his name appear after "Hi" on the label. I can do it using 2 labels side by side but I would like to have it all on 1 label

View Replies!   View Related
Find The Last Label
I am trying to create a button where it automatically copys and paste a label. I need to change the label name from the original to say "New". Since this button will be used constantly, how do I find the last Label copied, and change the caption on that lable? If you look at the code I have, you would see that it calls for a specific Label number to be changed (Label 826).

Sub CreateANewPN()
Dim LastRow As Long
Dim SecondLastRow As Long
LastRow = Range("I65536").End(xlUp).Row
SecondLastRow = LastRow - 1
Range("A" & SecondLastRow, "A" & LastRow).Select
Range("A" & SecondLastRow).Activate
Application.CutCopyMode = False
Range("A" & SecondLastRow).Select
Range("A" & SecondLastRow).Activate
Selection.Insert Shift:=xlDown
ActiveSheet.Shapes("Label 826").Select
Application.CutCopyMode = False
Selection.Characters.Text = "NewPN"
End Sub

View Replies!   View Related
Changing Value In A Label Just Like A Cell
I have in a cell something like: =B5. Once the value in B5 changes, the value in that cell changes as well. Is it possible to do the same using a label. In other word link a label to a specific cell and have it change along with the other cell.

View Replies!   View Related
Unhide Worksheet With Same Label Name
I have a workbook with quite a few worksheets, along with a summary page. The summary page has a list of names of all the worksheets in the workbook. I am still learning to use Macro. So far, I have created buttons to hide the individual worksheets (each component is on a worksheet by itself). But I can't figure out how to unhide a specific worksheet from a group of worksheets without creating just as many macros calling specific worksheet names. I don't really want to create 100s of little macros. I am sure there is a better way out there.

Here is a concept of the workbook:

On the summary page, the user supposed to click on a label ("T1000" for example), and this will unhide a worksheet with the same name (T1000). There are hundreds of these worksheets, how does it find

This is my idea of how the function supposed to work.

First determine the label text as a string.
Then locate the worksheet with the name equals to the string
then set visibility = true.

View Replies!   View Related
Creating A Quick Label
I am doing a "Lost and found" spreadsheet project for a hotel.

Each row contains the lost items with all the necessary info in separate cells (Room nr, found by, reference nr, client name, etc)

Every item is being bagged and then a label is put on it. ( has the same information but its just arranged prperly. (Like a label )

What I would like to do is to have a column in each row that has a "print" button. What would happen is that the macro would create a label in sheet2, print it on the default printer and then erase everything on sheet2. (I am kind of new to macros so I dont even know if I need to use the sheet 2 for it).

I have already done a kind of a template for the label and set it up on sheet2. I would probably manage to get the print function working with the online tutorials(if not i'll be back) but can't figure out how to do the first bit of it.

View Replies!   View Related
Convert A Date To A Label
I am trying to convert a date to a label. The following is what I am trying to do:

1/9/09 (formatted as date) converts to date value of 39822

How can I copy that cell & paste so that it will actually read 1/9/09 and not actually put the 39822 value in?

View Replies!   View Related
Label Visible By Value In TextBox
I have the following code written to check calculations. Now I would like to make one or another Label Visible depending on the value this code returns.

If TextBox57 = 0 then I want Label 7 to visible but if it doesn't I need Label 8 to be visible. I am starting both Labels hidden.

View Replies!   View Related
Textbox Output In A Label
I am having trouble linking a textbox so that it will out put in a label on a worksheet.

I have been given the code

Private Sub UserForm_Initialize()
TextBox1.Value = Worksheets("Sheet1").OLEObjects("Label1").Object.Caption
End Sub
But this returns the error

Run time error 1004

Unable to get OLE Objects property of the worksheet class

This is something that once perfect on one label and textbox I will then wish to make work on multiple labels.

View Replies!   View Related
Label Chart Elements
I've used Rob Bovey's Chart Labeller a lot recently, but this morning when trying to add some labels, I get the error message

"Unable to get the Name property of the Series class (bGetSelectionIndexes())"

Is there a limit to the number of series it will work with?

View Replies!   View Related
Timed Label Message
I have a userform with a label that displays a message using the 'Mouse Move' event. Is there any way to show the message for a specified period of time & then fade out?

View Replies!   View Related
Loop Through Label Controls
Label2, label4, label6, etc. how can I loop through these labels (I want to turn visible to false for a set of labels…)?

View Replies!   View Related
Label On Userform Not Updating
I have a userform with seven textboxes. I want to sum up the values in those textboxes and print the result in a label caption on the form. The label which displays the total needs to be updated every time anyone adjusts the textboxes. Because I need to have it update like this, I need to have a separate (short) procedure that I can call (when a textbox is adjusted) to sum up the textboxes once again, and place that sum in the label caption.

I have encountered a weird problem. When I update the label caption during the USERFORM ACTIVATE procedure, everything works fine and dandy, even though the summing is taking place in a separate procedure. But I cannot realistically run this entire activation procedure every time the user changes the number in a textbox. (Or, I should say, it just seems like sloppy programming to me.) I really need to update the label caption in a separate smaller procedure. However, when I change my code so that the label caption gets updated during the smaller procedure (which performs the summing operation), the caption remains blank.

View Replies!   View Related
Translucent UserForm Label
Is it possible to create a translucent background on a userform label?

View Replies!   View Related
Returning The Maximum Value And Corresponding Label
I have a table of data in cells B1:F5. I want to find the maximum value for each line of data from D:F and record the maximum value in cell H and then the heading (held in row 1) in cell I. Some one has kindly given me some code which has gotten me very close to this goal, but it doesn't quite work. I've been through the code line by line several times and I can't figure out where the problem is. I've included my code and the file.

Sub FindWinner()
Dim lngEntryRows As Long
Dim lngRow As Long
Dim rngEntries As Range
Dim dblMax As Integer
Dim intMaxCount As Integer
Application.ScreenUpdating = False
lngEntryRows = Range("C65536").End(xlUp).Row
lngRow = 2
Do Until lngRow = lngEntryRows
Set rngEntries = Range("D" & lngRow, "F" & lngRow)
dblMax = WorksheetFunction.Max(rngEntries)
Range("H" & lngRow).Value = dblMax
intMaxCount = WorksheetFunction. CountIf(rngEntries, dblMax)
If intMaxCount = 1 Then
Range("I" & lngRow).Value = Cells(1, WorksheetFunction.Match(dblMax, rngEntries)).Value
Range("I" & lngRow).Value = "Draw"
End If
lngRow = lngRow + 1
Application.ScreenUpdating = True
End Sub

View Replies!   View Related
A Macro To Format Data
I have been trying to produce a macro that will format data of varying lengths. Basically, I pull data from a database and export it into excel. I have a macro that will format the first few lines fine. Unfortunately, there are a few lines of info at the end of each report that need to be deleted. I cannot figure out how to make excel reach the end of my data a delete these two lines. If I build the macro on one report that has 100 lines and delete line 99 and 100, and then attempt to run the macro on a report that has 150 lines - lines 99 and 100 are still deleted.

Is there a way to delete the last couple of lines of data of a spreadsheet?

View Replies!   View Related
Format Data Into Columns
with "Selecting equally spaced cells and putting the info into columns...". I work for a company that needs to get the info from this site here:


I'm trying to get it all organized into one nice and neat excel spreadsheet. I researched things myself and I found that you can just insert the source code into excel and excel will automatically put the tables into cells. My only problem is that, if you noticed, on the site each and every person has their own "First Name", or "Last name", "Email", or whatever. I just want one "First Name" in the column A, the "Last name" part in column B, and so on and so forth. Is this possible to do in an easy method because I'm seriously not going to copy and paste the info myself, all 36,000+ times.

View Replies!   View Related
Rearrange Data Into A Certain Format
I am seeking the expertise of you knowledge folks to advise me whether it is possible to create a "script" in Excel to jumble up certain letters in an entry?

Let me explain. What I have are 4 separate columns with data in them. I need to somehow rearrange them into a certain format, like an encryption I suppose.

Here's an example of some data I have :


What I need to do is jumble those up so they appear in the following format:


Is it possible to do this in Excel? You're probably wondering why I don't do it manually, but there are over 20,000 records that need jumbling up and obviously that would take a very long time.

View Replies!   View Related
Format The Downloaded Data
I often download data from various web sites - this is data that is displayed on a web page in tabular format, and often consists of hundreds of lines. I typcally Select All, then Copy, then go to a blank spreadsheet and Paste. After a bit of reformatting, I'm usually in good shape. The problem is, that sometimes the contents of a cell might have been "5-2" (without the quotes), and it comes through on Excel as "May 2". Without proofing each cell, I can't just make the assumption that the original was "5-2" - it might have been "5-2-10" (but not a date).

The normal Paste Special options are not available when I do this, so I can't paste them in as values. I have tried formatting the worksheet at text prior to doing the copy but that does not work.

View Replies!   View Related
Data Moves To The Right In CSV Format
i have csv data which has splits cells due to commas between numbers, and then moves data further to the right, is there a way to fix this, so for example this is what some cells look like

31874.90 JPMORGAN

but need to be like this

31 874.90 JPMORGAN

View Replies!   View Related
Format Data From Web So It's Usable
I think I have a similar problem as this thread The answer is probably somewhere in the forum, but I looked around and I can't figure it out on my own.

Basically, I copied some data off a website and now I am trying to organize it so it is usable. Here is my example. I would like to put the data below into usable columns. The only data I need is the hospital name (column A), location (column B), Births (column C), and Beds (column D). This is how the data currently appears in excel:..............

View Replies!   View Related
Changing Data From One Format Into Another
I have a data set in excel which is the following format which I would like to convert into a text file with a different format. The excel sheet format is as follows:

V C 1 2 3
1 23 0.06 0.04 0.03
1 12 0.09 0.06 0.03
2 5 0.11 0.07 0.05
2 11 0.12 0.08 0.04

The text file should have the above data in the following format with brackets and comma:

For example:

View Replies!   View Related
Convert Data To Different Format
See the attached file. In the RawData tab, I have the Type, The Date, and total for that date and type. Using a macro, i would like to redesign the output(onto another sheet) so it can be updated to a database table(i already have the code to export to database). The output should look like what is in the "OutputExample" tab. Figures in the OutputExample tab are only for the first three dates. So currently, I have the output as:

View Replies!   View Related
Re-Format Data Layout
I have a stacking program but it does not keep the first column associated with the data that is stacked as well as the first row. This is how the database looks now:

CREATE TABLES LIKE BELOW?July '07August '07September '07
HR #55551341,929
HR #57217311,653
HR #102322,8981,988
HR #98221,5571,097

this is what I need:

HR #5721 July '07
HR #10232 July '07
HR #9822 July '07
HR #55 34 August '07
HR #57 731 August '07
HR #102 2,898 August '07
HR #98 1,557 August '07
HR #55 1,929 September '07
HR #57 1,653 September '07
HR #102 1,988 September '07
HR #98 1,097 September '07

The current VBA prgram just stacks the columns in one column going from right to left.

View Replies!   View Related
Format Data In Macro
I have cells which contain dates in the typical Excel format. IE: Jan 3rd, 2000 in unformatted form shows up as 36528 in Excel in Cell A2. What I would like to do, in VB, is take the contents of that cell A2 (36528), and turn it into a date in the form of:

(For instance, Jan 5th 2001 would be
And then assign it to a variable

View Replies!   View Related
Worksheet Label Font Size
whats the correct syntax for Font Size as a Label property? I am attempting to change the font size of all Labels on a particular sheet via vb

View Replies!   View Related
Label Caption To Equal Cell Value
I have 3 labels in my userform that I want to display the text in AN50,AN52 and AN54 in all the time. How can I write this code?

View Replies!   View Related
Insert Multiple Rows And Label Accordingly
in inserting 8 rows as per the attached spreadsheet highlighted in orange, and labelling them, obviously, the year after the one above it.

View Replies!   View Related
Function To Return A Column Label
Is ther a function that will return the column letter rather thatn the column number?

View Replies!   View Related
Label Values Not Updating, But Chart Is
I have a number of charts (mostly pie) I don't have a legend, but I do have "Show label and percent" selected.

When I add in new data the actual chart updates to show the right sized pie slices... but the values on the labels don't update as well. I can't figure out how to make that happen?

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