Posts by pmgibs

  • VBA code to check for empty text box, if text box is empty stop

    I am looking for a code that will generate a message box if there are values in either TextBox2 or TextBox3 but nothing in TextBox1. If this happens I would also like it to stop the sub.


    This is my current code that will generate a message box but only if there is a value in TextBox3 and nothing in TextBox1. It doesn't seem to check TextBox2. Further, once I click Ok on the message box it will forward the value in TextBox3 to the worksheet, which I don't want.


    Code
    Private Sub SubmitCommandButtom_Click()
'Error Message if Unit Number is not Typed in
    If Len(TextBox1) = "" And Len(TextBox2) > 0 Or Len(TextBox3) > 0 Then
        MsgBox "Unit Number must be entered to continue!"
    End If
'Inserting original unit values into workbook
    Sheets("Uneven Split Job Aid").Range("A2") = TextBox1.Value
    Sheets("Uneven Split Job Aid").Range("B2") = TextBox2.Value
    Sheets("Uneven Split Job Aid").Range("C2") = TextBox3.Value


    Thanks for any help!

  • Return Column Header of Last Occurence of Specified Value

    I have various values that repeat in columns BHS:BWT, I would like a formula that identifies the last column a specific value (this value is in BWU) occurs in a row and return the column header to column BWW.


    For example: in row 2, BWU=4, "4" is in columns BHS:BIM, the column header of BIM is 290, I would like 290 be returned as the value in BWW.

  • MIN Function Returning Zero

    Nevermind I figured out my problem the formula in BHS:BWT was returning a "1","2",... Once I changed these to 1,2,3 your formula and all other formulas I was trying worked. Stupid mistake between number and string.


    Thanks for you help though!

  • MIN Function Returning Zero

    I am using a MIN Function, but it continues to return "0" for all rows due to blank cells.


    These are the formulas I have tried so far:


    =MIN(BHS2:BWT2)


    =MIN(IF(ISNUMBER(BHS2:BWT2),IF(BHS2:BWT2>0,BHS2:BWT2))) Ctrl+Shift+Enter


    =MIN(IF(BSH2:BWT2 <>0,BHS2:BWT2)) + Ctrl + Shift + Enter


    All of these formula continue to return a "0".


    The numbers in the data set run from 1-17 with no 0, but many blank cells.