Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / September 2007

Tip: Looking for answers? Try searching our database.

I need to suggest a value

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Roberto Carabajal - 23 Sep 2007 17:02 GMT
Hello:
Please, I would appreciate help with this problem. I want Excel to suggest
the value of the previous row, each time the current cell is empty, but with
no need to define this with the classic native method (select, drag....).
Something like conditional format  that says:
each time cell (A x) is empty --> suggest value of cell (A x-1)

If A6 is empty, Excel should suggest A5 value

I need this behaviour only for B column.

Thanks very much for your attention. My best regards.
Roberto
Rick Rothstein (MVP - VB) - 23 Sep 2007 17:38 GMT
I'm not entirely sure what you mean by "suggest". The following code...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Not Intersect(Target, Range("B:B")) Is Nothing Then
   If Application.Selection.Count = 1 Then
     If Target.Row > 1 And Target = "" Then
       Target.Value = Target.Offset(-1, 0)
     End If
   End If
 End If
End Sub

will place the text from the cell above in the current Column B cell and
leave focus on cell so you can just type in a new value, but there will be
no visible indication (other then the selection indicator) to let you know
the cell contents are copied. If this were a TextBox, I would select all the
text in the TextBox so you would know the entry is pending "approval", but I
don't know how to do this in a cell... and even if I did know how, it would
probably be dependent on the

    Tools/Options/Edit/Edit directly in cell

option being checked.

Target.Row > 1 And Target = ""

Rick

> Hello:
> Please, I would appreciate help with this problem. I want Excel to suggest
[quoted text clipped - 10 lines]
> Thanks very much for your attention. My best regards.
> Roberto
Roberto Carabajal - 23 Sep 2007 19:06 GMT
Thanks Rick.!!

This Worksheet_SelectionChange will work like a standard Visual Basic event ?
Can you give me a link to see all similar events listed ?
Rick Rothstein (MVP - VB) - 23 Sep 2007 19:47 GMT
> This Worksheet_SelectionChange will work like a standard
> Visual Basic event? Can you give me a link to see all similar
> events listed ?

SelectionChange is a standard event for the Worksheet object. You can see
all of the Worksheet events by going into the VBA editor, clicking on any
Sheet object shown in the Project Explorer window, then selecting Worksheet
from the combo box on the left side of the Code window and, finally, looking
at all the available events in the combo box on the right side of the Code
window.

Rick
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.