Cell that allows both formula and manual input options

**Rustico18** · 03-07-2017, 07:47 PM

Hello,

In the attached file, I have a drop-down menu (cell A3) that allows users to determine whether they want to manually input a number in a specific cell (cell F3) or have the worksheet input a placeholder value for them in that cell. The problem is that anytime a user manually inputs a number in F3, it overwrites the formula that pulls in the placeholder value from the second tab (cell B2). Thus, if the user switches back to "placeholder value" option, cell F3 no longer works properly.

I know I can work around this by creating additional rows and using if/then statement, but really hoping to maintain the format of having just one cell for both the input and placeholder value. I believe this requires VBA. Can anyone help me accomplish this? *Thanks!

**spitfireblue** · 03-07-2017, 07:59 PM

Hi,

You can do it using a macro like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Address = "$A$3" Then Exit Sub
    If Target = "Use Placeholder" Then
        Range("D3").Formula = "='Literature-Based Inputs'!B2"
        Range("C3") = "Placeholder: "
    ElseIf Target = "Input Actual" Then
        Range("D3").ClearContents
        Range("C3") = "Input: "
    End If
End Sub

**Rustico18** · 03-07-2017, 09:30 PM

Thank you Stephen,

That definitely worked. However, on my actual spreadsheet i have 4 different drop-downs that i need to do the same thing for, yet VBA won't let me duplicate this code 4 times in one worksheet.
For example, for 1 of the 4 drop-downs might be an input, while the other 3 pull from the 2nd tab.
Is there a way to do this? See attached file and thanks again!

**spitfireblue** · 03-07-2017, 11:10 PM

You could do it several ways.
Maybe like this?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target = "Use Placeholder" Then
        Target.Offset(0, 2) = "Placeholder: "
        Target.Offset(0, 3) = Sheets(2).Range(Target.Address).Offset(-1, 1)
    ElseIf Target = "Input Actual" Then
        Target.Offset(0, 2) = "Input: "
        Target.Offset(0, 3).ClearContents
    End If
End Sub

**Rustico18** · 03-08-2017, 01:53 AM

Works perfectly. Much appreciated!

Cell that allows both formula and manual input options

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Cell that allows both formula and manual input options

Re: Cell that allows both formula and manual input options

Re: Cell that allows both formula and manual input options

Re: Cell that allows both formula and manual input options

Re: Cell that allows both formula and manual input options

Thread Information

Users Browsing this Thread

Similar Threads

Option to put in manual input without erasing the formula

Option to put in manual input without erasing the formula.

[SOLVED] Cell equals formula OR manual input

[SOLVED] Adding manual input into a cell that has a formula installed without losing the formula

[SOLVED] Formula and Manual Input

Run Macro on cell change from streaming data (not manual user input)

Having a mcro prompt me for manual input in cell

Bookmarks

Bookmarks

Posting Permissions