Posts by Ted

Thanks v.much.

Hi Ralph,

How do add a user form?

Please can someone tell me how to place a control into a Frame control properly. I've tried and tried but as soon as I switch off design-view the controls are 'behind' the Frame.

Help...!!?

Ah, okay so in the above example if I stick an extra line of code in like:

Range("A1").Activate

Then the rest of it, it should work..? I'll give it a go, and I'll also try passing all of the parametres to the Find method to see if that helps, ie. LookAt, SearchOrder, etc.

Thanks for your help, hope it works.

Ted

I don't think so - not entirely sure though, I'll have to try it tomorrow. Do you think this could be the problem?

I populate a combo-box with a list of dates on one of my worksheets, but - even though the cells on the worksheet are formatted dd/mm/yy the combo-box shows mm/dd/yy. Anyone know how to fix this?

A friend suggested changing the regional settings in the control panel, but at work we don't have access to the control panel. If this is the solution, is there a way to change the settings programmatically?

Ted

For some reason I can't use the following code in my application at work, when it's fine at home. (Older version of Excel and Windows at work)

Range("A1:A100").Find(What:="Test").EntireRow.Select

Anyone know why? The error I get is "Unable to use the Find property of the Range class".

Great, thanks.

What's the easiest way, VBA or otherwies, to print part of a worksheet when some cells are frozen.

What I mean is, from row 5 upwards is frozen. I want to print part of the worksheet way down at around row 300, but I want rows 1-5 to be included on the printed page.

Can anyone help?

Thanks in advance.
Ted

Great, - think I'll go with the EDATE solution, but they all work!

Thanks all.

I need a formula to work out an expiry date from today, using a given number of years and months.

For example, today is 15th July 2003, using 5 years and 5 months I would want December 2009 to be returned. Using 0 years and 1 month I would want August 2003 to be returned.

At the moment I am using something like:

=TODAY+(Years*365)+(Months*30)

It works fine - but it's not 100% accurate as there aren't 30 days in each month, and sometimes there are 366 days in a year. Does anyone know a better way to do this.

Example file attached to help.

Thanks for looking.

Ted

Thanks Dave, exactly what I needed.

Thanks Dennis, I've played around with it a bit - but sometimes no data gets entered in a month, and in that case the above wouldn't really work because it picks up the last 12 entries. ie: I need it to move along a column month by month even if no data is entered.

Hello everyone.

Please can someone help me figure out how to get a 12 month average by using a formula or VBA.

I have columns Jan, Feb, Mar, etc, starting from say column E, and I need column D to sum 12 months from whatever month we're in.

Example file attached if I'm not making any sense. Thanks for helping. :))

Ted

This might help for part of what you want:

Dim myGoodUserName As String


Private Sub CommandButton1_Click()
    RemoveUnwantedCharacters Cells(1, 1).Value
    MsgBox myGoodUserName
End Sub


Private Sub RemoveUnwantedCharacters(ByVal strWhat As String)
    myGoodUserName = ""
    Dim i As Integer
    
    For i = 1 To Len(strWhat)
        If Mid$(LCase$(strWhat), i, 1) Like "[a-z]" Then
            myGoodUserName = myGoodUserName & Mid$(LCase$(strWhat), i, 1)
        End If
    Next i


End Sub

Can you be a bit more specific, or post an example please?

Are you sure you want the whole row deleted, or just the cell(s) with a value of zero - and the remaining cells 'shifted' up?

Regards
Ted

Are you saying that if your targets (sales targets I presume?) fall short in a month, you want the difference distributed amongst the remaining months?

Example: January's target is to sell 20 items, but you only sell 18. February's target was to sell 30 - but because January fell short by 2, it should be increased to 32. (If you want the shortfall to be spread amongst all of the remaining months, do you want it to be even? Also - can we go into decimal, ie 20.5, 25.25, etc?)

Ted

Thanks.

Don't worry about the save thing.

Ted

I have one workbook that gets all of it's info from several other ones (completely seperate workbooks - some may be open, some may be in the middle of being updated, and some may be closed).

Is there anyway to get the workbook with all of the combined info to update as soon as one of the others are saved?

Also, is there anyway to get rid of the save prompt when the workbook is closed?

Thanks in advance... :beergrin:

Ted

Hi there.

One way to do this is to put the times you want displayed into a range of cells, say from A1 to A48, and then set the Combo Box property ListFillRange to A1:A48.

Hope this helps.

Regards
Ted