Posts by rabsofty

    Try this:

    pos = InStr(1, Cells(1, 2), "/", vbTextCompare)
    Cells(1, 2).Characters(Start:=pos + 2).Font.Color = -16776961

    the instr finds the position of the / character
    the next line uses the pos # + 2 as its start to the end of line

    note: if you wish only parts of it you can add the length to the characters command

    Cells(1, 2).Characters(Start:= a number, Length=: a number).Font.Color = -16776961

    I originally had code to list some commands (sub lst_dbcmd1)
    I called sub lst_dbcmd1 and lst_dbcmd3 after it. (and it works)

    I decided to change sub lst_dbcmd1 to range list instead of a for loop (range list sub = lst_dbcmd2)

    When I call lst_dbcmd2 (has range list) then call lst_dbcmd3 it now fails (could not set list property value)

    I have attached a workbook with my problem.

    Click the Mod1 button,
    Then type cmd1 in the textbox (click OK) - list shown
    Then type cmd3 in the textbox (click OK) - list shown

    both will work!

    Then type cmd2 in the textbox - list shown
    Then type cmd3 in the textbox - list fails

    cmd3 fails with listbox property error.

    the only change made was cmd1 loads the listbox with a for loop list
    cmd2 loads the listbox as a range list

    For some reason when you load the listbox with a range list then rebuild it with a 2d array it fails???
    If you load it with a for loop then rebuild with a 2d array it works.

    use the Val command, it returns zero automatically when the textbox=""

    Cells(emptyRow, 9).Value = Val(CInt(Me.TextBox8.Value)) + Val(CInt(Me.TextBox9.Value)) + Val(CInt(Me.TextBox10.Value))

    Note: Val also returns Zero if the textbox contains a character.

    your code should test if user entered characters instead of numbers before you perform the addition.

    If Me.TextBox8.Value = "" Then Me.TextBox8.Value = 0
    If Me.TextBox9.Value = "" Then Me.TextBox9.Value = 0
    If Me.TextBox10.Value = "" Then Me.TextBox10.Value = 0
    If Not IsNumeric(Me.TextBox8.Value) Or Not IsNumeric(Me.TextBox9.Value) Or Not IsNumeric(Me.TextBox10.Value) Then
      'report error here
      Cells(emptyRow, 9).Value = Val(CInt(Me.TextBox8.Value)) + Val(CInt(Me.TextBox9.Value)) + Val(CInt(Me.TextBox10.Value))
    End If

    There are a lot of different ways to do this, dependant on how many spreadsheets you have.
    1. save workbook b4 a), make changes, test - if errors then revert to the saved workbook, (reenter your macro code)

    2. export your sheets, make changes,test - if errors then reimport exported sheets

    3. create routine to save data to files, create routine to format sheets to what you require,
    make changes to code - if errors, run macro to reload data and reformat sheets.

    Personally, I use #1 all the time. (doing it this way for 15yrs)
    My spreadsheet loads all data and saves all data from/to files. My loader routine always reformats the data.

    I have production(Prod) and development(Dev) folders.
    the production folders contain current working data and workbook.

    when I want to work on the workbook, I copy the Prod folder data and workbook to Dev
    I then make changes to the code in (Dev) (if the changes are large I open an notepad file and paste the changes to it)
    I test the code, if it fails and I have to restore the data, I simply copy the Prod data to Dev and reload it.
    if the changes are to great and I want to start over, I copy the Prod workbook and data back to Dev and start over
    (if I put code into notepad, I can simply paste the code I want back to the Dev workbook)

    By doing this production data and workbook remain always remain functional.
    As I said this is how I would do it. My financial work is extensive so it works best for me to do it this way.

    there is an error in your if statement.

    try this:

    If Weekday(Date) = 1 Then
        ActiveWorkbook.SaveAs Filename:= _
            "C:\Users\bill_\Desktop\DATE TEST - " & Format(Now() - 7, "mm.dd.yyyy") & ".xlsx", FileFormat:=51, CreateBackup:=False
        ActiveWorkbook.SaveAs Filename:= _
            "C:\Users\bill_\Desktop\DATE TEST - " & Format(Now() - 1, "mm.dd.yyyy") & ".xlsx", FileFormat:=51, CreateBackup:=False
      End If

    or to save a little code, try

    sd = (Weekday(Date) <> 1) * 6 + 7
      ActiveWorkbook.SaveAs Filename:="C:\Users\bill_\Desktop\DATE TEST - " & Format(Now() - sd, "mm.dd.yyyy") & ".xlsx", FileFormat:=51, CreateBackup:=False