Posts by goneps

This item cross-posted in Excel Guru forum (no response there after 2 weeks).

Excel 2003, Win7 Home SP1

I always edit cells via the formula bar, not directly in the cell. For some reason the appearance of the cursor has suddenly changed from a thin vertical line to a much thicker one, which is unhelpful and distracting.

When the formula bar is first opened for entry or editing the cursor appears as the normal thin line, and remains thus if the cursor is moved either by mouse or arrow keys:

But as soon as any other key is pressed to begin entry or editing it changes to the thicker line:

As you can see, it's similar to when the Insert keyboard function is selected (but that's not the case). This is occurring in all but one of my workbooks, including one that I'd not opened for several months.

Can anyone please tell me how to remedy this annoying behaviour? And preferably how it might have occurred in the first place.

Many thanks,

Richard

Re: Force UDF to recalculate?

Many thanks, Derk. The UDF formula didn't refer to the changed cell, so your hint put me right. Easily corrected by adding "if(M350>0,...". A classic instance of being unable to see the wood for the trees. Your help much appreciated.

R.

Each week I read the electricity meter and enter the reading on a spreadsheet. This is done by invoking a macro with adds a row and copies down the formulae in each column, then entering the new reading. However, the last column contains a UDF formula which totals the Cost column (but only if it's 4 weeks since the last bill), which refuses to recalculate when the new reading is entered. Neither does it respond to F9.

How can I force the cell with the UDF formula to recalculate? All I've been able to come up with is the code below, and that doesn't work.

Many thanks,

Richard

Private Sub Worksheet_SelectionChange(ByVal target As Range)


  If Application.ActiveSheet.Name <> "Contact (2)" Then End


  If target.Column = 14 Then    ' column to right of where reading entered (cursor moves right on Enter)
    Cells(target.Row, 18).Calculate                                        ' cell containing UDF
  End If


End Sub

Re: Changed appearance of cursor when editing in formula bar

Roy,

NumLock is always on, but why would that affect it? I've just restarted Excel with it switched off, and there's no change.

I've a feeling this may have started when I had the VB Editor open, so perhaps you're on to something.

Richard

This item cross-posted in Excel Guru forum (no response there after 4 weeks).

I always edit cells via the formula bar, not directly in the cell. For some reason the appearance of the cursor has suddenly changed from a thin vertical line to a much thicker one, which is unhelpful and distracting.

When the formula bar is first opened for entry or editing the cursor appears as the normal thin line, and remains thus if the cursor is moved either by mouse or arrow keys:

[ATTACH=CONFIG]59188[/ATTACH]

But as soon as any other key is pressed to begin entry or editing it changes to the thicker line:

[ATTACH=CONFIG]59189[/ATTACH]

As you can see, it's similar to when the Insert keyboard function is selected (but that's not the case). This is occurring in all but one of my workbooks, including one that I'd not opened for several months.

Can anyone please tell me how to remedy this annoying behaviour? And preferably how it might have occurred in the first place.

Many thanks,

Richard

Re: Can a Worksheet_Change routine perform actions on a different worksheet?

Roy,

Thanks for your further suggestion, but it results in "Run-time error 1004: Select method of Range class failed". However, the good news is that calling a separate Sub does the trick, so problem solved. It looks as though a Worksheet_Change routine can't perform actions on other worksheets.

So, adding the line "Call Deselect" in the Worksheet_Change code of Worksheets("KWB") brings up:

Private Sub Deselect()


  With Worksheets("CSH")
    .Activate
    .Range("B3").End(xlDown)(2, 1).Select
  End With
  
  Worksheets("KWB").Activate


End Sub

".Activate" is necessary, because omitting it causes an error.

Yes, I am aware that numbered sheet references can be problematic if the sheets are moved. As you can see from the above, the sheets in question are actually named.

Thanks to all for suggestions offered—much appreciated.

Richard

Re: Can a Worksheet_Change routine perform actions on a different worksheet?

Thanks Roy. Unfortunately there's no way of selecting the range other than manually because it varies in size and location every time. It's manually copied from Worksheets(1), and the Worksheet_Change routine on Worksheets(2) is activated when the copied selection is pasted to Worksheets(2). It just seems a bit primitive to manually go back to Worksheets(1) and move the cursor to deselect the copied range.

Might try writing a separate sub to do that and calling it from the Worksheet_Change sub. Would that work, do you think?

Re: Can a Worksheet_Change routine perform actions on a different worksheet?

Thanks jaslake, but that does not deselect the selection (ie. revert to a single cell), which is what I'm trying to do.

I'm using a Private Sub Worksheet_Change(ByVal target As Range) macro on Worksheets(2) to perform certain actions when a selection from Worksheets(1) is pasted to Worksheets(2). Before ending the Worksheet_Change sub I'd like it to deselect the copied selection on Worksheets(1). I've tried to do this by selecting Worksheets(1) and moving the cursor, but VB ignores the "With Application.Worksheets(1)" instruction. Removing "Private" from the sub heading makes no difference.

Is it possible do this with a Worksheet_Change routine? If so, how?

Many thanks.

Re: Restrict UDF operation to a single Worksheet?

Thanks, Ger—appreciate your help. Just couldn't come up with the right syntax for the IF statement.

Richard

