Posts by rabsofty

  • Color last word in a range of cells

    Try this:


    Code
    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
    eg:



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

  • listbox property error with range list mixed with add item

    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.


    Instructions:
    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!



    Now
    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.



  • Error adding data from User Form

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


    Code
    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.

    Code
    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
Else
  Cells(emptyRow, 9).Value = Val(CInt(Me.TextBox8.Value)) + Val(CInt(Me.TextBox9.Value)) + Val(CInt(Me.TextBox10.Value))
End If
endif

  • Save and Restore Spreadheet during Development session

    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.

  • Date / Time stamp on Save

    there is an error in your if statement.


    try this:


    Code
    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
  Else
    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


    Code
    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