Posts by Darko_Giac

    Hi all,


    I'm fairly new to Excel VBA, and am trying to protect a workbook as much as I can.

    I've been able to get this VBA code working (which gives the user a warning message if the workbook has been copied to another location; the file then automatically closes, rendering the workbook unusable):

    What I'm trying to do is append a macro that then deletes the excel book once it has auto-closed. Something like:



    However, the trouble I'm having is...I can't get these two to work together quite as smoothly as I would like. When I run the Close_Delete sub, there will be a "File Not in Location" prompt, at which point a prompt appears asking if I want to save changes before the workbook closes. The interesting thing is...whether I select "Cancel", "Save", or "Don't Save", the file closes and deletes itself.


    I'm just wondering how I could revise the Close_Delete sub so that the pop-up box does not appear at all, and the workbook just closes and deletes itself after displaying the "File Not in Location" message.


    Any and all suggestions are welcome. Really having a tough time with this one!

    Hi Carim, thank you for the helpful tips! I was actually going to re-post with the Macro-enabled Excel workbook, but the link you provided has all the info I need to solve the issue I'm having :).


    Also, I posted the VBA code separately from the workbook because I was castigated on Stack Overflow, as they said "it could be a virus for all we know". This thread was MUCH more helpful. Thanks again!

    Hi all,


    I've slowly been chipping away at a report generator developed in Excel (using VBA) that will allow me to generate a narrative report based on exam scores.


    It seems to be working for the most part; however, what I am trying to do now is exclude sections of the narrative if scores for certain courses are not entered in the table (ie., left blank, as not all students may have taken all of the tests).


    For example, I have a table with two columns: The test, and a score in percentage points for that test. If there is no score for English, for instance, the VBA will generate a sentence, but with no score value. I would like to eliminate that sentence entirely if a score for English is left blank.


    Excel Workbook: XLSX Sample Workbook

    Ooohh I see what you did. That completely makes sense. I wasn't kidding when I said I'm a bit of a VBA newbie haha.


    I was under the impression that, whenever you open the editor (alt+f11), whatever you write in that module falls under the general umbrella of "VBA".


    Thank you very much for the help! I just wasn't sure why this particular line:


    was ignoring the "Mr". But I guess it's the way I've grouped the pronouns and titles.


    Thank you again!

    Hi all,


    I'm currently using VBA to build and generate a static report into which I can plug numbers (Table 1) and some demographics (cells A12 and C12).


    (I've attached a workbook for clarity, but I'll try and summarize here as clearly as I can, as I'm fairly new to VBA myself).


    Let's say I'm a teacher, and I need to generate similar (but not identical) reports based on tests students have taken. I would plug scores into Table 1, hit "Generate Report", and a narrative would be created using the numbers I have just input.


    However the issue I'm having is with a specific Else/If statement (highlighted in red). In cell C12 of the workbook, I have a "Gender" drop down list. I've created a pronoun string variable (sPrn) in my VBA code to tell excel that whenever it sees Gender in C12 as female, use "Mrs" as the pronoun, if "male" is selected in C12, I want the pronoun "Mr." to be inserted in the spots where I've placed the sPrn variable.


    When I run my VBA code, this works great when I select "female" as the gender; however, when I select "male", it does not insert the "Mr" pronoun, rather, just an empty space.


    Being new to VBA, I apologize if this is a rudimentary question, but thought I'd start here to see if anyone has any advice/suggestons.


    Thanks in advance,


    Darko




    Thanks!


    That worked very well!


    Apologies for the follow-ups, but if I wanted to do the same thing with data in columns P and Q this time, how would I edit the macro?


    So:


    Learning = N/A
    ASDS = N/A
    ADDH = N/A
    LMAP = N/A
    Other = Blank


    Learning, ASDS, ADDH, LMAP and Other are all in column P, the N/As and Blanks are in column Q.


    (Attached an updated workbook for reference)


    https://www.mediafire.com/file…l7rb9/TestBook2.xlsx/file


    Thank you again.


    Darko

    Hi all,


    I'm currently working with a fairly large data set (a sample of which is attached) that contains a drop-down list of options that respondents can choose from.


    I'm wondering whether there is a way to write a formula and preserve that formula when certain options are selected, but remove that formula from that column when others are present.


    In other words, I would like to remove the formula when "Other" is selected (or if there is just an empty cell); when someone selects "Other", there should be nothing at all (not even the formula) in the cell, but NA should appear when the other options are selected.


    Hopefully the attached workbook will clear the problem up slightly.


    https://www.mediafire.com/file…eaytic/TestBook.xlsx/file


    Any suggestions would be greatly appreciated, thank you!


    Darko