2 Replies - 3296 Views - Last Post: 14 July 2008 - 08:07 PM Rate Topic: -----

#1 bellaelysium  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 36
  • Joined: 08-June 08

[VB2005]adding scrollbar on top of chart in excel

Post icon  Posted 13 July 2008 - 06:48 PM

I have successfully add in scrollbar manually and it was able to scroll the chart in new chart sheet and I recorded the VBA code and now wish to convert to VB.Net for my VB2005 application

However, there's these few lines which I have problems converting:

ActiveSheet.ScrollBars.Add(1428928.5, 224788.5, 3806190, 223698).Select
	Selection.ShapeRange.IncrementLeft -0.02
	Selection.ShapeRange.IncrementTop 5.31
	Selection.ShapeRange.ScaleWidth 1.27, msoFalse, msoScaleFromBottomRight
	Selection.ShapeRange.ScaleWidth 1.03, msoFalse, msoScaleFromTopLeft
	With Selection
		.Value = 0
		.Min = 0
		.Max = 23
		.SmallChange = 1
		.LargeChange = 10
		.LinkedCell = "AnimalRackTrial!$K$31"
		.Display3DShading = True
	End With 



Whats the code for form scrollbar?
ActiveSheet.ScrollBars.Add(1428928.5, 224788.5, 3806190, 223698).Select



and I got problem converting this line:
Selection.ShapeRange.IncrementLeft -0.02
	Selection.ShapeRange.IncrementTop 5.31
	Selection.ShapeRange.ScaleWidth 1.27, msoFalse, msoScaleFromBottomRight
	Selection.ShapeRange.ScaleWidth 1.03, msoFalse, msoScaleFromTopLeft

I have to make sure that the scrollbar always stay on top of the chart and the same width as the graph.


And for these:
 With Selection
		.Value = 0
		.Min = 0
		.Max = 23
		.SmallChange = 1
		.LargeChange = 10
		.LinkedCell = "AnimalRackTrial!$K$31"
		.Display3DShading = True
	End With 


I tried :

objExcelC.ScrollBars.Add(1428928.5, 224788.5, 3806190, 223698)

With objExcelC.ScrollBars.Add(1428928.5, 224788.5, 3806190, 223698)
			.Value = 0
			.Min = 0
			.Max = 23
			.SmallChange = 1
			.LargeChange = 10
			.LinkedCell = "AnimalRackTrial!$K$31"
			.Display3DShading = True
		End With


but there's no scrollbar added in, even if there's no error.

I also tried:
Dim MyObject As OLEObject
		MyObject = objExcelC.OLEObjects.Add(ClassType:="Forms.ScrollBar.1")

		With MyObject 
			.Value = 0
			.Min = 0
			.Max = 23
			.SmallChange = 1
			.LargeChange = 10
			.LinkedCell = "AnimalRackTrial!$K$31"
			.Display3DShading = True
		End With


But there's some error.

Can anyone help?? :crazy:

Millions thanks! :)

Help appreciated:)

Is This A Good Question/Topic? 0
  • +

Replies To: [VB2005]adding scrollbar on top of chart in excel

#2 tody4me  Icon User is offline

  • Banned
  • member icon

Reputation: 13
  • View blog
  • Posts: 1,398
  • Joined: 12-April 06

Re: [VB2005]adding scrollbar on top of chart in excel

Posted 14 July 2008 - 07:11 AM

The issue that you're facing, from what I can tell, is that you are trying to set the properties to the application and not to the worksheet. You have to open an object instance to the application, then the current workbook, then the current sheet (or the sheet that you want to perform these tasks on). Once you have an object to the active sheet / chart sheet, you can set the properties of that sheet accordingly and it'll take.
'Declarations for the application handling
Dim xlApp as Excel._Application
Dim xlWB as Excel._Workbook
Dim xlWS as Excel._Worksheet

'Grab the Active Sheet and Set the workbook and worksheet values
xlWB = xlApp.ActiveWorkbook
xlWS = xlWB.ActiveSheet

'Change this line
With xlWS.ScrollBars.Add(1428928.5, 224788.5, 3806190, 223698)
            .Value = 0
            .Min = 0
            .Max = 23
            .SmallChange = 1
            .LargeChange = 10
            .LinkedCell = "AnimalRackTrial!$K$31"
            .Display3DShading = True
        End With


This post has been edited by tody4me: 14 July 2008 - 07:12 AM

Was This Post Helpful? 0
  • +
  • -

#3 bellaelysium  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 36
  • Joined: 08-June 08

Re: [VB2005]adding scrollbar on top of chart in excel

Posted 14 July 2008 - 08:07 PM

View Posttody4me, on 14 Jul, 2008 - 07:11 AM, said:

The issue that you're facing, from what I can tell, is that you are trying to set the properties to the application and not to the worksheet. You have to open an object instance to the application, then the current workbook, then the current sheet (or the sheet that you want to perform these tasks on). Once you have an object to the active sheet / chart sheet, you can set the properties of that sheet accordingly and it'll take.
'Declarations for the application handling
Dim xlApp as Excel._Application
Dim xlWB as Excel._Workbook
Dim xlWS as Excel._Worksheet

'Grab the Active Sheet and Set the workbook and worksheet values
xlWB = xlApp.ActiveWorkbook
xlWS = xlWB.ActiveSheet

'Change this line
With xlWS.ScrollBars.Add(1428928.5, 224788.5, 3806190, 223698)
            .Value = 0
            .Min = 0
            .Max = 23
            .SmallChange = 1
            .LargeChange = 10
            .LinkedCell = "AnimalRackTrial!$K$31"
            .Display3DShading = True
        End With



Yes I did :)

I have already done code on creating workbook and worksheet, do up the chart programatically .

Now I am left with the scrollbar to scroll the chart. ~! :crazy:

I did tried your method before, but the scrollbar did not show up on top of my chart though the linked cell has the value '0' .

PS: My chart is in new chart sheet.


Hope you can help :)

This post has been edited by bellaelysium: 14 July 2008 - 08:35 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1