Sheet(2) of the workbook contains cells which call a UDF. Sheet(1) has cells which reference the UDF cell values in Sheet(2) via VLOOKUP.

Is it possible to restrict the UDF to operating only when Sheet(2) is the active sheet?

This has arisen while debugging Worksheet_Change code on the other Sheet(1). I'd prefer not to disable calculation while that routine runs, which might be one way of doing it.

Any suggestions gratefully received.

Re: Automating entries in column adjacent to pasted selection

Thanks apo—appreciate your help. R.

Re: Automating entries in column adjacent to pasted selection

Gosh, apologies—it does indeed work as you say. As a VB battler I got confused by the routine finding the last row, and indeed the entire used range. Sorry to have doubted you!

Not one to readily give up, in the meantime I tried other things, and eventually came up with this:

Private Sub Worksheet_Change(ByVal target As Range)


  If target.Column = 1 Or target.Column = 2 And Cells(target.Row, "B").Font.ColorIndex = 10 Then
    Dim c As Range


    For Each c In ActiveWindow.Selection
      If c.Column = 2 Then
        With Cells(c.Row, "E")
          .Value = "ep"
          .Font.ColorIndex = 10
        End With
      End If


      If c.Column = 3 Then c.ClearContents
    Next c
  End If


End Sub

As you can see, there are a couple of additional refinements to further automate the process. Having to invoke a For Each Cell procedure provided the opportunity to delete unwanted contents in col. C.

I've saved your code since it will surely provide ideas for the future, so many thanks again for your help.

R.

Re: Automating entries in column adjacent to pasted selection

Thanks for your suggestion, apo, but unfortunately it doesn't work. Perhaps I should have mentioned that the paste is not being made at the end of the worksheet—sometimes a long way from it.

To my inexpert way of thinking the obvious way to handle this is to count the rows in either the copied or pasted selection, and I'm baffled that it doesn't seem to work. Why should it be so difficult to identify the size of a highlighted range?

I appreciate your help—hope you didn't waste too much time on this.

R.

The following code inserts the text "ep" into col. F when an entry (formatted text colour green) is made in the corresponding row of col. B:

Private Sub Worksheet_Change(ByVal target As Range)


' Insert "ep" in Type column and set font colour


  If target.Column = 2 And target.Font.ColorIndex = 10 Then
    With Cells(target.Row, "E")
      .Value = "ep"
      .Font.ColorIndex = 10
    End With
  End If
End Sub

However, more often than not new entries are made by pasting from another sheet, and usually more than one row. How can the code be modified to count the number of rows in the pasted selection and place "ep" in each corresponding row of col. F (instead of just the top row)?

I've been trying Selection.Rows.Count but keep getting the dreaded error message, "Object doesn't support this property or method".

Many thanks,

Richard

Re: Recalculation issues with UDF

Rory,

Marvellous! Works like a charm. So simple, when you know how. Many thanks indeed.

Richard

Re: Recalculation issues with UDF

This query now cross-posted at http://www.excelguru.ca/forums…-with-UDF&p=6035#post6035 and also at http://www.excelforum.com/exce…tml?p=3060363#post3060363
(for some reason I can't edit previous posts to put this at the top of the thread).

Re: Recalculation issues with UDF

Thanks, that seemed to work, so zipped file attached.

I've combined two UDFs into one that serves both functions, depending upon the first argument passed by the formula. However, I've now run into recalculation problems. Two of the nine worksheets have formulas which call the function, but only the sheet that's active when the workbook is opened calculates correctly; the other has to be forced to recalculate with F9. If neither sheet is active when the workbook is opened then both are a mess.

Having searched for answers I've incorporated Application.Volatile in the function but it makes little difference. The other suggestion was to pass all ranges needed for calculation as arguments, but how on earth could that be done?

Here's the UDF:

Function Last(x As Integer, r As Range)


' Last(1,CellAddress) finds row for last fill
' Last(4,CellAddress) finds row for last 4 fills
' Use with INDEX function in formula


  Application.Volatile
  
  Dim i as integer, n As Integer
  
  If x = 1 Or x = 4 Then        ' Check for correct parameter passed
  
    i = r.Row
    n = 0
    
    Do
      i = i - 1
      If Range("B" & i).Value > 0 Then n = n + 1
    Loop While n < x
    
    Last = i
  
  Else: Exit Function
  End If


End Function

I'm attaching a slimmed-down sample of the workbook containing only the two sheets in question. Their layout is identical, with the exception of formulas in col. I. The obvious problem seems to lie with col. J ("Dist."), which calculates distance since the tank was last filled, but other calculations are also faulty. The two coloured rows at the foot of worksheet "Cefiro MPG" show fixed, correct values for the two similarly coloured rows with formulas, as a check for correct calculation.

Oh, great! Can't upload the file—keep getting a "500 IO error". At 152KB it's well below the limit. Any suggestions?

With grateful thanks for any help that may be forthcoming.

Richard

Re: User-defined function won't work

Thanks, Rory. Passing a cell reference in col. B (r as range) for LastFill, and the same plus the cell row (i as integer) for Last4 did the trick nicely. Your help's appreciated.

Perhaps I could impose upon you to solve another mystery. While fiddling about with this the functions appeared in the User-Defined Functions list (Insert > Function), but now they do not. I've tried using Private, Public, and plain Function, but still nothing. Any suggestions?

Richard