Graphics with VBA

lafrenie

Board Regular
Joined
May 30, 2007
Messages
60
I have a database with loads of data. Dates YY.MM in column A and data in other columns. I want a pop up window that will allow me to choose from the begining and end date of the data that will be used for the graph. Can you anyone help ?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
What do you mean?

Do you just want The VBA code to open an InPutBox set to except a Range selected with the Mouse or KeyBoard and you will finish the code needed to ues that range in a chart?

Like:

Sub getRange()
'Standard module code, like: Module1.
Dim objMyRng As Object

On Error GoTo mySkip

Set objMyRng = Application.InputBox(prompt:="Select the cells to work with:", _
Title:="Get the Range to Work with!", _
Default:="", _
Type:=8)

'objMyRng.Value = "Test!"
mySkip:
End Sub


If you know the starting cell and just need code to automatically find the last data cell, then you do not need an InPutBox it can be done with "End(xlUp)" used on the range object.

If you will need to select portions of a range then the InPutBox is the best bet.
 
Upvote 0
VBA Graphics

I have the below code. What I want is to select the data for the graph from the rows "startdate" to "enddate" of specific columns. Lets say date in colum a and data from colum c and F.



Sub Total2()
Dim vdt As Variant
Dim strName_file As String
Dim strName_onglet As String
strName_file = "Database" '& myDate
strName_onglet = "mn"
Application.ScreenUpdating = False
Workbooks.Open (ch_file + strName_file)
startdate = InputBox("Pls enter start date of graphic " & "dd.mm")
Enddate = InputBox("Pls enter start date of graphic " & "dd.mm")
Workbooks.Add
Charts.Add
ActiveChart.ChartType = xlLineMarkers
[/list]
 
Upvote 0
If you're using an input box, set the type to 8 and let the user select the actual cells. So much easier than typing (and mistyping) a date.
 
Upvote 0
Graphics VBA

Thks John

I kind of have to use dates for this, any idea how I can do it ?

Just in case, how to you code the box type to 8 ?

Rgds

Eric
 
Upvote 0
Graphics VBA

John,

I have weekly data going back to 1904. Therefore I need to be able to decide the date range for the data in the various columns. I was hoping to be able to do this with the inpiut box.

Any other idea ?

Thks
 
Upvote 0
This code will add a Data Validation In Cell Drop Down List of Dates to a Cell.

It requires that you pick a Sheet and Column for the list's Table of Dates [Which the code automatically builds for you!], to be used as the source for the DropDown dates. It also requires that you pick the Sheet and the Cell that gets the DropDown List in. The date to Start the list with and the number of dates to list in the DropDown are also set in the code. The code re-sets itself if you re-run it or change any of the basic date information. By changing the Lists Name you can use this code to maintain as many Date Drop Down Lists in a Workbook as you want!

Read the notes in the code to make these changes!
The code must be run from a Standard code module, like: Module1, do not use a Sheet module!

This is a working sample, to test it insert a copy of this code into Module1 of a Blank Workbook with at least Sheets1 & Sheets2, then run the code:


Sub myCalDates()
'Standard module code, like: Module1.
Dim datMyStartDate As Date, datMyNewDate As Date
Dim lngNextRow&, lngIntervalPeriod&, lngNumOfDatesToList&, lngColNum&
Dim strInCellDropDownSheetName$, strTableOfDatesDataSheetName$
Dim strDtIntervalType$, strCellToGetTheDropDownList$, strTableOfDatesColumn$
Dim strDateTableColumn$, strNameOfThisDateList$, strListLocation$

'Note: You must change the information below!
'Adjust the StartDate and the location of the DatesList data location,
'then adjust the In-Cell-Data-Validation-Drop-Down-List location below!
'*************** Default Date Information! **************************************

datMyStartDate = #1/1/2007# 'The Date to start the Date List With!

strDtIntervalType = "d" 'd=Day,m=Month,y=Year, the DropDown Date Intrerval!
lngIntervalPeriod = 1 'How many of the Interval Type to space dates by!

lngNumOfDatesToList = 366 'How many dates to list!

strTableOfDatesDataSheetName = "Sheet2" 'The Sheet Name that gets Date Data!
strTableOfDatesColumn = "A" 'The Column to use for this Date Data!

strInCellDropDownSheetName = "Sheet1" 'The Sheet Name of where the DropDown is!
strCellToGetTheDropDownList = "C6" 'The actual Cell that gets the DropDown List!
strNameOfThisDateList = "DateList" 'The name of this Date List!
'*************** Default Date Information! **************************************


On Error GoTo myNoNamedRng
ActiveWorkbook.Names("DateList").Delete

myNoNamedRng:

With Sheets(strTableOfDatesDataSheetName)
.Select
strDateTableColumn = strTableOfDatesColumn & ":" & strTableOfDatesColumn
.Columns(strDateTableColumn).ClearContents
.Columns(strDateTableColumn).NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
.Range(strTableOfDatesColumn & 1).Select

.Range(strTableOfDatesColumn & 1).Value = datMyStartDate

For lngNextRow = 2 To lngNumOfDatesToList
datMyNewDate = DateAdd(strDtIntervalType, lngIntervalPeriod, datMyStartDate)
.Range(strTableOfDatesColumn & lngNextRow).Value = datMyNewDate
datMyStartDate = datMyNewDate
Next lngNextRow

.Columns(strDateTableColumn).Columns.AutoFit

lngColNum = Range(strTableOfDatesColumn & ":" & strTableOfDatesColumn).Column

strMyListLocation = "=" & strTableOfDatesDataSheetName & _
"!R1C" & lngColNum & ":R" & lngNumOfDatesToList & "C" & lngColNum

ActiveWorkbook.Names.Add Name:=strNameOfThisDateList, _
RefersToR1C1:=strMyListLocation
.Range(strTableOfDatesColumn & 1).Select
End With

Sheets(strInCellDropDownSheetName).Select
Range(strCellToGetTheDropDownList).Select
With Selection.Validation
.Delete

strListLocation = "=" & strNameOfThisDateList

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=strListLocation
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Select Date!"
.InputMessage = "Pick the DATE you want from the list here!"
.ShowInput = True
.ShowError = False
End With

With Sheets(strInCellDropDownSheetName).Range(strCellToGetTheDropDownList)
.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
.ColumnWidth = 28
.ClearContents
End With
End Sub
 
Upvote 0
Re: Graphics VBA

I have weekly data going back to 1904. Therefore I need to be able to decide the date range for the data in the various columns. I was hoping to be able to do this with the inpiut box.
Did you read the article? The use of dynamic names and scrollbars to indicate the first and last dates make it easy for the user and eliminate the need for VBA.
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top