Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

Probs with validation

2009-01-07
2013-04-15
  • Hartmut Erb
    Hartmut Erb
    2009-01-07

    Hello,

    first: congratulation and thank you for this wonderful soarklines - and sorry for my bad english...

    btw: i made some tests and found one problem with validation function (described as here http://chandoo.org/wp/2008/08/07/excel-add-drop-down-list/\) > the sparklines disappear if this function is activ and the dropdownbutton of the cells, which include this function also disappear when i press the button "Delete all shapes".....

    Greetings from South Germany
    Huckly

     
    • nixnut
      nixnut
      2009-01-17

      Uhm, well yeah. That dropdown button is a shape too, so it would be deleted in that case. Better stick to using the "Delete all sparklines" when you're using UI controles in your sheet. It'll likely happen when you apply a filter on a table too. Better yet, separate the data from the presentation by putting these on separate sheets.

       
    • Hartmut Erb
      Hartmut Erb
      2009-01-28

      Hi nixnut,

      thanks fpr your reply. The Problem is, that i will integrate the sparklines in an existing Excel-based MIS an this is the reason that it is impossible to seperate the data from the presentation and also to change the lot of validation cells in UI controles. Therefore my ask: Isn't it possible via vba to separate the shapes, which are deleted (e.g. shapetyp?). I hvae made some tests and the change of code ("And Not InCellDropdown ") in the following macro was successfull to avoid that validation-hapes were deleted.

      Private Sub DeleteSparklinesOnSheet()

      Dim shp As Shape

        If ActiveSheet.Shapes.Count = 0 Then
           MsgBox "No Shapes on page for deletion"
           Exit Sub
        End If
         
        Application.Cursor = xlWait

        For Each shp In ActiveSheet.Shapes
              Application.Wait DateAdd("s", 0.00001, Now)
              If Left(shp.Name, 4) = "Sprk" And Not InCellDropdown Then shp.Delete
          Next shp

        Application.Cursor = xlDefault
       
      End Sub

      But i was not successfull in changing the other relevant macros, especially the macro "RedrawSparklinesOnSheet()
      " :-(

      Do you have any idea?? The sparklines are Shapetyp = 6 and the validation are Shapetyp = 8
      finded out with:

      Sub ShapeTypeDefine()
      Dim shp As Shape
      For Each shp In ActiveSheet.Shapes
      MsgBox shp.Type & "/" & shp.AlternativText
      Next shp
      End Sub

      Thanks in advance
      Huckly

       
    • nixnut
      nixnut
      2009-01-28

      I'm just sure what's happening in your worksheet. The DeleteAllSparklines code should only delete sparklines images and leave all other images intact. So please answer these questions:

      1. Do the dropdown buttons disappear when you use the Delete All Sparklines button button? (note: Delete All Sparklines not Delete All Shapes)

      2. Do the Sparklines images disappear when you use validation on the cells with sparklines formulas?

      3.  Have you tried using shape type to delete shapes? Something like below:
      For Each shp In ActiveSheet.Shapes
          Application.Wait DateAdd("s", 0.00001, Now)
          If shp.type = 8 Then shp.Delete
      Next shp

       
      • Hartmut Erb
        Hartmut Erb
        2009-01-28

        Hi Nixnut,

        thank you for your fast reply:

        "1. Do the dropdown buttons disappear when you use the Delete All Sparklines button button? (note: Delete All Sparklines not Delete All Shapes) "

        > Yes, if i use "Delete ALL SHAPES" the sparklines ans also the dropdown buttons disappear

        "2. Do the Sparklines images disappear when you use validation on the cells with sparklines formulas? "

        > No, not immediately. But if i go to a cell and set the cursor behind a sparkline-formular and press Enter-Button the sparkline-shape in this cell disappear. And it is not possible to ractivate this sparkline shape. The sparkline shape also disappear, if i change a parameter in the sparkline formular, which is placed in an other cell.

        "3. Have you tried using shape type to delete shapes? Something like below:
        For Each shp In ActiveSheet.Shapes 
        Application.Wait DateAdd("s", 0.00001, Now) 
        If shp.type = 8 Then shp.Delete 
        Next shp"

        > Yes, but without success: all shapes, not only shapes = Typ 8 were delete

        Greetings
        Huckly

         
        • nixnut
          nixnut
          2009-01-29

          uhm...your answer to the first question doesn't seem to answer what I asked. There are two buttons to delete shapes. One button deletes all sparklines and the other button deletes any and all shapes. So let's try again. If you press the DeleteAllSparklines button do any and all shapes disappear?

           
          • Hartmut Erb
            Hartmut Erb
            2009-01-29

            Hi nixnut,

            ok, if i press the button named "Erase Sparklines on Sheet" and I think/hope this is the button you mean, when you say "press the DeleteAllSparklines button" all sparkline shapes disappear but NOT the validation shapes...

            Greetings
            Huckly

             
            • nixnut
              nixnut
              2009-01-30

              Yes, that's the button I meant. And that's the button you should use if you want to delete only the sparklines. So, the buttons work as they should.

              Now about your validation problem. I don't understand what you're doing. Can you give an example of how you use validation on cells that contain sparkline formulas?

               
              • Hartmut Erb
                Hartmut Erb
                2009-01-30

                Hi nixnut,

                i need an email adress to send an example...

                Greetings
                Huckly

                 
                • nixnut
                  nixnut
                  2009-02-03

                  I got your sheet. I'll have a look as soon as I have a bit of time.

                   
                • nixnut
                  nixnut
                  2009-02-04

                  One problem is that you used the airlines-example worksheet which contained a bug in the linechart function. The result is that some linecharts don't quite fit in the cell and don't get named properly. And those faulty linecharts then don't get removed by the "Erase Sparklines..." button. I've updated the example worksheet with newer code. You can try that and see if the problem still occurs.

                   
                  • Hartmut Erb
                    Hartmut Erb
                    2009-02-06

                    Hi Nixnut,

                    thanks for your efforts in reparing the airlines-example worksheet...Now it works fine!

                    btw: do you have found a solution or a workaround regarding the validation prob?

                    Thanks in advance and greetings

                    Huckly

                     
                    • nixnut
                      nixnut
                      2009-02-08

                      Can you explain what the problem is with validation? The file you send me didn't show me any problem other than the bug with the linecharts. I don't understand what you're trying to do and what you think isn't working.

                       
                    • nixnut
                      nixnut
                      2009-03-04

                      Hi again

                      Sorry for the late reply. I've tried the worksheet you send me and I see something that looks like behaviour I sometimes see with other sheets. For unknown reasons sometimes when I enter  a sparkline formula in a cell no image appears. Often this can be resolved by entering the cell (F2) and pressing enter or by clicking the RedrawSparklines button or forcing a recalculation of the workbook (ctrl+alt+F9). In rare cases however I need to close the workbook,  reopen it and then enter the cell again and press enter. This also worked on the workbook you send me. Can you check if it also works for you?

                      regards,
                      nixnut