Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007

    Unanswered: Force user action when populating cell

    Hi all

    I am sure there is someone out there who knows how to do what I am hoping to achieve.

    I am creating a spreadsheet for the staff to enter amounts into each month. I have attached spreadsheet file for you to see.

    I would like help to achieve the following:

    When a user enters an amount in a cell in the columns with the months above it (i.e. Rows 7 onwards and columns C, F & I etc), I want to force the user to select either “High”, “Med”, “Low” or “Firm” from the cell to the right of the cell they are populating, before they can continue.

    For the user to select the High, Low, Med & Firm drop down cells I used the Data Validation List function.

    I have set the grey cells with a formula that recognises the selected text and uses it to calculate a value from the user entered figure. Here is the formula I have used:

    =IF(E7="High",D7*75%)+IF(E7="Med",D7*50%)+IF(E7="L ow",D7*25%)+IF(E7="Firm",D7*100%)

    I hope someone is able to help with this.

    Kind regards
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2003
    Howdy, and welcome to the board.

    One suggestion, not directly related to your request, but to design to help you with your request. Normally (in western languages), people move from right to left, not the other way. It seems more logical to have the required cell prior to what you desire. Thus, change columns C and D and other pairs of columns).

    Also, it seems that VBA code in a private module (worksheet event) would be the best approach.
    old, slow, and confused
    but at least I'm inconsistent!

    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Dec 2007
    Hello Shades and thank you for your kind welcome.

    I move from left to right in my language too, but I am working to some very specific requests here.

    Any idea on how I might be able to acheive this using VBA code in a private module?

  4. #4
    Join Date
    Dec 2007
    put the following code inside sheet1

    Dim a As String
    Private Sub Worksheet_Change(ByVal Target As Range)

    If IsEmpty(Cells(Target.Row, "C")) Then
    Exit Sub
    Range("D" & Target.Row).Select
    If Cells(Target.Row, "D") = "Select" Then
    a = Target.Row
    End If
    End If
    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not a = "" Then
    If Cells(a, "D") = "Select" Then
    MsgBox ("please select")
    Range("D" & a).Select
    End If
    End If
    End Sub

    I think it would help u

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts