Replies

Not sure why I never brought closure to this issue. brettnewman is absolutely correct and I must have been unaware of the a action toolbar.

Thanks to all for the help.

You might consider applying your ‘psychic’ energies to identifying significant number in his life. Most people choose numeric codes based on birth dates, combination of dates (like two children born in 1980 and 1986… they might choose 8086), first or last digits of a social security or personal identity number.

Other than trying nearly a million possibilities or contacting the manufacturer or engaging a locksmith/safe cracker, I think this is the most efficient approach. Just keep track of the numbers you try so you don’t repeat.

Moz, would you mind sharing the macro for doing borders within a range? I have been struggling with something similar and a peek at your code would help.

Thanks

Try a debug line to see what the value of .formula is. In front of the If statement add this line
Debug.print .Formula

Then show your immediate window (Ctrl-G) and run the code. What is the value shown in the immediate window?

Have you also tried the alternate one line code I offered? I thought this would be better for floor layouts since it gives you four directions rather than two.

1) The one line of code works well and I have it as a rotate macro… the other is just a fast toggle when I work with rectangular objects (shapes).

2) I did the debug thing as you suggested and found that the value of .formula was ‘0 deg.’ NOTE the period at the end of deg. So, I changed the macro to include a period after every instance of deg and it works fine now.

3) Many thanks to you for this. Now, if I can figure out how to have a button on the tool bar to which I can assign the macro. I can only get one button that shows a dropdown with all the macros.

Did you select an object on the page before running the code? Your sample code included a specific object to rotate, my effort allowed you to select an object to run the code on.

My problem with the macro not running was due to the macro security setting within Visio. Once I changed that, the macro runs but not as expected.

The macro is as follows:

Code:

 Sub Toggle_Shape()
    With ActiveWindow.Selection.Item(1).CellsSRC(visSectionObject, visRowXFormOut, visXFormAngle)
        If .Formula = "0 deg" Then
          .Formula = "90 deg"
        Else
          .Formula = "0 deg"
        End If
      End With
    End Sub

If my selected shape is at 90 then it will toggle to 0. However, if the shape is 0, it will not go to 90. I reversed to code just for testing to the following

Code:

    Sub Toggle_Shape()
    With ActiveWindow.Selection.Item(1).CellsSRC(visSectionObject, visRowXFormOut, visXFormAngle)
        If .Formula = "90 deg" Then
          .Formula = "0 deg"
        Else
          .Formula = "90 deg"
        End If
      End With
    End Sub

What I find is that the IF statement always resolves to false and the ELSE is executed. In other words, if my selected shape is at 0 then it will toggle to 90. However, if the shape is 90, it will not go to 0.

Thanks Andrew but when I put that code in (between the sub & end sub). It does nothing that I can see.

I have a column in a MS Excel spreadsheet, some of whose columns contain numbers and others contain a question mark. Is there a way to count only how many cells contain a number that is great than 0? I don’t wnat the cells containing a question mark to be counted.

If your range is A1:C5 use this formula (replace the range accordingly)

=COUNTIF(A1:C5,”>0″)

Got it. thank you.

Andrew – a question…

In your code you have

If Target.Address = “$N$8” Then

I am curious why the IF since the user has already said he wanted N8 to contain the sheet name. Also, how would VB be able to determine if the Target.Address = “$N$8”?

Just curious… if you did want to make it an add-in, how do you do that? and how would you share the add-in?

Would some kind soul point us the way please as this issue is getting us nowhere fast.

Regards, Bruno Terlingen

Bruno, I used CDex from http://cdexos.sourceforge.net/?q=download and it worked fine. I also came across this that appears to be an even simpler solution although I have not yet tried it… http://www.topbytelabs.com/freestuff/index.php?id=68

If you do try the second one, please report back how it worked.

Thanks Sam – I love this and will certainly use it… often.

First off my ‘Greetings’ to everyone here. What an excellent site for windows & related help.

Being a bit of an experienced computer troubleshooter I frequently get questions from friends. A lot of you here no doubt find yourself in the same situation, helping a neighbor or family member with what most often is a simple issue. I often ask if they perhaps took a bit of time & Googled for a solution. Most individuals must find Googling for an answer as something to be avoided at all costs. And what incentive is there to do that when I’m really just as easily a quick email or phone call away!!!

Now I’m a patient person & generally will answer a question as simply as possible. But, if asked something more challenging for which I have no clear answer I will simply say, “Let me Google that for you! I’ll get back to you ASAP via email.” That’s where what follows the sample question comes in.

Sample Question: Hey buddy! How Can I Tell If My Operating System Is 32 Or 64 Bit?

I then head over to: Let Me Google That For You & enter the search question.
The site generates a link for me that I will then copy paste into my reply email.

Here is the link provided for the 32/64 bit question: How can I tell if my operating system is 32 or 64 bit? [You, the reader, will probably want to click this link first to see the end result.]

That will usually bring a chuckle to my inquiring friend, who I can now hope next time will make the effort to give Google a shot first & then call me if he/she is unable to find an answer!

Best Wishes To All!

okay, I finally got it. Love it when the light comes on. Thank you.

When assigning a range to an object variable, you must use the Set keyword.
Also, the outter-most With construct isn’t used in your code. This comes down to:

Code:

Sub Resetvalues()
    ActiveSheet.Unprotect
    Dim wsh As Worksheet
    Set wsh = Worksheets("EVENT-Work")
    Set myRange = Range("B9:B110, H9:H110, O9:O110")
    For Each rngarea In myRange.Areas
        For Each rngcell In rngarea
            With rngcell
                If .Value  vbEmpty Then .Value = "0"
            End With
        Next rngcell
    Next rngarea
    Application.ScreenUpdating = True
    ActiveSheet.Protect
End Sub

Alternatively you can use the SpecialCells method:

Code:

Sub Resetvalues()
    Dim rngCell As Range
    ActiveSheet.Unprotect
    Application.ScreenUpdating = False
    For Each rngCell In Range("B9:B110, H9:H110, O9:O110")
        With rngCell
            If .Value  vbEmpty Then .Value = "0"
        End With
    Next rngCell
    Application.ScreenUpdating = True
    ActiveSheet.Protect
End Sub

Where can I learn more about how to use rngCell? The VBA help does not have anything about rngCell.

Thank you all for your help. There are, it seems, several ways to do what I needed. I looked in another resource and got yet another answer… see below.

Code:

Sub Resetvalues()
ActiveSheet.Unprotect
Dim wsh As Worksheet
Set wsh = Worksheets("EVENT-Work")
Set myRange = wsh.Range("B9:B110, H9:H110, O9:O110")
For Each cl In myRange
If Not IsEmpty(cl.Value) Then cl.Value = "0"
Next cl
Application.ScreenUpdating = True
ActiveSheet.Protect
End Sub