SOLVED

MS Excel IF formula in VBA

Brass Contributor

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,,

kammumin_0-1662907092498.png

kammumin_0-1662907700191.png

 

kammumin_1-1662907100694.png

kammumin_2-1662907340495.png

 

 

4 Replies
best response confirmed by KAM_Mumin (Brass Contributor)
Solution

@KAM_Mumin 

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
Thanks a lot brother, it works,,, but i just have a little bit correction and it's also done,,,,

@Hans Vogelaar 

can 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?

kammumin_0-1663501125784.png

 

@KAM_Mumin

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
1 best response

Accepted Solutions
best response confirmed by KAM_Mumin (Brass Contributor)
Solution

@KAM_Mumin 

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

View solution in original post