Results 1 to 7 of 7

Thread: Display a message if already saved data

Hybrid View

Abdul Display a message if already... 01-24-2021, 04:20 PM
DocAElstein Hello Abdul I probably have... 01-24-2021, 08:12 PM
Abdul Thanks for your quick... 01-25-2021, 11:33 PM
DocAElstein Hello Abdul :confused: I... 01-26-2021, 02:29 AM
Abdul Once again thanks for you... 01-26-2021, 11:13 AM
DocAElstein Hello Abdul You can choose... 01-26-2021, 02:04 PM
Abdul Hello Alan Sir, once again... 01-27-2021, 03:18 PM
Previous Post Previous Post   Next Post Next Post
  1. #1
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,522
    Rep Power
    10
    Hello Abdul
    You can choose to make it compulsory to define all variables. This means you would then be forced to Dim all variables
    If you choose this option, then sometimes you will see such simple problems immediately when you try to run your macro

    In your case, Answer was seen by VBA as a variable that was not declared explicitly, ( - I mean it was not Dimed like Dim Answer As …… )

    If, in your original macro, you had Option Explicit at the top of your code module, then , when you tried to run your macro, you would have been warned by VBA of your small error:
    http://i.imgur.com/yn4q13F.jpg



    It is just personal preference. You can choose. Most people prefer to include Option Explicit, because it is helpful to see small errors

    Alan

    Code:
    Option Explicit ' I choose to define all my variables explicitly
    Sub TestPaper()
    
    Dim FoundCell As Range
    Dim FindWhat As String
        FindWhat = Worksheets("Test").Range("D3").Value
        Set FoundCell = Worksheets("Result").Range("C:C").Find(What:=FindWhat, LookAt:=xlWhole)
    
        If Not FoundCell Is Nothing Then
            MsgBox "Register Number " & FindWhat & " is already submitted Test at " & Sheets("Test").Range("J6").Value & ". It can't be edited or modified. You have already secured " & Sheets("Test").Range("J5").Value & " marks."
    Exit Sub
    End If
    
    
    Dim x As Long
    Dim y As Worksheet
    Set y = Sheets("Result")
    
    Dim Answer2 As VbMsgBoxResult
        Answer2 = MsgBox("Do you want to save your Result, If once submitted can't be edited", vbYesNo + vbQuestion + vbDefaultButton2, "Save Result")
        If Answer = vbYes Then
    Else
    
    x = Sheets("Result").Range("B" & Rows.Count).End(xlUp).Row
    With y
    
    Sheets("Test").Range("D2:D4").Copy
    .Range("B" & x + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Transpose:=True
    .Cells(x + 1, 6).Value = Sheets("Test").Range("J2").Value
    .Cells(x + 1, 5).Value = Sheets("Test").Range("J4").Value
    Sheets("Test").Range("L4:AZ4").Copy
    .Range("G" & x + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Sheets("Test").Range("L5:AZ5").Copy
    .Range("Q" & x + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    With Sheets("Result").Range("A2:Z100" & x)
        .Borders(xlEdgeLeft).LineStyle = xlNone
        .Borders(xlEdgeRight).LineStyle = xlNone
        .Borders(xlEdgeTop).LineStyle = xlNone
        .Borders(xlEdgeBottom).LineStyle = xlNone
        .Borders(xlInsideVertical).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With
    
    With Sheets("Result").Range("A3:A" & x + 1)
        .Formula = "=Row() - 2"
        .Value = .Value
    
    With Sheets("Result").Range("A2:Z" & x + 1)
        .BorderAround xlContinuous
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous
        .Borders(xlInsideVertical).LineStyle = xlContinuous
    End With
    End With
    End With
    
    
    MsgBox "Your Result is submitted successfully. You have secured Total Marks is " & Sheets("Test").Range("J4").Value
    
    ActiveWorkbook.Save
    
    'MsgBox "Your Data not saved, you can  make correction if  you want then submit again"
    Exit Sub
    End If
    
    End Sub
    Last edited by DocAElstein; 01-26-2021 at 02:11 PM.

  2. #2
    Junior Member
    Join Date
    Dec 2020
    Posts
    10
    Rep Power
    0
    Quote Originally Posted by DocAElstein View Post
    It is just personal preference. You can choose. Most people prefer to include Option Explicit, because it is helpful to see small errors
    Hello Alan Sir,
    once again lot of thanks for you guidance.
    Last edited by DocAElstein; 01-27-2021 at 03:48 PM.

Similar Threads

  1. Replies: 5
    Last Post: 09-16-2016, 12:57 PM
  2. Replies: 0
    Last Post: 07-07-2013, 01:52 AM
  3. Replies: 2
    Last Post: 07-02-2013, 02:36 PM
  4. Display sheet names in a message box
    By pells in forum Excel Help
    Replies: 4
    Last Post: 02-13-2013, 07:33 PM
  5. Display numbers in Lakhs
    By Prabhu in forum Excel Help
    Replies: 1
    Last Post: 01-29-2012, 02:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •