| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I make the formula see the message box answer?
I have a pop up message box as shown below
Dim Res As String Res = InputBox("What is the cell address?") If StrPtr(Res) = 0 Then MsgBox "User Clicked Cancel" ElseIf Res = vbNullString Then MsgBox "User Clicked OK with no input" Else MsgBox "User Entered cell " & Res End If ' Now I need the answer to that reqeust to be in this formula where 0 is the cell address that I have just entered. (This is a simple formual, but I need to see if I can get it to work before I use it in a more complicated formula) ActiveCell.FormulaR1C1 = "=R[-1]C[2] + 0 " Range("D7").Select |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I make the formula see the message box answer?
ActiveCell.FormulaR1C1 _
= "=R[-1]C[2] + " & range(res).address(referencestyle:=xlr1c1) But I'd drop the inputbox and use application.inputbox. Dim Rng as range '... Set rng = nothing on error resume next set rng = application.inputbox(Prompt:="Select a cell",type:=8).cells(1) on error goto 0 if rng is nothing then 'user hit cancel 'what should happen? else ActiveCell.FormulaR1C1 _ = "=R[-1]C[2] + " & rng.address(referencestyle:=xlr1c1) end if Melody4572 wrote: I have a pop up message box as shown below Dim Res As String Res = InputBox("What is the cell address?") If StrPtr(Res) = 0 Then MsgBox "User Clicked Cancel" ElseIf Res = vbNullString Then MsgBox "User Clicked OK with no input" Else MsgBox "User Entered cell " & Res End If ' Now I need the answer to that reqeust to be in this formula where 0 is the cell address that I have just entered. (This is a simple formual, but I need to see if I can get it to work before I use it in a more complicated formula) ActiveCell.FormulaR1C1 = "=R[-1]C[2] + 0 " Range("D7").Select -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I make the formula see the message box answer?
Sub bBB()
res = InputBox("What is the cell address?") If StrPtr(res) = 0 Then MsgBox "User Clicked Cancel" Exit Sub ElseIf res = vbNullString Then MsgBox "User Clicked OK with no input" Exit Sub Else MsgBox "User Entered cell " & res End If ActiveCell.FormulaR1C1 = "=R[-1]C[2] +" & res If ActiveCell.Text = "#NAME?" Then res = Application.ConvertFormula(res, xlA1, xlR1C1) ActiveCell.FormulaR1C1 = "=R[-1]C[2] +" & res End If End Sub -- Regards, Tom Ogilvy "Melody4572" wrote in message ... I have a pop up message box as shown below Dim Res As String Res = InputBox("What is the cell address?") If StrPtr(Res) = 0 Then MsgBox "User Clicked Cancel" ElseIf Res = vbNullString Then MsgBox "User Clicked OK with no input" Else MsgBox "User Entered cell " & Res End If ' Now I need the answer to that reqeust to be in this formula where 0 is the cell address that I have just entered. (This is a simple formual, but I need to see if I can get it to work before I use it in a more complicated formula) ActiveCell.FormulaR1C1 = "=R[-1]C[2] + 0 " Range("D7").Select |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I make the formula see the message box answer?
Perfect! Thanks Tom!
"Tom Ogilvy" wrote: Sub bBB() res = InputBox("What is the cell address?") If StrPtr(res) = 0 Then MsgBox "User Clicked Cancel" Exit Sub ElseIf res = vbNullString Then MsgBox "User Clicked OK with no input" Exit Sub Else MsgBox "User Entered cell " & res End If ActiveCell.FormulaR1C1 = "=R[-1]C[2] +" & res If ActiveCell.Text = "#NAME?" Then res = Application.ConvertFormula(res, xlA1, xlR1C1) ActiveCell.FormulaR1C1 = "=R[-1]C[2] +" & res End If End Sub -- Regards, Tom Ogilvy "Melody4572" wrote in message ... I have a pop up message box as shown below Dim Res As String Res = InputBox("What is the cell address?") If StrPtr(Res) = 0 Then MsgBox "User Clicked Cancel" ElseIf Res = vbNullString Then MsgBox "User Clicked OK with no input" Else MsgBox "User Entered cell " & Res End If ' Now I need the answer to that reqeust to be in this formula where 0 is the cell address that I have just entered. (This is a simple formual, but I need to see if I can get it to work before I use it in a more complicated formula) ActiveCell.FormulaR1C1 = "=R[-1]C[2] + 0 " Range("D7").Select |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I make the formula see the message box answer?
Thank you to you also Dave. This one is somehting I can use in another
workbook. "Melody4572" wrote: Perfect! Thanks Tom! "Tom Ogilvy" wrote: Sub bBB() res = InputBox("What is the cell address?") If StrPtr(res) = 0 Then MsgBox "User Clicked Cancel" Exit Sub ElseIf res = vbNullString Then MsgBox "User Clicked OK with no input" Exit Sub Else MsgBox "User Entered cell " & res End If ActiveCell.FormulaR1C1 = "=R[-1]C[2] +" & res If ActiveCell.Text = "#NAME?" Then res = Application.ConvertFormula(res, xlA1, xlR1C1) ActiveCell.FormulaR1C1 = "=R[-1]C[2] +" & res End If End Sub -- Regards, Tom Ogilvy "Melody4572" wrote in message ... I have a pop up message box as shown below Dim Res As String Res = InputBox("What is the cell address?") If StrPtr(Res) = 0 Then MsgBox "User Clicked Cancel" ElseIf Res = vbNullString Then MsgBox "User Clicked OK with no input" Else MsgBox "User Entered cell " & Res End If ' Now I need the answer to that reqeust to be in this formula where 0 is the cell address that I have just entered. (This is a simple formual, but I need to see if I can get it to work before I use it in a more complicated formula) ActiveCell.FormulaR1C1 = "=R[-1]C[2] + 0 " Range("D7").Select |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto answer message box | Excel Discussion (Misc queries) | |||
| Answer an Outlook Express message box | Excel Worksheet Functions | |||
| How can I repeat a macro, and how can make it without any message? | Excel Programming | |||
| how do i add 2 columns to make the answer apper on the 3rd column | Excel Worksheet Functions | |||
| Code to make a New line in a message | Excel Programming | |||
Linear Mode
