Posts by ShosMeister

  • Macro - hide row if cells are empty

    Re: Macro - hide row if cells are empty


    Actually you don't want that entire range nor any of them for that matter. The reason is that you are checking for those cells to be empty. Not being able to see your workbook, I don't know if there are formulas in those cells or what.


    The only explanation I could offer would be if your row is basically a record and to be "complete" it needs data entered into 5 different columns, then the last column of data that needs to be entered would be my trigger.

  • Macro - hide row if cells are empty

    Re: Macro - hide row if cells are empty


    First off, welcome!


    Second, when you compose a message, there are various ICONs along the top to insert QUOTE, CODE, etc. tags. Just click on the button and paste your actual VBA code in between.


    Finally, this is rather simple. When you are in the VB Editor, where is the current code? In a module? Attached to the workbook? Worksheet? You can tell by double-clicking on the left pane and seeing which it's under (that's the simplest way).


    There is a specific code you can use to monitor and trigger every time something on the worksheet changes:

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 5 Then
    ' DO SOMETHING HERE
  End If
End Sub


    Note there is a "Target" parameter. This will be the cell on the worksheet that actually changed. Typically, one would limit the cells the want to trigger on so that the code doesn't run all of the time. In the example I gave above, the code would only run if the target cell was in column 5. Yours could be anything or could be a specific row or even one cell.

  • Print Multiple listbox Selections as one Print Job

    Re: Print Multiple listbox Selections as one Print Job


    There was an exact post about this just a few months ago. Was that yours and is that solution not working or something different?


    If the worksheets are all formatted the same (page setup parameters, basic layout, margins, etc.) then during the loop, simply select the worksheet rather than printing. Then, when completed, print the selected worksheets. That would result in a single print job given the above condition.

  • Vlookup / networkdays required

    Re: Help with Interesting vlookup / networkdays required - please. :)


    You're right; a sample sheet is going to be a must here. As always, remove/change any proprietary data and provide a separate sheet with what the expected outcome should be.

  • Sort with merged cells

    Re: Sort with merged cells


    Attach a sample copy of your worksheet and not just a screen shot and I'll take a look. As Cytop indicated, one of the least known alignment options is the "Center Across Selection". Works great in combination with cell borders for giving the appearance of merged cells - that's what we used before merge existed.

  • Sort with merged cells

    Re: Sort with merged cells


    Get rid of the merged cells. Your layout is probably just for visual purposes. you can do the same thing quite simply without using merged cells. There is no way that I know of to sort around merged cells.

  • Average of curves

    Re: Average of curves


    Your trendline is an exponential trendline. According to MS:

    Quote


    Exponential Applies a curved line to display data values that rise or fall at increasingly higher rates. For an exponential trendline, your data should not contain zero or negative values.
    This type of trendline uses the following equation to calculate the least squares fit through points: y = ce^bx


    where c and b are constants, and e is the base of the natural logarithm.


    Given that, did you look at the statistical functions built into Excel; LINEST, LOGEST, GROWTH, etc? One of these or one of the other functions should allow you to do this without any VBA at all.

  • The values of the msg box prints in the cell.

    Re: The values of the msg box prints in the cell.


    Just as a cursory look - have not written nor fully tested - you already have what you need.


    The code you use to copy out the selections into msg for display, could also be used to populate the cells on the worksheet.

    Code
    With ListBox1
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            msg = msg & .List(i) & vbNewLine
        End If
    Next i
End With


    Based on what you defined as your requirement, there will be a selected cell prior to clicking the button. So, if all is well and the user selects OKAY from the confirmation dialogue box, you should be able to do something like this (again, not tested. just written here)

    Code
    With ListBox1
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            ActiveCell=.List(i)
            Range.Offset(1, 0).Select
        End If
    Next i
End With

  • listbox / multi selection filter

    Re: listbox / multi selection filter


    Code
    rngVendors.AutoFilter Field:=rngVendors.Column, Criteria1:=Me.lbx_Vendors.Text


    Your error is in the setting of the AutoFilter. The FIELD parameter should be the field within the active filters. Since you only have 1 (the rest of the columns have no filter applied), the Field value should be 1 and not 9 (which is the column of rngVendors).


    You can either apply a filter to the entire data set and leave the Field as is or change it to a 1 and you should be good.