Posts by Excelman

    Hello All,


    I am after a formula which will return a result if a cells value is within a certain range, for example If the value in cell A1 is between 0.01 and 0.99 then I want to return a value of 1 in cell A2. If however the value of cell A1 changes and falls within the next range which is 1.00 - 4.99 then the the value of cell A2 needs to change to 2, I have 7 ranges so cell A2 return a result of 1-7,


    Hope this makes sense,


    Thanks.

    Re: Date When A Cell Was Updated


    Pangolin,


    Yes it works but with one problem, I have got it in "the before save" section, the problem is the date changes to the current date every time you open and save the sheet.
    If say the cell changed to "complete today then it sets the date as todays date, but if I open it say in three days time then the date will change to that days date?
    What I need is for the date to stay at the date the cell changed to "complete"


    Any ideas.

    In a workbook I have a date in cell A1, In cell A2 there is a formula which shows either "pending" or "complete", what I need is for cell A3 to show the date when cell A2 changes to "complete". I then need cell A4 to show the difference in days between the date in A3 and a date in cell A1.


    Does that make sense and is it possible?


    Thanks.

    I have a worksheet which is set like a database, it contains 10 columns. There is a auto filter facility bove each column. What I need is for the auto filter to automatically set all the columns to show all fields when the workbook opens as some users leave the filters on so it doesn't show all the info when the book is opened and you have to manually set all the fields to show all.


    Thanks.

    Re: Delete cell if empty


    As far as I know the cell is empty, the code works fine when tried on it;s own but as soon as I put it in with my other code it doesn't work??

    Re: Delete cell if empty


    It doesn't seem to highlight it when stepped through with F8, it is in with a whole load of other code (which all works fine), could it be something else stopping it?

    Hi All,


    Iam having trouble with this peice of code, basically if cell "P8" is empty I want to delete cells "O8 & P8" and shift the other cells up, for some reason it is not working, any ideas??



    Code
    If Range("P8").Value = "" Then
        Range("O8:P8").Delete Shift:=xlUp
        End If

    Hello all,


    I have a user form which is called by double clicking a cell (any cell), the code for this is:

    Code
    Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    UserForm2.Show
    End Sub


    The user form contains a text box which is supposed to display the contents of the cell, (whichever one was double clicked), the code I have for this is:

    Code
    Private Sub UserForm_Activate()
    TextBox1.Value = ActiveSheet.Range("ActiveCell").Value
    End Sub


    If a user changes the text and hits the ok button it is supposed to change the text in the cell, the code I have for this is:

    Code
    Private Sub CommandButton1_Click()
    ActiveSheet.ActiveCell.Value = TextBox1.Value
    Unload Me
    End Sub


    The problem is when the cell is double clicked I get a runtime error 1004, application defined or object defined error.


    Am I missing something obvious??

    Re: Macro Overwriting Existing File


    Norrie,
    Sorry I'll try to explain better,


    The excel sheet is created and saved, the button on the excel sheet is then pressed by the user to create a word document with a table from the excel sheet inserted in.
    If a user then goes back into the excel sheet and makes a change and presses the button to create a new word document, the existing one is overwritten with no warning. What I need is a box similar to the standard windows one which warns the user if a document with that name already exists and gives them the option to overwrite or cancel.

    I have the following code which copies a range of cells from a excel worksheet and copies them as a table into a word document, it then saves the word document with the name of the value of cell B1 in the excel sheet.
    It all works fine until somebody makes a change in the excel sheet, it then overites the existing word document with no warning. What I need is a way of it bringing up a message box to warn the user it is about to overwrite the existing document, this box is only to appear if an existing document with the same name already exists.


    Does that make sense.