From inputbox to cell works but how to add an cell overwrite warning MsgBox

[borreltijd]

Hi,

I am quite new with VBA and I am working on a excel sheet to gather data from an experiment. I want to have an absolute monkey proof program. Therefore I use an InputBox to gather data and to write it into a specif cell. I also check if the data range is valid.


For example:

Sub GetData()
' This macro is a first attempt to generate monkey proof macro for entering expimental data into a excel sheet.
' First some variables are declared.

Dim MP1_Suction_Temperature As Single
Dim MinValueTemperature As Single
Dim MaxValueTemperature As Single
MinValueTemperature = 0
MaxValueTemperature = 250

' Input of MP1_Suction_Temperature
Msg = " Enter the suction temperature, ranges from " & MinValueTemperature & " up to " & MaxValueTemperature & " [C]"
Do
UserEntry = InputBox(Msg)
If UserEntry = "" Then Exit Sub
If IsNumeric(UserEntry) Then
DblEntry = Val(UserEntry)
If DblEntry >= MinValueTemperature And DblEntry <= MaxValueTemperature Then Exit Do
End If
Msg = "Your previous entry was INVALID."
Msg = Msg & vbNewLine
Msg = Msg & "Enter a value between " & MinValueTemperature & " and " & MaxValueTemperature
Loop
Worksheets("DATASHEET").Range("MP1_Suction_Temperature").Value = UserEntry
' End of input MP1_Suction_Temperature
End Sub

What currently is missing in this procedure is a way to give a warning when the cell (MP1.Suction_Temperature) already contains data.( So when the macro is run for the second time)

I want a MsgBox to pop with the warning that the cell already contains data and the question overwrite yes or no = cancel.

Yes means continue and overwrite, cancel should should down the macro and leave the current data intact.

I am thinking about the IsEmpty tag but I am not sure,

thanks in advance.

 

[Johnny C]

I think you just need to add

IF Worksheets("DATASHEET").Range("MP1_Suction_Temperature").Value <> 0 Then
     X = MsgBox("Data in there already",vbYesNo)
     If X = 7 then Exit Sub     'Clicking No returns 7
End If

There are other options for the IF condition,
IF Worksheets("DATASHEET").Range("MP1_Suction_Temperature").Text <> ""

 

[DonkeyOte]

yes something like the below at the commencement of your routine (you could think about storing your cell as a variable - range) but this would work I think

If IsEmpty(Worksheets("DATASHEET").Range("MP1_Suction_Temperature")) = False then
M1 = MsgBox("Cell Not Empty: Continue ?",vbyesno,"Overwrite?")
If M1 = vbno then Exit Sub
End If

 

[borreltijd]

yes something like the below at the commencement of your routine (you could think about storing your cell as a variable - range) but this would work I think

If IsEmpty(Worksheets("DATASHEET").Range("MP1_Suction_Temperature")) = False then
M1 = MsgBox("Cell Not Empty: Continue ?",vbyesno,"Overwrite?")
If M1 = vbno then Exit Sub
End If

Thanks this works fine !