Posts by Ranger

  • Macro Stops On Top Line

    Re: Macro Stops On Top Line


    Hi Bumfield,


    Your first line:

    Code
    ActiveWindow.ActivateNext


    will move you to the next Workbook. Is this what you want, because you said in an earlier post that you want to move from one SHEET to another.


    Bill

  • File Protection

    Re: File Protection


    Hi jjob68,


    If you put the following code into the ThisWorkbook module:

    Code
    Private Sub Workbook_Open()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Main" Then ws.Visible = xlSheetVeryHidden
    Next ws
End Sub


    It will hide all of the sheets unless it is called 'Main'. Users will not be able to unhide the sheets by selecting Format|Sheets|Unhide, because it will not show any hidden sheets.


    Place the following code in the Main sheet module:


    This will allow you to view and hide the sheets by typing in the password in cell IV1. You could use a command button, but this would just make the User curious and determined to access the data. You can hide the sheets at any time just by typing anything in cell IV1. Show the sheets by typing Secret in IV1.


    Bill

  • Validate Cell Range Via A Macro On The Save

    Re: Validate Cell Range Via A Macro On The Save


    Hi Bret,


    You can do it with Worksheet_Change() event and Workbook_BeforeClose() event code.


    The Worksheet change code will give a message if any of the data in the range is deleted. The Workbook before close code will not allow the workbook to be closed if the user has not filled in range B3:B11



    The Workbook before close code should be placed in the This Workbook module and the Worksheet change code in the appropriate Worksheet module


    Bill


    Bill

  • VBA Color Cells For Gannt Chart

    Re: Color Cells Based On Owner &amp; Time (gannt Chart)g


    Hi Kiwifinny,


    You could also do this by using the Worksheet_Change() event.



    This will automatically colour the cells as you enter the 'x' or 'X'. It will also remove the cell colour if you delete the 'x'.


    Bill

  • VBA Color Cells For Gannt Chart

    Re: Color Cells Based On Owner &amp; Time (gannt Chart)g


    Hi Kiwifinny,


    If you could have blanks in column A, then you will have to adjust the code slightly. I have also changed the Range 'Names' by adding another column and adding the ColorIndex for each name into the new column, so that it automatically reads the color index when you select a new name in column A.


    I have added the code to the button on the sheet.


    Bill

  • VBA Color Cells For Gannt Chart

    Re: Color Cells Based On Owner &amp; Time (gannt Chart)g


    Hi Kiwifinny,


    Code
    Sub AddColors()
    Dim c As Range
    Dim iCount As Integer
    iCount = WorksheetFunction.CountA(Range("A4:A1200")) + 3
    For i = 4 To iCount
        For Each c In Range("C" & i, Range("IV" & i).End(xlToLeft))
            If c.Value = "x" Then c.Interior.ColorIndex = Range("L" & i).Value
        Next c
    Next i
End Sub


    Should do it for you.


    Bill


    EDIT: I have placed the ColorIndex numbers for each color in the rows in Column L, you can have them wherever you like, just change the code where it says "L" & i.

  • Vba To Sum With Condition

    Re: Vba To Sum Total Figures 600 Or Over


    Hi Mikeburg,


    Code
    Sub Sumit()
    Dim lng1099sTotal As Long
    lng1099sTotal = WorksheetFunction.SumIf(Range("F7:H70"), ">600", Range("F7:H70"))
    Sheet1.Range("A1").Value = lng1099sTotal
    
End Sub


    should do it.


    Bill

  • Workbook Password Protect 2 Levels

    Re: Workbook Password Protect 2 Levels With 1 Pwrd


    Hi Lionel,


    I know you say you don't want to use the Save As, but if you use Save As and tick the Read Only Recommended checkbox, then you only have to put in one password, if you want access to it, if not just click the Read Only button.


    Bill

  • Find The Last Date Of The Week

    Re: Find The Last Date Of The Week


    Hi Reafidy,



    This is totally different from the last post which asked for the number to be added to the spreadsheet if the latest week ending date was missing.


    If you add a label to your form (Label1 in my code, probably different in yours), with the Visible property set to False, then add the following code to TextBox1 (may be different in yours) Before_Update event.



    Hopefully this is what you are looking for.


    Bill

  • Auto Insert Invoice Number

    Re: Auto Insert Invoice Number


    Hi Revinrod,


    The easiest way to do it would just be to put 2000 as your starting number in cell D3, then put a line of code in the macro that runs when you click the button to enter the Invoice into the database:


    Code
    Range("D3").Value = Range("D3").Value + 1


    It depends on whether the command button to enter the invoice is from the Forms toolbar or the Control Toolbox on how you add this. It would be much easier if you could post the workbook.


    First of all, copy the above code.


    If the button is from the Forms toolbar, you will be able to right click on it, select Assign Macro and just before the End Sub line, paste the code.


    If the button is from the Control Toolbox, select View|Toolbars|ControlToolbox from the Main Menu, click the Design Icon (the green triange), then double click the command button that enters the Invoice. This will take you to the code for the button. Just before the End Sub, paste in the code. Close the Code window and click the Design Icon again to turn it off. The code should now run.


    Bill

  • Find The Last Date Of The Week

    Re: Find The Last Date Of The Week


    Hi Reafidy,


    The following code will add the next week ending date after the last entry in column A, if the last entry is 7 days before today's date. I don't know if this is what you mean.


    Code
    Private Sub UserForm_Initialize()
    Dim dNum As Double
    Dim dLatest As Double
    dNum = Date
    dLatest = Sheet1.Range("A65536").End(xlUp).Value
    If dLatest <= dNum - 7 Then
        Sheet1.Range("A65536").End(xlUp).Offset(1, 0).Value = dLatest + 7
    End If
    
End Sub


    Bill

  • Find The Last Date Of The Week

    Re: Find The Last Date Of The Week


    Hi Reafidy,


    What is in a TextBox, the date or the End or Week date, or both? What kind of textbox is it, from the Control Toolbox, Forms Toolbar or Drawing Toolbar?



    Bill