Forum Discussion
Solved
MS Excel IF formula in VBA
Hi everybody!
Here I make a data entry sheet when I click on the submit button then it gets the data copy and put it another sheet in a table. But here I am facing a problem that if I put a blank fill without any entry of value it also copy that and put it to the sheet. But I want when the two cell are empty means no entry then it shows me a message and re-back to the cell where need to input data value,,,
I tried to use IF formula in VBA because the Submit button is assigned with this,,,
and there is another solution need that what I need to do for the copied date where I am pasting it then it turns in to number and if I manually format cells as date then it shows date type but how to past it atomically as date type,,,
Please if any of you have a solution then reply to me,,, I attach below a screen short for understanding,,
Sub Submit() If Range("C4").Value = "" Then Range("C4").Select MsgBox "Please enter the date!", vbExclamation Exit Sub End If If Range("C6").Value = "" Then Range("C").Select MsgBox "Please enter the type of bill!", vbExclamation Exit Sub End If If Range("C8").Value = "" Then Range("C8").Select MsgBox "Please enter the amount!", vbExclamation Exit Sub End If Worksheets("Home").Range("C4,C6,C8").Copy With Worksheets("Data Sheet").Range("A2") .PasteSpecial Paste:=xlPasteValuesAndNumberFormats .EntireRow.Insert End With Application.GoTo Worksheets("Home").Range("C6") Range("C6,C8").ClearContents End Sub
Sub Submit() If Range("C4").Value = "" Then Range("C4").Select MsgBox "Please enter the date!", vbExclamation Exit Sub End If If Range("C6").Value = "" Then Range("C").Select MsgBox "Please enter the type of bill!", vbExclamation Exit Sub End If If Range("C8").Value = "" Then Range("C8").Select MsgBox "Please enter the amount!", vbExclamation Exit Sub End If Worksheets("Home").Range("C4,C6,C8").Copy With Worksheets("Data Sheet").Range("A2") .PasteSpecial Paste:=xlPasteValuesAndNumberFormats .EntireRow.Insert End With Application.GoTo Worksheets("Home").Range("C6") Range("C6,C8").ClearContents End Subcan you please give me more a little bit help,,, when I filled up two or three cells then I click on the enter button at keyboard without clicking submit button for copying the data to data sheet but submit button also stay on the excel sheet,,,and there also adding a new check box button to confirm submit,,, can I do this?
I think that would be dangerous - you could still be editing the data. Also, Excel doesn't "know" whether you're pressing Tab or Enter.
About the check box: let's say it's named Check Box 1.
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("C6,C8"), Target) Is Nothing Then Me.CheckBoxes("Check Box 1").Value = (Range("C6").Value <> "" And Range("C8").Value <> "") End If End Sub
- Thanks a lot brother, it works,,, but i just have a little bit correction and it's also done,,,,
