Inputbox problem.

[gjsala]

I have the following code and I keep getting a type mismatch error. Any help would be appreciated.

Sub inputA()
Dim ans As String, inp As String, quest As String, Answer As Integer
'Selecting the cell to change.
ans = Application.InputBox("Type the cell you wish to change. For example: C23.", Answer)
If ans = False Then
MsgBox ("Please type in a cell to change!&quot, vbCritical, "Warning"
Call inputA
End If
If ans = "" Then
MsgBox ("Please type in a cell to change!&quot, vbCritical, "Warning"
Call inputA
End If
inp = InputBox("Type in the new number.", Answer)
If inp = "" Then
MsgBox ("Please type in a number!&quot, vbCritical, "Warning"
Call inputA
End If
range(ans).Value = inp

'Asking to change more cells.
quest = MsgBox("Do you have more samples or markers to change?", vbYesNo, "Next step&quot
If quest = vbYes Then
Call inputA
End If
If quest = vbNo Then
Call Calculate
End If

End Sub

 

[SkipVought]

Hi,

ans cannot be a String -- must be Variant.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[gjsala]

SkipVought,
I dim "ans" as a variant and the error now is "Object variable or With block variable not set" Is there something else that's not dim correctly?

Thanks.

 

[SkipVought]

On what statement?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

This kind of dialog is VERY TEDIOUS! It is NOT condusive to a GUI approch. It isolates the user from the data. There are other ways of leading the user to the places where data is needed that let the user interact with the worksheet.

You ought to rethink this approch!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[gjsala]

This one section of the bigger code I'm having a problem with. The line that's giving me the problem is:
range(ans).Value = inp

Thanks for looking into this.


If you were to change this, what would you write?

 

[SkipVought]

You need to have some data validation to assure that and is a valid Cell reference.

This is STILL a bad idea!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[gjsala]

Do I need to create a variable for the valid cell reference?

 

[SkipVought]

ans IS your variable. You need to validate ans to be certain that it is a valid cell reference.

ie
"A" is NOT a valid cell reference
"12A" is NOT a valid cell reference

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[gjsala]

Skip,
Since my method doesn't reference a valid cell, what could I write instead of: range(ans).Value = inp or what should I add?

Thanks.

 

[SkipVought]

On Error Resume Next
...
Range(ans).Value = inp
If Err.Number <> 0 Then
  'tell the user to enter a valid cell reference
End IF

What if the user wants to bail out?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[gjsala]

What could I write to give the user a way out?

 

[glenntb]

Gjsala,

You might try something like this:

Sub LetUserSelectARange()
Dim Message, Title As String
Dim rng As Range

Message = "Please click on the cell to change!"
Title = "Click a cell"
On Error GoTo UserCancelled
Set rng = Application.InputBox(Message, Title, Type:=8)
On Error GoTo 0

Exit Sub

UserCancelled:
' The user cancelled so do appropriate stuff here.
Resume Next

End Sub

Hope this helps!

Glenn

 

[gjsala]

glenntb,
Thanks for the code but it only allows the user to select a cell. I'm looking for the user to be able to select the cell with the first inputbox, and with another input box the user can enter the right data.

Thanks

 

[glenntb]

That's easy enough to add. In between these two lines of my original post:
On Error GoTo 0

Exit Sub

insert this line:
rng.Value = InputBox("Please enter the correct data for this cell.&quot

and it should do what you wanted!

Glenn