Posts by DOC

    Re: Disable Button On Sheet From Vba

    Hi Simm, it looks like you have a shape your working with, below is a few basics that might help, but i think there may be a lot more work to this than just that as you may need to ref your text required to a cell, and also maybe a drop down box for your macro's required etc before you run the code.

    If you want to pop up a demo, happy to look at it

    Re: Specific Cell Total

    Quote from mspiels

    That would work, how do I make that work if I want it to output the result to a different sheet within the same book?

    Copy that formula to where you want the output to be ( different sheet ) then in that code highlight the first ref (a1:a40) =SUMIF(A1:A40, "Bobcat Co", C1:C40) then with it highlighted select the sheet the data is on and highlight the range A1:A40 then press enter, do the same with the second part C1:C40 and press enter.

    and you should be fine. example =SUMIF(Sheet2!A1:A40, "Bobcat Co", Sheet2!C1:C40)

    Re: Open/close Workbook Depending On A Part Of String

    Why dont you reference each file type ie type Concentració Empresa Emissora on a hidden page say called FILECHECK in cell A1 then have you macro read something like

    Note: You will probably need to add an error handler if done this way.

    If Sheets("FILECHECK").Range("A1").Value <> "Concentració Empresa Emissora" Then 
        Msg = MsgBox("Error in the file opened. Look at the name of the file next time." _ 
        & vbCrLf & "The program will close without finishing the process. Start again. Thanks", _ 
        vbOKOnly + vbCritical, ("END OF PROCESS")) 
        ActiveWorkbook.Close Savechanges:=False 
        Exit Sub 
    End If

    Re: Deleting Matching Debits With Credits

    Hi AYCH, Are there any Date colums etc or any other stuff, perhaps if you could paste a small example of your speadsheet please and Ill try and sort some thing for you.

    Re: Get The Value Of A Cell Before It Was Changed

    Did you want to use that number in that macro or much later down the track, second option is it will need to be stored somewhere else

    the first is to do something like

    MYSAVEDNUMBER = Range("B2").value
    then later in your macro you can reacll it as
    range("b55").value = MYSAVEDNUMBER

    Re: Macro To Show/hide Comments

    Quote from simonpnewman

    I think I didn't explain too well. I can extend the range, say from 1-100 in the VBA, but when you physically go to insert a row above, say, 15, it won't let you due to the autonomous code which gets activated everytime you highlight the row. So is there a way to modify the code so that rather than clicking the cell to make the tick appear and the comments become shown, you just type something in the cell to make the comments appear, and when you delete the contents of that cell, they become hidden again.
    Many thanks.

    Yes there would be a way around what you say above by disableing the events macro, anyway here's a version more to what you were after with the modified code, it works by placing the letter A into col A as you wanted, and i have added a button to the top to run it. To have it so when you deleted the tick it auto hides brings us back to the events macro problem so you will need to hit the button to finish all changes made.


    Re: Macro To Show/hide Comments

    Quote from simonpnewman

    Perfect! Many thanks :)[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Actually, although that works really well, I am finding a few issues with the automomy of it, such as trying to insert rows etc, or if you select all cells it can modify a few other cells. Is there anyway of making it so that it does it without clicking, i.e. if you put anything in column A, it show/hides the comments for the relevant row, i.e. somekind of if A2<>"" then Show Comments, If A2="", Hide Comments.

    Any ideas?! :)

    Yes it easy enough to adapt that way and , is there a set range you will always work in ie 1 - 100 or is this a variable ?

    Re: Macro To Show/hide Comments

    Quote from simonpnewman

    I have a standard laid out spreadsheet, but in column C, D and E, there are comments in each cell which are filled with a lot of text. I would like to set a macro so that whenever an 'a' is in column A, the comments for that row are shown/hidden (the 'a' being a tick symbol in Webdings). So if I place an 'a' in A4 and A23, and click a show/hide button, it causes the comments in C4, D4, E4; C23, D23 and E23 to be displayed, and when it is clicked again (or it could be a separate button), they disappear. If no row has an 'a' in the A column, then the button has no action.
    I have recorded a Macro of me showing and hiding comments, but of course that is for a specific row.
    Many thanks.

    Hi simonpnewman, i have attached a small example of just one way it can be done, this may not be what you need as each row will need to be set up, but it eliminates the need to place ticks or press a master button. if you just press a blue cell ( on and off )you will see what i have done. you can adjust the range in the sheet as it is an events macro
    Hope it helps

    Re: Correct Code For A Quotation Mark

    Quote from Dave Hawley

    Oh stop being so over-sensitve and admit you worded your Thread Title with what you thought was your answer. I'm not treating you like a "goose" or an "idiot" at all, simply holding you to which you agreed. The whole idea of forums is so that the majority in need of help can search for their problem and not have to click through 100's of Thread Titles with what the asker thought was their answer.

    As I have said, if your issue is Text File is Importing With Double Quotes you should have simply used a Title along those lines.

    Dave I did word it wrong, but not by intent and made that clear in an apollogy above and i will apollogise again for that, with that done is there any advice on the best way to resolve this problem from happening or a way to create the desired outcome, any help would be appreciated.

    Re: Correct Code For A Quotation Mark

    Quote from Dave Hawley

    If you cannot abide by the rules to which you agreed, then it's best. The choice is yours though.

    You know Dave, the funny thing is am far from stupid, i bent over backwards to get the title right, ( for a confusing question that i was unsure of the correct way to express it ), now in that one feels like they are a goose and treated like one.

    So as a goose, i will be happy to help people out, but refrain from asking the very odd question i may have and just sort it myself, i simply do not deserve the implications of being an idiot or being treated like one.

    Re: Print Range

    Quote from cbjorgol


    This might be a simple fix, but maybe not. I have a print range set up that is based on a named range that is an offset formula. The problem is that I would like to set it up as portrait instead of landscape, but if I do that the area to the right doesn't all fit on the page. The print area will always be a certain width, just different hights. I just want to be able to have the print range adjust downwards and still fit the whole area on one page wide.

    Hello cbjorgol, I may be on the wrong track with this but i think your answer does not lie in your coding as i think it is in your setup.

    If you go to page preview > setup then check the FIT TO option to 1 page wide / one page high then this should solve the problem

    Re: Correct Code For A Quotation Mark

    Quote from Dave Hawley

    I'm not disputing you don't the resolution to your issue, however it is spelt out on the New Thread page that Thread Titles should NOT be used to state what you THINK is you answer. You also agreed when joining as part of the rules.

    Use them ONLY to describe your problem, or to describe what you are wanting to do.

    Well Dave, if i got it wrong i appologise, I tried very hard to get it right and abide by forum rules with no ill intent, i will mosey along as not to cause this forum any more greif


    Re: Delete Rows With Column cell value less Than entered value

    Quote from pilotom

    Hi all,

    My knowledge of VBA is very limited. I need help in trying to make a macro I have do an additional step. I have a column (D) in a worksheet that is showing time values (2:30 PM, 3:15 PM, etc.). I need to code the macro so a user can enter a time value and the macro will then delete any rows in that worksheet in which the corresponding cell in column D shows a time value LESS THAN the value entered by the user. I've seen a couple of macros that delete rows matching a string in a cell but nothing that deletes based on the cell value being less than. I would appreciate any help you call can offer. Also - I need this to be done as a macro. I know how to filter the column manually to get what I want but I need this as a macro so people who are not familiar with Excel can simply open the workbook (I have the macro set to run automatically), enter the time value (ex. 2:30 pm) and they'll get all the rows in which the time is greater than or equal to 2:30 pm (the rows with an earlier time having been deleted by the macro).

    Thanks in advance.

    Hi pilotom, if you have your data in cell from row 2 down and a filter in row 1 and type into cell d1 the time you want to delete less than, then this should be OK, you can adjust to suit but it gives you an idea of how it can work.

    Re: Correct Code For A Quotation Mark

    Quote from Dave Hawley

    So why have asked something and worded your Thread Title which you THINK is your answer. Do you tell your Dr what you THINK you need prescribed?

    Dave, I am just doing my best to describe this very perplexing and confusing issue.

    I have attached an excel file of what i start with and a .txt file of what i need it to end up looking like via a save excel page as .txt page

    It is vey tricky as really has me stumped, i have numbers that need to be inverted into quotation marks and it needs comma's between theses.

    Now i can save it in a csv file but the other end needs to use it in text format so it needs to be in .txt

    Dave not here to be funny etc and apoligies to if it seems that way, i really at all losses with this problem and how to get from test.xls to test.txt with some coding


    Re: Correct Code For A Quotation Mark

    Quote from Kenneth Hobson

    A CSV file is a TXT file. What difference does it make? If it imports back properly then all should be well.

    There in lies the problem, its not importing properly due to importing """TEST""" not "TEST" as indicated in the .csv file, a real head ache this is causing thats for sure

    Re: Close Application Window With Macro

    Quote from robnot

    I'm running into a problem when I'm using the Application.ActiveWorkbook.Close method within the Workbook_Open event. The workbook opens and closes, but Excel itself doesn't close, even though there are no more workbooks open. Is there a way to ensure that Excel closes?


    try the quit option and you should be fine


    Re: Emailing Single Sheets From Spreadsheet

    Quote from simon_rexel

    i have got several work books in excel with lots of seperate sheets.
    is there any way of setting up a macro to automatically email certain sheets to certain email addresses without having to move the sheets into seperate work books?

    Yes you would get the macro to copy the worksheets needed only then send that copy to the desired person, then close the copy without saving it, this would be one way it could be done.

    Re: Correct Code For A Quotation Mark

    Quote from norie

    Like I said this is probably to do wtih the save.

    And without knowing how you are doing that I don't think we can help much more.

    Sorry norie i misunderstood your post / save section of code shown below

    Sicarii This doesn't give you what you want?

    ActiveSheet.Range("C4").Value = Sheets("PRODUCTS").Range("Y1").Value

    Sicarii , no that only produces TEST and is not then inthe quotation marks needed as "TEST" , when viewed in the created .csv file using what i have it looks all good as "TEST" as soon as i import this into the .txt format needed it then shows """TEST""" , I'ts nearly like te extra quotation marks are some how hidden in the .csv file. but how to code to get a sinle quotion mark each side of TEST is beating me

    Re: Correct Code For A Quotation Mark

    Quote from norie


    This might actually be nothing to do with the code - it could be do do with how the file is being saved and what format it's being saved in.

    I am saving this data to a worksheet in excel, i then save that sheet as a .csv file, this file needs to be uploaded as a text file in a text / number format.

    What i see in the .csv file is what i want ie "TEST", but as soon as i open a .txt file and then open the .csv file into it i get the error as

    This has me stumped for over a week now on this one problem