Posts by MrkFrrl

    Re: Macro Behaves Differently On Second Run


    Post some of your code, especially the part when you hit the debug button.


    Going off on a wild tangent, I'd say that it's related to having another workbook open as the activewindow and then closing it, invoking your before_close event. Or maybe it's the other workbook that has the before_close event, in which case you'd need to put these tags before and after opening it:

    Code
    application.enableevents = false
    application.enableevents = true


    I subscribe to a newsletter where someone asked about having a graphic appear near a cell, based on the cell's answer. For example, say, if the cell had a nested-if formula and said, "Pear," a picture of a pear would appear; if an apple, that picture would appear, etc. I tried it, and it works. Someone else had suggested some changes to it, but I'm just posting my response here.


    In any case, this is what I came up with in response to it:


    First, you'll need to have one cell that will use an If-statement to display the word related to your picture. In this particular case, we'll say that cell A1 of the sheet you want to display the picture in - say Sheet1 - will use your IF-formula to display one of these words: "Apple", "Pear", or "Coconut". For simplicity's sake, let's use this formula in cell A1 (referencing the values in cell A2):


    =IF(A2=1,"Apple",IF(A2=2,"Pear",IF(A2=3,"Coconut","")))


    So if cell A2 has a value of 1, then cell A1 will say "Apple" and so forth.


    Next, select (or add) a blank sheet in your workbook, which we'll say is Sheet2.


    With your blank sheet (Sheet2) selected, go to Tools -> Options -> select the View tab (at the top) -> from the options, de-select (uncheck) the box for Gridlines -> and then click on the "OK" button, so that none of your Gridlines on this blank sheet show.


    Now, go to Tools -> Customize -> select the Commands tab (at the top) -> select "Tools" from the left-hand box, then scroll down about 2/3rds the way in the right-hand box until you see a "camera" icon. Left-click and hold down your mouse button on the camera icon; then, drag the icon to your tool-bar (where all your other menu choices are at the top of the Excel program). (NOTE: Before releasing the icon, it should appear as a plus-sign "+". You can put it either at the top on the right-hand of your menu choices, or in between other tool bars.)


    Between cells A1 and C9 of your new sheet (Sheet2), put a picture of a pear. Put a different fruit - an apple - between cells A10 and C18. Finally, put your third picture of a fruit - a coconut or whatever - between cells A19 and C27.


    On your new sheet (Sheet2), select cells A1 to C9 by left-clicking and holding in cell A1 and then dragging your mouse until it's over cell C9 and all the cells in between are highlighted. Now, click on your new camera icon. The area around the picture will show little lines around it. And your mouse-pointer should become a plus-sign.


    Now, go to the sheet where you want the picture placed (Sheet1). The plus-sign will still show. Left-click and hold your mouse button down, moving your mouse diagonally so that the outline of the picture is sized and in the place where you want it. When you release the mouse button, the first picture will appear in that spot. (You can click on this picture again to re-size it; and it is a dynamic picture, in that if you put a different picture in the newer sheet, it will be reflected here.)


    Now, go to your menu at the top and select Insert -> Name -> Define. In the top box, type in "FruitName" (without quotes). In the "Refers To" box below, type in a formula that references cell A1 on your current sheet and the three different areas of your sheet that holds your pictures. Here's an example, using what was previously given:


    =IF(Sheet1!$A$1="Pear",Sheet2!$A$1:$C$9,IF(Sheet1!$A$1="Apple",Sheet2!$A$10:$C$18,IF(Sheet1!$A$1="Coconut",Sheet2!$A$19:$C$27,"")))


    When you're done with this, click on the "OK" button. (Note: Be sure to use the absolute references here, designated by the dollar-sign, and then to go back and check it afterwards to make sure it didn't change, as it initially changed to an entire-sheet reference in my initial trial since I didn't use absolute references.)


    Now, click once on the "graphic" image that you've previously placed on your sheet (in Sheet1). If you look in the formula bar, it will say something like this:


    =Sheet2!$A$1:$C$9


    Change that formula to this:


    =FruitName


    Now, if cell A1 (of Sheet1) says "Pear" (without quotes), a picture of a Pear will be wherever you placed the "graphic" in Sheet1; and it will change to the other graphics also dependent on the text. (Or if the formula is kept intact in cell A1, it will change based on the number 1, 2, or 3 in cell A2.)


    You're just about done: just a couple more things.


    Select the sheet where the three graphics are kept (Sheet2). From the menu at the top, select Format -> Sheet -> and then Hide. This will prevent anyone from messing up your graphics, with the originals needing to be retained in their set spots.


    You can also select Protection on Sheet1, and it will have no effect on your graphic changing, based on the cell's value (though you may want to unprotect certain cells if need be).


    p.s. I'd recommend that you change your bitmap-image to a JPEG (or GIF), also, as it will take considerably less file-size to include a JPEG.

    Re: Word After Last Specified Character


    So you want a formula that, in the example given, would take your hyperlink as given and show up as this?
    sylvantropicalbirdi._t


    For the sake of the example, the link is in cell A6.


    I'm assuming that there will only be one period (.) in the text after the last backslash. I'm also assuming that there would be no pound-sign (#) in the hyperlink. (If there is, you'd change the formula below with another symbol than the pound-sign, such as an asterik.)


    I'm going to write the formula regularly and then put the same formula within the code-tags, as I'm somewhat new here and don't know what, if any effects (possible truncating?) occurs if you don't--even though it's not code and is just a regular formula.


    I was just curious if I could do something like this, and did it for no other reason. It took me a little while.


    It seems to work.


    So just put this formula in the cell wherever you want the result to appear, and change the references from cell A6 in the formula accordingly.


    =LEFT(RIGHT(A6,(LEN(A6)-SEARCH("#",SUBSTITUTE(A6,"/","#",LEN(A6)-LEN(SUBSTITUTE(A6,"/",""))),1))),FIND(".",RIGHT(A6,(LEN(A6)-SEARCH("#",SUBSTITUTE(A6,"/","#",LEN(A6)-LEN(SUBSTITUTE(A6,"/",""))),1))),1))&"_t"


    Code
    =LEFT(RIGHT(A6,(LEN(A6)-SEARCH("#",SUBSTITUTE(A6,"/","#",LEN(A6)-LEN(SUBSTITUTE(A6,"/",""))),1))),FIND(".",RIGHT(A6,(LEN(A6)-SEARCH("#",SUBSTITUTE(A6,"/","#",LEN(A6)-LEN(SUBSTITUTE(A6,"/",""))),1))),1))&"_t"


    Re: Show Grouping Detail


    It sounds like in the first case, it gives you an error because you're trying to run an Excel "4" macro named "SHOW.DETAIL(2,endr,TRUE,,r)". (And, aside from that, isn't Excel XP "10"?)


    I don't usually mess with grouping. If you're just trying to group everything together, would something like this work?


    Code
    Sub UnGroupArea()
        Range("A1").Select ' where A1 is the first cell of the area to be un-grouped
        Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Rows.Ungroup
    End Sub
    
    
    Sub GroupArea()
        Range("A1").Select ' where A1 is the first cell of the area to be grouped
        Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Rows.Group
    End Sub


    Re: Copy Cells Containing Specific Words


    No, that does NOT make any sense.


    And what does "dinf" mean?


    Here's my "best guess" on what you want to do, changing the code accordingly:


    Code
    If [B]Anbieter[/B] = "[B]DTAG[/B]" Then
                 DTAGKostenlfd = DTAGKostenlfd + Kostenlfd
                 DTAGKostenBereit = DTAGKostenBereit + KostenBereit
            ElseIf [B]Anbieter[/B] = "[B]dark[/B]" Then
                 ' do whatever else you need to do here
            End If


    Re: Concatenate & Copy Cells


    I think that the reason Dave probably hasn't responded to your feedback (new question) by now is that some of the things you've said just wouldn't make any sense to anyone who isn't employed where you work.


    You talk of "parent items" and "child items," but how would anyone know what they are outside of your immediate associates?


    This rephrasing of yours below also sounds much different than your initial question, which Dave already answered.


    And, by the way, what Dave said should still work fine by adding a column, inserting the formulas, using drop handles, selecting all, a paste-special of values, and then column deletion if necessary. This could have even been done with your macro recorder. Don't be afraid.


    As to what you initially said, if you really do require VBA, here's some code that should work. I think you should be able to adapt it to what you require. I don't know how experienced you are here, so let me just say that lines that begin with apostrophes are text.


    Always start by making a copy of the workbook, just in case something goes awry.



    Quote from amypoinsett

    Hi Dave,
    Yes, that formula. combined with copying with the fill handle, does work for doing this manually for each parent inventory item. What I was hoping for was a macro that would detect the size of the range to be filled and do it for me!


    Of the 20,000 items in my inventory list, about 5000 are parent items. Most parent items have some variable number of child items underneath them. I'm hoping there's a way to have a macro that runs down my inventory list, detects when it has reached a child item, determines how many child items there are under that parent and then does the required copying and formula-filling-in.


    Thanks
    amy

    Re: Password Protect Worksheets From Viewing


    You Dim iCount as an Integer, but then also use lCount (L-Count) interchangeably. If he had Option Explicit selected, it would cause an error.


    You don't need to to set iCount to 0, as the For statement puts it at 1 to 3.


    Also, the For iCount 1 to 3 makes the iCount = iCount + 1 unnecessary. In fact, it would probably skip a try if included.


    Also, I think the macro would cause the end-message box and exit the application in any case.


    I know it may sound as if I'm nitpicking, but I don't think the guy who started this line knows too much about any of this.


    Changes below:


    Quote from royUK

    That was a quick idea based on Dave's original code. This works by closing the workbook after a third unsuccessful attempt..

    Re: Password Protect Worksheets From Viewing


    The iCount would never = 4 here in your example, so never would know when to no longer give them chances to insert a password.


    I like what you did, but would change it slightly like this:



    Quote from royUK

    This is a simplified way, using Buttons to replace your hyperlinks. Each button will trigger a request for a password, all sheets are Very Hidden on closing the workbook. Basically, it adapts Dave's code.

    Re: Password Protect Worksheets From Viewing


    I'm not going to promise that you won't have problems with this, as I just put this together. But you can try this:


    Turn on the macro recorder.


    Click on a cell.


    Stop the macro recorder.


    Press the keys ALT-F11 simultaneously. You should see the Visual Basic Editor.


    Double-click on Module1.


    Replace the code for the macro you just created with this (between the lines):



    Now, double-click on the ThisWorkbook module and insert this code:


    Code
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On error resume next 
    Sheets("Sheet1").Visible = xlVeryHidden
    Sheets("Sheet2").Visible = xlVeryHidden
    Sheets("Sheet3").Visible = xlVeryHidden
    ActiveWorkbook.Protect Password:="OzGridCom", Structure:=True, Windows:=True
    ThisWorkbook.Save
    End Sub


    When the workbook is closed, it "should" hide the sheets cited above, protect the workbook's windows and structure, and then save the workbook.


    The sheets should be VeryHidden so no one would even know that they're even there (and you can also hide your VBA code to help keep things even more protected, though there is no guarantee that it can't be hacked).


    When you run the AskForPassword macro (preferably linked from a form button in a worksheet that's not hidden - from the Excel menu see View -> Toolbars -> Forms and then click on the Button after it appears, then selecting an area in your worksheet) and answer the password correctly, it will un-hide all the sheets.


    Your hyperlinks will then work as you'd expect.


    If you want different individuals to have access to only certain sheets, instead of -


    Code
    If MyInputBox = "OzGridCom" Then
        ActiveWorkbook.Unprotect Password:="OzGridCom" 
        Sheets("Sheet1").Visible = True
        Sheets("Sheet2").Visible = True
        Sheets("Sheet3").Visible = True
    End If


    - in the above example, you could try substituting something like this for three different people (with passwords of "OzGridCom1", "OzGridCom2" and "OzGridCom3") -



    NOTE: If using this last bit of code, still don't change the Private Sub's password or the ActiveWorkbook Password, following the basic guidelines above. You can re-name the sheets anything you want, changing the macros above accordingly, so no one will be able to guess the sheets' names.



    Re: Run Macro For Specific Workbook In Background


    If it's just one workbook running in the background, in addition to the activate method already cited, you can set up a userform for the workbook and then have a button for


    application.visible = false


    and then another button so that you can see it again when necessary


    application.visible = true


    I tried it on a workbook I've got that's set up to update regularly, with another workbook in the foreground active; and it seemed to work OK.


    If it encounters an error that's not handled, it may pose problems however. So be sure to have any error-handler to make the application visible.


    Otherwise, if worse comes to worse, you'd have to go to your task mananger, select processes, and actually close Excel that way.


    Quote from tgit

    yes, the excelsheet running macro will always be open (and records will be inserted at specified periods). But at the same time I may want to work on some other excelsheet which I am not able to do so.