I have a basic data table with KPI labels down one side and dates across the top. Using this data table as the series source, I create a number of charts below it with serialised names eg Chart0, Chart1, Chart2, etc. I am attempting to enable the KPI labels so that when a user clicks it, the spreadsheet will navigate to the appropriate chart. I place the name value for the chart object in the cell immediataly to the left of each KPI label (and hide it). This way I know which chart object below is related to which KPI label above. That part works.
I have a Worksheet_SelectionChange() event procedure attached to the charting worksheet. When a user clicks into one of the KPI labels, the event procedure fires and the appropriate chart is activated. The code is:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B5:B19")) Is Nothing Then
On Error Resume Next
chtObjName = Range(Target.Address).Offset(0, -1).Range("A1").Value
If Left(chtObjName, 5) = "Chart" Then
ActiveSheet.ChartObjects(chtObjName).Select
If Err.Number <> 0 Then
MsgBox "No such chart exists.", vbCritical, _
"Chart Not Found"
End If
End If
On Error Goto 0
End If
End Sub
Every time I copy and paste a small object "jpg" or "png" into a cell it defauts in the cell to the left and top edge s of the cell. I would like the object to be centered in the cell. I can drag the object to "Close to Center", but I have green and red go/no go dots in 120 cell going downward I would would really like to see them aligned.
I have been trying to create a macro in excel to chart a selection of data and to output the chart on the active sheet where the data was taken (as opposed to a named sheet). So basically, I have about 300 worksheets with data, and I would like to have a button on each page that automatically charts that data when clicked, and outputs the chart to the page where the macro was clicked. However, I have not been able to figure out a relative reference that will allow me to make the LocationasObject reference simply the ActiveSheet as opposed to a specifically named sheet. See my code below, which references an output to a worksheet called "Charts". Right now, all of my charts are outputting to the sheet called "Charts", as opposed to the active sheet.
Sub ConsDiscChart() ActiveCell.Offset(29, 11).Range("A1").Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Range("A1:B1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Offset(0, -1).Range("A1:C24").Select Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts" With ActiveChart .HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With End Sub
I have searched high and low and cannot seem to find an answer. I have inserted an ActiveX button in a cell to run 3 macros when pressed. I cannot get the 4-letter name on the button to appear centered on the button, unless I make the button larger than the cell, which I do not want to do.
adjust the following code so that my images are just a bit lower than "top aligned"? The code centers my images perfectly within the cells, but when you top align an image, it's just a hair too high and border formatting does not come out.
I have a very strange problem in DEBUG mode, because i get this error "Application-defined or object-defined error" when referring to a cell and assigning it a value so it goes to my error handler and i have a Resume Next there. It continues to go through the code whilst continuing to go to the error handler but when i step out of the function it restarts again from the beginning on the called function and then on the second run of my code it seems to WORK!?! So i'm thinking what the hell is going on, it falls over and fails the first time round and works the second time round? In free-run mode from excel i just get a #VALUE!
I have a range of amounts in Sheet 1 from F7:Q13 and im using the find method to search for the active and non active values in the cell. Which means that if there's a value in the cell it will transfer the value in Sheet 2, if nothing is found in the cell the cells in Sheet 2 will return as nothing or null.
I think the problem lies on the FindWhat variable. Im getting a compiled error which im not sure what is it.
I've attached the spreadsheet so you get a better idea of the problem that i encountered.
When I copy the tab and change some of the data within the cells, I want the macro refer to the chart on the current tab and the values in the current tab - as currently it refers to only "Chart 2" and the values in the tab 'Figure 2 - WE OPH'.
I've tried changing the sheet name to ActiveSheet.name but that doesn't seem to work.
1) i have office 2003 on a laptop. within powerpoint, i can create a 'microsoft excel chart 11' object. to create a link to the excel data source, do i have to go through the odbc sql setup? it works, but i don't want my powerpoint to be dependent on some excel file somewhere. what are the other options to insert/make a functional pivot chart in powerpoint with the data also within powerpoint? the data as sheet option does not result in the chart being a pivot, it's just a plain chart. it has to be a proper object, not an image paste or a chart that updates links with the excel file open.
2) i have office 2007 on my other laptop. i can not find any suitable object to choose from to make a pivot chart in powerpoint. what's the best way to go about in 2007 version?
3) am i going about this the wrong way with the objects? should i be after vba code?
I have got stuck on one piece of my code and having trouble fixing... Overall I am trying to find variable station name in cell L2 of Sheet 2 in Sheet 1 and then select and copy the data from the data in "cell L2 of Sheet 2" to the last entry of that row. I have attached an example test spreadsheet of the data and a macro is within Sheet 1 called test1. Please note that cell L2 in Sheet 2 will always be different station name and the station list in Sheet 1 will change with differing station name.
The code I am using is:
[Code] .....
The code that is not working and bringing up an error is:
whenever the active cell is within a given range, highlight the cell on the same row in column S (by changing its interior colour). This should occur each time the active cell is changed, whether by cursor keys or mouse. The effect would be similar to the row and column highlights at left and top of the worksheet.
This action should be restricted to one sheet in the workbook.
I am having difficulty getting a form to work the way that I would like it to work. I have a form that is used to display questions that my students will be answering. The form also is used to put the answers into a worksheet. I have 2 sheets. Sheet2 has the questions, student answers, and correct answers. Sheet1 is used to indicate correct answers and to keep track of percentage correct. I am fairly new to VB. I have 2 pieces of code that I am going to post. The first one works and the second one doesn't.
This is in the "This Workbook" section and it works. Option Explicit Public intNoQ As Integer Public strNoQ As String Public NumberofQuestions As Integer Dim StudentName As String Dim InputBoxAnswer As String Public Sub Workbook_Open() Application.Visible = False 'Hide Excel Load Questions Load NumberCorrect NumberofQuestions = Worksheets("Sheet1").Range("K3").Value For intNoQ = 1 To NumberofQuestions strNoQ = VBA.CStr(intNoQ) If intNoQ = 1 Then Questions.Controls("QuestionNumberBox").Value = "Question#" & strNoQ Questions.Controls("QuestionBox").Value = Worksheets("Sheet2").Range("B1").Value End If Questions.Controls("CorrectBox" & strNoQ).Visible = True Questions.Controls("CorrectLabel" & strNoQ).Visible = True Next intNoQ...............
This code is part of a routine that copies and pastes from CASH RECEIPTS sheet to YEARLY TOTALS sheet in the same workbook. rngCash is assigned as a Range variable.
In the various fixes I’ve tried, the code worked to varying degrees.
Code: Dim Recurring_Total as Range With .Range("A" & Rows.Count).End(xlUp).Offset(,2) Set Recurring_Total = .Range("A" & Rows.Count).End(xlUp).Offset(,2) .Font.Bold = True End With
with the line "Sheets("Hidden Data").cells(rngfound.row, 9) = sheets("data").range("l18")" the error messages states Object Required, but i am not sure what this is trying to say.
any idea's or thoughts would be greatly appreciated.
at the top of the coding i have listed "Public rngFound"
I have inserted a workwook in another workbook that the user will have to edit and save changes from time to. I have the excel file inserted as an object. when I double click it the file opens and I can save changes made.
I'm trying to create a macro that can open the object from other tabs. i used the macro recorder and got this
Range("I21:O25").Select Sheets("Formulas").Select ActiveSheet.Shapes("Object 3").Select Windows("Worksheet in SBATrendReport2010").Visible = True Selection.Verb Verb:=xlPrimary ActiveWindow.WindowState = xlMaximized End Sub
The problem I'm having is I get a error message when I run the macro "subscript out of range.
I have a sub which loads a bitmap image onto a sheet, lets say to sheet1 ,cell B2, the reference sheet1!b2 is held on sheet2 in column A.
how to add a hyperlink to the image so that when it is clicked it looks at looks at the value on sheet2 and creates hyperlink to that cell, effectively selecting the cell underneath the image. I've tried this with text - using the following in a cell:
=HYPERLINK("[LoCQ34.xlsm]indirect(Sheet2!A2",Sheet2!B2) - this works for a text link, using the add hyperlink from the right click menu but doesnt do what I want with a bitmap, and I'm not sure how I'd do this in VB.
So, i would like to know it can it be applied to an image, and how can it be done in VB as the value in Sheet2 changes?
How important is it? I've read some books that recommend it and others that don't even mention it. I have a procedure that uses a lot range, worksheet and workbook variables. Should take the time to set each one to "Nothing"?
I'm writing a little program for a co-worker of mine to make their job a little easiar. Here is the code that I have written so far. The first 2 parts of it work perfectly fine, but if it gets to the 3rd part I get an Object Variable or With Not Set. I understand this is horribly written code as I am kind of a beginner and just do things by trial and error.
VB: With Sheets("Graph").ChartObjects("Chart 3") .Activate .Chart.SeriesCollection(1).XValues = Sheets("Data").Range("D2:D" & Sheets("Data").Cells(Rows.Count, "D").End(xlUp).Row) .Chart.SeriesCollection(1).Values = Sheets("Data").Range("E2:E" & Sheets("Data").Cells(Rows.Count, "E").End(xlUp).Row) .Chart.SeriesCollection(1).(What Do I put here To Set the color of the line) End With
When calling the code below, oTbl seems to retain the data from the previous call to the function. During debug, the Nothing statment clears oTbl and I can see that it is empty. However once Set oTbl = oFunc.Tables("ENTRIES") is called, oTbl contains all the data from the previous call again, i.e. even before the oFunc.Call statement that actually connects and retrieves the data from the remote system.
VB: ' oRS is a global Connection Object variable to a remote system Public Function fncTest() Dim oFunc As Object
I am trying to make this code work, I am getting the subject error:
HTML Code: Private Sub CommandButton1_Click()Dim cols As VariantDim NextRow As ObjectDim Myrow As Object Sheets("Sheet1").SelectWith Sheet1 Myrow = Range("A" & Rows.Count).End(xlUp).Row + 1
