Posts by Halvar

    Re: Passing variable from child sub to parent sub


    cytop and PCI, that would be a good solution, but unfortunately Child needs to stay a Sub as it runs a lot of other stuff.


    Here's why I'm doing it this way: Child sub opens an external workbook and imports some information. If that information has already been imported I want to exit Child and go back to Parent. Parent has a message box notifying the user whether the Parent macro has finished running successfully, and it evaluates a number of things in Parent to determine that. One of the factors that I would like to consider is whether Child sub was able to successfully import the information. I thought the easiest way to do this would be to pass a boolean variable from Child to Parent (True for successful import and False for unsuccessful).


    Hope this clears things a bit.

    Re: Run macro in another worksheet (Advanced )


    oddy, no offense, but you're basically asking people to do all the work for you. It's a relatively easy macro to write, and I'm sure would be an excellent learning experience for you if you're interested in learning VBA. So, unless you're willing to pay someone for this work, I think your best bet would be to try to move forward with this on your own and post pieces of the code you get stuck with here so that we can help you.


    A couple things you would need to learn for this project:
    1. Workbook.Open and .Close methods (http://www.excel-easy.com/vba/examples/close-open.html) - to open the file affected by the change and save it before closing
    2. Adding worksheets to your workbook and renaming them (http://www.ozgrid.com/VBA/add-worksheet.htm)


    I think those would be good starting points for you. As I said, if you get stuck feel free to reach out for help and we would troubleshoot with you.


    Good luck!

    Would anyone know a way to pass a variable from a child sub back to its parent sub (i.e., from a sub that's been called in the main routine to that main routine? Something along the lines:


    Code
    Sub Parent()
    
    
    Dim i as Integer
       Child
       ...
       i = i + 1
    
    
    End Sub


    Code
    Sub Child()
    
    
       i = 1
    
    
    End Sub



    And then when I continue in Parent sub after executing Child sub it would already know that i = 1 since that's what we've determined in Child sub. So, my answer in Parent sub would be 2 in the provided example.


    Would appreciate your help!

    Re: I am trying to populate a range from a column on another tab based on a dropdown


    Hi there, one way (not so elegant) to do this would be to use formulas. In cell A2 on your second tab enter the following:


    Code
    =IF(INDEX(Sheet1!A2:C2,MATCH($A$1,Office,0))="","",INDEX(Sheet1!A2:C2,MATCH($A$1,Office,0)))


    and drag that formula down to, say, row 20.


    Note that using VBA would probably be a better answer here though - whats your preference?

    Re: Protection - Can Add Data but Can't Delete It


    Jason, I agree with Roy that you would need to use VBA for that.


    The approach that has worked great for me in the past is to lock cells based on their color. So, for instance user enters new values in your workbook on Sheet1. When the workbook is saved, newly added cells on SHeet1 are pained a certain color (you can do this with Workbook.BeforeSave event, for instance). Then, use code similar to this one to protect your cells from editing (it's not perfect but you can tweak it a bit or look for answers as Roy suggested): http://www.mrexcel.com/forum/e…ct-cells-color-basis.html

    Hi everyone, I have a (hopefully) simple problem - I'm trying to copy and paste a number of columns in my file. The code I have works just fine if the sheet I'm trying to change is the active sheet; however, when another sheet is activated I get the "Application-defined or object-defined error" message on the following line:


    Code
    Sheets(ws.Name).Range(Columns(1), Columns(2)).Copy


    My research shows that the problem with the code might be that .Cells property (and I'm assuming .Columns works the same way) by default references the active sheet. The suggested solution has been to use "With ws" statement similar to below:


    Code
    With ws
         .Range(Columns(1), Columns(2)).Copy
      ...
    End With


    However, this causes the code to bug out as well. Any suggestions on how I can improve my code to make it work without activating the sheet where I'm doing the copying and pasting?


    Thanks in advance!

    Re: Range object: string longer than 255 characters


    Thanks S O, these are excellent suggestions. I would imagine Rows() has the same 255 symbol limitation, right? I will look into an array solution, my problem is that I will likely exceed 255 limit pretty fast anyway, I'm looking at hundreds of rows that need to be hidden.

    Hello all,


    I was hoping someone may direct me in my search for a solution to a pretty simple problem. In my code I would like to (1) use a loop to build a string of rows that need to be hidden (figured this part), and then (2) use the string I've built to hide all the rows using Range().EntireRow.Hidden = True.


    The line of the code that returns an error is as follows (rowsToHide is a variable that I use in the loop to built a string of rows that need to be hidden):


    Code
    .Range(rowsToHide).EntireRow.Hidden = True


    The code works fine if the rowsToHide variable consists of a relatively small numbers of cells (e.g., if rowsToHide = "A1,A3,A8" the code would work just fine and will hide rows 1, 3 and 8). However, if the value of rowsToHide becomes something like "A2,A5,A6,A7,A9,A12,A13,A14,A16,A19,A20,A21,A23,A25,A28,A29,A30,A32, ...." (and so on and so forth) the code will return an error instead of hiding rows 2 5, 6, 7 etc..


    From what I can tell this is because the .Range() object is limited to 255 symbols. I was wondering if there's a workaround for this problem? Maybe, a different approach? I would very much like to build a string first and then hide all the rows as hiding them one by one would be very slow.

    Re: sort


    You just beat me to it - basically, the same logic should apply. As the solution you've posted suggests, you need to replace the alpha symbol with the one you're trying to count (in your case, "-")

    Re: sort


    You might have to explain to us the purpose of this exercise a bit better as I'm also having trouble understanding where you're going with this.


    Are you trying to sort your list first by column B, then by column C, then column D, E, F, etc.? If so, assuming you're using Excel 2010, go to Home => Editing => Sort and Filter => Custom Sort. There, you should be able to Add Levels to your sorting.


    Since you've mentioned you're trying to find the number of occurrences of the minus symbol "-" in each of the cells in column A, here's a solution (taking cell A5 as an example):


    =LEN(A5)-LEN(SUBSTITUTE(A5,"-",""))


    The formula does first finds the length of the text string in cell A5 (=LEN(A5)). Then, it find a hypothetical length of that same text string from A5 as if there were no "-" symbols in the string (LEN(SUBSTITUTE(A5,"-","")). The difference between the two is the amount of times symbol "-" appears in your string.


    Hope this helps.

    Re: Sorting formulas into descending order


    Just to make sure, you're not looking to add %s to the pie chart itself as explained here http://www.homeandlearn.co.uk/excel2007/excel2007s3p9.html. Rather, you would like to include them on the side in the legend, is that right? If that's the case, you will probably need to continue using your formula to get to the proper labels.


    As far as sorting goes, how about using a VBA routine to do the sorting for you? You can make the macro code run every time the worksheet is activated, or make create a button for that.

    Re: Run Worksheet_Activate from another workbook


    Thanks Derk, you're absolutely right - the first part of my issue was caused by the fact that I open WB2 using VBA in WB1. Thus, since the first sheet that's activated when I open WB2 is one of the tabs where Worksheet_Activate should run, the event doesn't get triggered. I'm still trying to figure out why the heck it would not run on other sheets correctly, but that's a step in the right direction!

    Re: Run Worksheet_Activate from another workbook


    Quote from Derk;728881

    If it's Workbooks("WB_2.xlsx") it won't have any macros to activate.



    That is obviously a typo, and my workbooks are obviously not actually named WB_1 and WB_2. What I would be glad to figure out is how to make Worksheet_Activate event run from a different workbook - anyone had any experience with that?

    Re: Sorting formulas into descending order


    A few clarifying questions: are you sorting it in alphabetical order (i.e., based on attribute name - cash, commodities etc.)? Or based on %? Assuming the first scenario, you should be able to complete this in two steps:


    1. Number all attributes on Source sheet using COUNTIF formula;
    2. Pull already sorted data on your Destination worksheet using Index/Match or Vlookup (Index/Match does the exact same thing as Vlookup, but is better for a number of reasons - not relevant to this task, but is worth exploring)


    I hope the attached helps.


    forum.ozgrid.com/index.php?attachment/62665/

    Hi all,


    Is it possible to fire Worksheet_Activate event from another workbook? Let's say I have workbook "WB_1" that references sheets in "WB_2" through VBA. On Sheet1 in WB_2 there's some code that gets triggered when the sheet is activated. So, in a perfect world I would want to be able to run the following code in WB_1:




    Only, the ws.Activate piece doesn't trigger the Worksheet_Activate event. Any suggestions?