Posts by elevationzero

    Re: Zip Workbook & Email


    OK, Here's what I came up with


    I've tweaked some code posted by Richie(UK)


    First it will use SaveCopyAs to make a copy of the active workbook


    Second it will zip the workbook


    Third it will check to see the workbook has been zipped


    Fourth if its beeen zipped it will attach it to an email and place it in the outlook outbox



    So to have your users be able to simply click ONE button and have the currently open workbook zipped and emailed
    1. Import the module named ZipandMail from attached workbook
    2. Place a button wherever you want and include the following three lines.


    Code
    Call ZipIt
    Call CheckFileExists
    Call mailit


    ***Change source, destination and email information as needed***


    Im pretty ham-handed at this so if any of the more advanced users have a better method or notice any problems with how I've gone about it I would greatly appreciate the input

    Does anyone have one of these or do I need to modify some of the others out there? I've seen a few macros that accomplish these tasks but I want to have the user simply click a single button and have the active workbook zipped and sent to outlook. Is this possible or will it need to be saved under a different name first?

    I would like to create a routine that automates the save process, specifically I would like for the user to click file/save, and then have the save name match the value in a "job number" cell. So if the user runs the save routine, it will automatically save as cellsvalue.xls . Is this possible? and if so, would it be possible to check the value of the cell and if the cell is blank return a message forcing the user to enter a value in the "job number" cell before they could save?

    Re: error involving vgo


    Sorry I made this post now, hehe : D


    The error was in fact exactly as stated, there was another worksheet named "vgo". It was caused by a typo in some code referencing an incorrect worksheet. I was coming off the end of a ten hour session when I made the post, I checked and rechecked to make certain there was not another worksheet already named "vgo". Then when I got up this morning and looked at it, it was sitting right in front of me. :confused:

    Can someone tell me what vgo is? We just upgraded to excell 2003, We regularly transport a product commonly known as vgo, however when it is entered in a text box it now causes an error. See linked picture. for the time being I told them to simply type in "vgo" instead of vgo and it works fine


    Just curious what vgo is, I dont have any books that reference it.



    http://tankermen.com/vgoerror.jpg

    Re: Macro button on different sheet


    I'm not sure exactly what you are after, If I understand you correctly what you need to do is this


    Take the code which is currently on your 'This Workbook' sheet and copy it


    open up your vbe- visual basic editor, if you dont know how you can find it by clicking on tools/macro/visual basic editor


    go up top and click on insert/module


    type in


    Code
    Public Sub mymacro
    paste the code you currently have contained in 'ThisWorkbook' sheet
    End Sub


    This takes the code in 'This Workbook' which can currently be seen only on that worksheet and places it in a standard module, where it can be seen by the entire workbook.


    Now do as Roy suggested. Im pretty new to this myself so If I've made any errors Im sure someone will be kind enough to point them out.


    If you could be really specific as to exactly what you want to happen in exact order Im sure we can get your buttons working as desired

    I was using speech recognition to enter data into cells. It misunderstood me in several cells and placed an @ symbol at the end of the data. Now if you click up on that cell it tries to send an email. How do I make this link go away? everytime I type data into the cells, they are blue and underlined and create this email link. Thanks for the help guys, and I apologize if this should have been in the Excel/Email section

    Re: proper syntax for activecell address


    Thank you so much, I actually began trying to use


    Code
    If Target.Address ="$A$10" Then Sheet2.Activate


    but i had the syntax incorrect, I kept toying with the syntax but sensed i was just getting further afield and ran here for help. The people on this site are the best, there is no way I could have gotten where I'm at in my application without all the help

    I want my users to be able to double click on a cell and based upon that cells address or range I want another sheet to be activated. Im currently trying to do something like this


    Code
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    
    
    If ActiveCell.Address = ("a10") Then Sheet2.Activate
    
    
    End Sub


    It does not matter to me what specific thing about the currently selected cell is used in the if then statement, but it cannot be the cells value as several cells have the same values.


    Am I on the right track and just bad syntax or am I way off base here?

    Re: variable scope


    wow, I cannot thank you enough, I realize now that the answer was contained many times over in some of the posts. I simply didnt realize what they meant by modules untill you said open the vbe. you have just solved several hours of frustration for me. People like you deserve medals for answering beginners questions.

    Just starting with excell and am working on a project for my class. I cannot figure out why the value of the variable "changer" defined by command buttons on sheet1 are not being passed to the procedure contained in command button on sheet 2.


    I declared the variable in the general declarations section of sheet1 with

    Code
    public changer as integer


    The command buttons on sheet 1 contain

    Code
    Private Sub CommandButton1_Click()
    changer = 1
    Cells(1, 1) = changer
    End Sub
    
    
    Private Sub CommandButton2_Click()
    changer = 2
    Cells(1, 1) = changer
    End Sub


    The command button on sheet2 contains

    Code
    Private Sub CommandButton1_Click()
    Cells(1, 1) = changer
    End Sub


    When I click on the command button on sheet2 nothing happens.
    I've spent a couple hours reading forums trying to figure this out, Im sure its something very basic that im missing, any help is greatly appreciated.

    I would greatly appreciate any help with this, Im sure some of you will see my error right away.


    I am trying to create a program to automate the gauging figures on oil barges, currently everything is done by hand and takes approximately 20 minutes to complete. I have the charts entered for the tanks already and have the code set so that when you click on a tank "gauge" it will enter the "volume" which corresponds to that gauge on a totals sheet.


    Here is where my problem is coming in


    The barges are gauged at four points
    "before loading"
    "after loading"
    "before discharge"
    "after discharge"


    I want to set a worksheet as the default page with four command buttons that let the user select which operation he wants to perform. ie
    before load figures
    after load figures


    and depending on which operation they select have it enter the volumes in the appropriate cells on the totals sheet.


    So if someone selects "loadopen" command button I want the following code to run on my worksheets.


    ==========================================================


    ==========================================================


    and if they select the "loadclose" command button I want this code to run


    ==========================================================
    now the only thing that has changed are the cells into which the number will be placed.


    So how do I accomplish this, "i've tried IF then statements without success" I probably just did not set it up correctly


    My other question is about the variable "oneportvolume" do I need to change the variable also to prevent the code from overwriting the previously entered number?


    Sorry if this was too long or too much info. If needed I will try to shorten the questions


    Thanks so much to anyone willing to help me