Posts by sleepeeg3

  • To Return Information from cells Across Multiple Rows Based On Value In Cell X

    Re: To Return Information from cells Across Multiple Rows Based On Value In Cell X


    All he is trying to do is distribute the values pulled in from one function across multiple cells, based on the "project manager" field. Normally the output would be spit into a single cell.


    Did you ever figure this out? I am trying to accomplish the same thing.


    Here is some simple code I am using now:

    It will return your result, but each new value will be added to the same cell with a comma separator.

  • Checking For Shape Name On Click

    Re: Checking For Shape Name On Click


    norie, sorry that example was just for testing. I was also using it to pull the shape names.


    andy, wow that was easy - that did it! Thanks to both of you for helping start my day out right.

  • Checking For Shape Name On Click

    Re: Checking For Shape Name On Click


    Thanks! It seems like what I need. I can get it to generate a message box with the shape name, but I am having trouble storing it to a variable. I keep getting "object required" errors. What am I doing wrong here:

    Code
    Dim State As String
 Set State = ActiveSheet.Shapes(Application.Caller).Name '<- errors here
 
 MsgBox "This macro was called by: " & ActiveSheet.Shapes(Application.Caller).Name

  • Checking For Shape Name On Click

    I am trying to create a clickable image map. I have one macro that is activated when any shape is clicked, but it executes different conditions depending on which shape is clicked.


    How do I check whether a specific shape is clicked?


    Some of the code I tried:

    Code
    If ActiveSheet.Shapes("Freeform51") <> null Then
...


    Also:

    Code
    If ActiveSheet.Shapes(Shapes.Name).OnAction = "Freeform51" Then


    Also, is there an easy way to pull the shape names and manually change them?

  • Editing External Workbook With Code?

    Re: Editing External Workbook With Code?


    That did the trick!


    Here is the final code, fellas:


    Sheet1:


    Module1:


    Module2:


    Thanks for your help, guys. That error code should also come in handy.

  • Editing External Workbook With Code?

    Re: Editing External Workbook With Code?


    Have you read my code? It is active. MrkFrrl actually showed me how to do that...


    The problem seems to be that I have the code embedded in the sheet, instead of a module. When I unthinkingly put MrkFrrl's code in a module, it worked fine. So I am going to see if calling the module from the toggle button embedded on the sheet will work, as soon as I figure out how to do that.

  • Editing External Workbook With Code?

    Re: Editing External Workbook With Code?


    I tried Mrk's code straight up and it does do the change in a new workbook. However, even with his merged into my code, Excel still makes the changes in the default workbook.


    What it must be doing is ignoring the active workbook and taking the "Range" command and applying it to ThisWorkbook. Is there a way I can define the ActiveWorkbook at the same as defining the range?
    Something like "Workbooks("H:\Documents\Troubleshooting Docs\Macros\misc_practice.xls").Worksheets("Region").Range("..."


    It never fails to open the misc_practice.xls. It's just not making changes to it!


    Here is my current code:

  • Editing External Workbook With Code?

    Once I open a new workbook, how do I make it the active workbook to have a function make changes to it? Problem I am having is even after it is open, the function will only make changes to the workbook containing the Macro (Thisworkbook).



    Is there maybe a way to specify the external workbook in the Range? i.e.

    Code
    Workbooks("J:\Network Purchasing\CANCELLATIONS\MISC_Practice.xls").Sheet!'Regional Summary'.Range("E2:E50000").Replace What:="AA8983 - Amie Anderson", Replacement:="Anderson, Amie", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False


    ^This is probably wrong, but I have tried multiple combinations of this code, based on examples from the net and can not figure out one that works.


    Have never had any classes in VBA and only fiddled with macros a few times, so I am still learning the basic syntax.


    Can someone please take a look at this:
    MACRO: http://www.geocities.com/sleepeeg3/name_change_macro.xls
    DATA: http://www.geocities.com/sleepeeg3/misc_practice.xls