Posts by Gizzmo

    Is there code which will allow me to create a stacked bar graph (the intention is to end up with a waterfall graph) from two series of data, without having to select the range each time new data is added to the range of source data for the series.


    For Example:


    The range might be A1:A4 for Series1 and B1:B4 for Series2 for the source data below


    Profit04: Series1 Data = 0, Series2 Data = 8
    Sales: Series1 Data = 8, Series2 Data = 4
    Margin: Series1 Data = 12, Series2 Data = (2)
    Profit05: Series1 Data = 0, Series2 Data = 10


    I now insert another row (as shown below) to my series of data and I need the range for Series1 and Series2 to update automatically to A1:A5 and B1:B5 respectively.


    Profit04: Series1 Data = 0, Series2 Data = 8
    Sales: Series1 Data = 8, Series2 Data = 4
    Margin: Series1 Data = 12, Series2 Data = (2)
    Tax: Series1 Data = 10, Series2 Data =(2)
    Profit05: Series1 Data = 0, Series2 Data = 8


    Any suggestions/help would be appreciated.

    Re: marco to request password


    Here is some code you may find useful. It works by user selecting a Command Button which then prompts the user to enter a password before they can view a user form and edit the information displayed.


    Try replacing "frmEditClaim.show" with your macro code!



    Code
    Private Sub CommandButton2_Click()
    Dim Password As String
    Do Until Password = "edit"
    Password = InputBox("Please enter password below", "Password", "????")
    If Password = "" Then
    Exit Sub
    End If
    Loop
    frmEditClaim.Show
    End Sub

    Timecard/Timesheet Function (Solved!)


    If any one is interested, I have managed to come up with a soulution of sorts, It's not ideal and for what I am trying to do it would probably be better to use VBA or a Third Party software that has 'overtime' generation.


    I have atached file.


    The Defaults sheet is used to enter a week ending date which updates the day and date on the Timesheet sheet (sweet!). Hidden columns to the right of each day (as well as rows) with a series of IF, AND, SUM functions which I have used to create criteria based on the total enterd at each line and the particular day of the week as well as the Employement Status.


    Note: The assumption is that the order in which time is expended is the order in which it is keyed (this has its limitations!).


    Protection is on but no password is required to disable it simply go to Tools, Protection, Unprotect Sheet.


    I hope it makes sense and can be of help to some one.


    Gizzmo

    Tim,


    Appreciate the reply, but this is not what I need.


    It's not the pay rate that I need the formula to calculate, its the remaining hours based on the rules as previously outlined.


    If I have a maximum of 2 hours for Time & Half on a Saturday then regardless of total hours worked I only want to record less than or equal to 2 hours based on how the total hours are distributed to each account/cost centre.


    Thanks any way,


    Gizzmo

    I am developing employee timesheets, (I am aware of excel templates for this but they are not suitable), I have a problem when it comes to calculating overtime hours for "Time & Half", for hours worked on a Saturday based on the following rule:


    Saturday:
    First 2 hours @ Time & Half
    Remaining hours @ Double Time


    It is intended that the total hours worked for the day are entered against a number of account numbers/cost centres for example:


    0.50hrs against 19165.1412.12000 therefore record 0.50hrs @ Time @ Half
    0.25hrs against 19165.1412.12001 therefore record 0.25hrs @ Time @ Half
    0.25hrs against 19165.1412.13000 therefore record 0.25hrs @ Time @ Half
    1.00hrs against 19165.1412.14000 therefore record 0.25hrs @ Time @ Half
    record 0.75hrs @ Double Time
    4.00hrs against 19165.1412.15000 therefore record 4.00hrs @ Double Time


    Any suggestions would be appreciated.


    Gizzmo

    I have recorded macro (as per code below) to change Text to Columns.


    Is it possible to write VBA code which will automatically select "OK" and or "Cancel" when Microsft Excel prompts with the following message window:


    ! Do you want to replace the contents of the destination cells?


    Sub Macro3()
    Selection.TextToColumns Destination:=Range("B3"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,_
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :=".", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2))
    End Sub

    Solved! well no quite.....


    I have instead created my own user form and and using a combination of "INDEX" and "MATCH" formulas, together with a combo box on the user form been able to populate the form with the details of the existing record.


    This functionality is not as good as the existing Data Form Excel offers i.e. edit, add, find next record etc. but it will do!


    Thanks to all that replied.

    Weasel,


    We are stumped! I had to change your code slightly......


    Loop
    Sheets("Register").Visible = True
    Sheets("Register").Select
    With ActiveSheet


    ......but it still stops on .ShowDataForm!


    Thanks any way.... if you could I would appreciate an attachment of your workbook .... I can't see why you are able to enable the DataForm with the code and I am not!


    Gizzmo

    Weasel


    I am running it as part of another Sub, the user clicks a command button which then prompts (using an Input Box) for the password "edit", if password is enterd correctly then the "Register" sheets DataForm is enabled, at least thats what I am trying to achieve.


    Thanks


    Gizzmo


    Here is the Code:


    Private Sub CommandButton2_Click()
    Do Until Password = "edit"
    Password = InputBox("Please enter password below", "Password", "????")
    If Password = "" Then
    Message = "Cancel"
    GoTo MessageEnd
    End If
    Loop
    Message = "OK"
    MessageEnd:
    If Message = "Cancel" Then
    'MsgBox ("Return to Main Sheet")
    Else
    If Message = "OK" Then
    Sheets("Register").Visible = True
    Sheets("Register").Select
    Sheets("Register").Range("B7").Select
    Sheets("Register").ShowDataForm
    End If
    End If
    End Sub

    I have a user form with a number of text boxes and combo boxes. If the user enters information into textBox1 eg "03001" I need to lookup a matching record on a spreadsheet and bring back the information relating to that record (ie information to the left and or right of the cell in the same row the record appears) to the relevant textBoxes and combo boxes, so that the information can be edited and then upon clicking a command button update the record with the new information.


    Any suggestions would be appreciated!