• 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.

I want macro to put symbol in Table

Hi Abhijeet ,

I have no idea , except that in your system , the added pictures are being considered earlier than the button ; let me rewrite the code a little ; I'll check for "Button 1" , which is the name of the button ; all shapes other than this will be deleted when the code is run.

Narayan
 
In this file Button is not disable but symbol is not working fine when change the date then previous number symbol not remove from Table
 
Hi Abhijeet!

Here is another approach..

I have created duplicate image of all the required image named them in a particular way ... and just set them visible if any number found ..

Code: 
Sub ShowMe()
For j = 1 To 9
    Sheets("Sheet1").Shapes("GrpImg" & j).Visible = False
Next j
    For i = 2 To Range("E" & Rows.Count).End(xlUp).Row
    If Trim(Range("E" & i)) <> "" And Val(Trim(Range("E" & i))) > 0 And Val(Trim(Range("E" & i))) < 9 Then _
        Sheets("Sheet1").Shapes("GrpImg" & Trim(Range("E" & i))).Visible = True
    Next i
End Sub

let us know.. if you are facing problem in adapting the same in your actual file..
 

Attachments

  • test pyara.xlsm
    28.7 KB · Views: 3
@NARAYANK991
In your uploaded attachment.. Some how you missed to change 1 to i

@Abhijeet
If you dont have any plan to change the picture within this year.. and all images are fixed.. then you can follow my procedure.. but if you are planning to change them .. frequently.. please follow Narayan's file with lil bit modification

Code: 
  For i = 1 To number_of_shapes
  If Shapes(i).Name <> FIXED_SHAPE_NAME Then
  Shapes(i).Delete
  End If
  Next
 
Hi Abhijeet ,

I am sorry , but I cannot contribute further ; on my system ( Excel 2007 ) everything works correctly.

I hope someone else will step forward to help.

Deb : The 1 is correct , and intentional. When I use i instead of 1 ( as I did first ) , it does not work.

Narayan
 
@NARAYANK991

You have already completed the project.. Just (may be) you have missed to change to change in your uploaded file. (I think)
In you uploaded file.. (post # 28) in For loop..

'-----
For i = 1 To number_of_shapes
If Shapes(1).Name <> FIXED_SHAPE_NAME Then
Shapes(1).Delete
End If
Next

'-----

I think it should be Shapes(i)
 
Hi Deb ,

I don't like repeating myself , but I'll do it for you !

On my system ( Excel 2007 ) , it works correctly with 1 ; when I use i , it does not.

The attached file has several tabs , each with a different date ; I entered each date in the main tab labelled Sheet1 , and clicked on the button to generate the table with its symbols. Then I made a copy of the sheet and renamed it. Please note that this takes care of the following :

1. I clicked the button 4 times , to generate the 4 sheets , and the button did not get disabled.

2. The dates are all different , and so are the numbers in column X ; each time I clicked the button , the earlier symbol pictures were deleted , and symbols were inserted ; you can verify that the symbols and the numbers in column X match ; you can also verify that the earlier symbols are not present.

3. You can also verify that the code has the following statement :

Shapes(1)

and not :

Shapes(i)

Narayan
 

Attachments

  • Pyra calculator Final.xlsm
    79.7 KB · Views: 2
Debraj said:
@NARAYANK991
In your uploaded attachment.. Some how you missed to change 1 to i

@Abhijeet
If you dont have any plan to change the picture within this year.. and all images are fixed.. then you can follow my procedure.. but if you are planning to change them .. frequently.. please follow Narayan's file with lil bit modification

Code: 
  For i = 1 To number_of_shapes
  If Shapes(i).Name <> FIXED_SHAPE_NAME Then
  Shapes(i).Delete
  End If
  Next
Click to expand...
Hi Debraj This file I change in code but small problem 2nd time I change the date then last time symbol not change
when 2 time run the macro then that symbol change
 

Attachments

  • NPyra calculator New.xlsm
    41.9 KB · Views: 0
Debraj said:
Hi Abhijeet!

Here is another approach..

I have created duplicate image of all the required image named them in a particular way ... and just set them visible if any number found ..

Code: 
Sub ShowMe()
For j = 1 To 9
    Sheets("Sheet1").Shapes("GrpImg" & j).Visible = False
Next j
    For i = 2 To Range("E" & Rows.Count).End(xlUp).Row
    If Trim(Range("E" & i)) <> "" And Val(Trim(Range("E" & i))) > 0 And Val(Trim(Range("E" & i))) < 9 Then _
        Sheets("Sheet1").Shapes("GrpImg" & Trim(Range("E" & i))).Visible = True
    Next i
End Sub

let us know.. if you are facing problem in adapting the same in your actual file..
Click to expand...
Hi Debraj
This Macro run Fine but can u give me for this File
 

Attachments

  • Pyra calculator New.xlsx
    13.3 KB · Views: 1
​
AFTER_%7E1.GIF
 
Code: 
'-----
For i = 1 To number_of_shapes
If Shapes(i).Name <> FIXED_SHAPE_NAME Then
Shapes(i).Delete
End If
Next
'-----
Hi, all!

Regarding this code structure, here're my comments:
a) Whenever deleting all items from a collection, you should start form the higher (last) value and end with the lower (first) value:
Code: 
'-----
For i = number_of_shapes to 1 Step -1
If Shapes(i).Name <> FIXED_SHAPE_NAME Then
Shapes(i).Delete
End If
Next
'-----
So when i=N, you'll be deleting the Nth shape, when I=N-1 idem and so on.
If you keep the For from 1 to N, when i=1 you'll be deleting the 1st, and so on, but when you reach N/2+1 you won't have shapes with such a high index but you'll still have N/2 shapes to be deleted.
b) The approach of using 1 as index instead of i works because it always deletes the 1st available shape (index 1), and it's independent of the i value used to control the no. of iterations.

Hope it helps.

Regards!
 
You must log in or register to reply here.
Back
Top