• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem while displaying Shape by macro, shape does not get populated [SOLVED]

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,


I am getting problem while displaying Shape.


I have following lines at the start of my code.


Sheets("BSE Index Watch").Select


On Error Resume Next


ActiveSheet.Shapes.Range(Array("MyShp1")).Select

ActiveSheet.Shapes.Range(Array("MyShp1")).Visible = True


On Error GoTo 0


I want to populate a shape when macro gets started.

The shape gets hide once the macro finishes.


When I run the macro in one go, pressing function key F5, the shape does not get populated.


But when I run the macro by doing F8, it is running fine.


When I run the macro by doing F8, the shape gets displayed at the start & disappears at the end.


But same does not happen with F5.


Can anyone help me with this.
 
Sachinbizboy


I suspect that this code will depend on the active sheet at the time the code is ran


Can you upload a copy of the file so we can review properly
 
Hi Sachin ,


Please note that the statement :

[pre]
Code: 
On Error Resume Next
is not a necessary statement , unless you really know what you are doing , since it allows the program to continue execution ignoring all errors.


Till you are fairly sure about everything that your program is doing , comment out this statement , and see where the program errors , and what error message is displayed.


The program itself is fairly simple :


You are selecting the worksheet tab labelled [b]BSE Index Watch


Can you explain what the following statement is supposed to do ?

ActiveSheet.Shapes.Range(Array("MyShp1")).Select
[/pre]
What does MyShp1 contain ? What does the Range(Array("MyShp1")) contain ?


Are the shapes present on the selected worksheet tab ?


The Shapes.Range[/b] construct returns a ShapeRange object ; this is normally used when you want to access several shapes at a time ; if you want to access just one shape , you can easily use the statement :


ActiveSheet.Shapes(1)


instead of


ActiveSheet.Shapes.Range(1)


Narayan
 
Good afternoon Narayan Sir, thanks a lot for the help.

Sorry for late reply, I will make the changes as per your advice.


Will share the resuls ASAP.


Have a nice day.
 
Hui Sir, thanks a lot for the help.


We are not allowed to download or upload anything from the office.


Sorry for late reply, have a nice day.
 
Sir now I have removed On Error Resume Next Statement.


I have defined the shape now, the shape does nothing, it is just used a pop - up to show the user that macro is running.


The shape is present in the BSE Index Watch sheet.


Sorry for not uploading sample workbook.

I am pasting the code I am using.


Still I am facing the same problem, I can see the shape while doing F8 but not able to see when run by doing F5.


Could you please help me in this.


Sub Macro2()

Sheets("BSE Index Watch").Select


Dim shp As Shape

Set shp = ActiveSheet.Shapes("MyShp1")

shp.Visible = True


Application.ScreenUpdating = False


On Error Resume Next

Sheets("Sheet4").Visible = True

On Error GoTo 0


Sheets("Sheet4").Select

Range("A1:n50").Clear

With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://www.bseindia.com/indices/IndexHighlight.aspx?expandable=1", _

Destination:=Range("$A$1"))

.Name = "IndexHighlight.aspx?expandable=1"

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.WebSelectionType = xlSpecifiedTables

.WebFormatting = xlWebFormattingNone

.WebTables = "3"

.WebPreFormattedTextToColumns = True

.WebConsecutiveDelimitersAsOne = True

.WebSingleBlockTextImport = False

.WebDisableDateRecognition = False

.WebDisableRedirections = False

.Refresh BackgroundQuery:=False

End With


Sheets("Sheet4").Range("A3:n38").Copy

Sheets("BSE Index Watch").Select

Range("A3").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False


Sheets("Sheet4").Visible = False


shp.Visible = False


Range("a1").Select

MsgBox "Data Refreshed !"

Application.ScreenUpdating = True

End Sub
 
Hi Sachin ,


You say your shape is present on the sheet BSE Index Watch , but immediately after selecting this sheet , and displaying the shape , you are selecting another sheet.


So , when you step through the code , everything is OK , but when you run the code , everything happens so fast that you never see the shape appear and disappear.


Narayan
 
Ok, I got my mistake.


Sir I will put a status bar rather than the shape.

I think I have that coding from this site itself.


Thanks a lot for the help & your valuable time.
 
You must log in or register to reply here.
Back
Top