+ Reply to Thread
Results 1 to 3 of 3

delete specific cell value if user clicks on cancel in inputbox

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    425

    delete specific cell value if user clicks on cancel in inputbox

    Hi,

    I have created a macro that shows three inputboxes.
    Inputbox 1: The user should enter a numerical value
    Inputbox 2: The user shold enter a month
    Inputbox 3: The user should select a range of cells that are going to be copied in the sheet

    I would like the macro to delete a specific cell value (the one which the user has entered in inputbox 2) if the user clicks on cancel in inputbox 3.
    How can i do this?

    Any input is much appreciated!


    Sub GegevensInvoeren()
    
        Dim rngCopyFrom As Range
        Dim iCode As String 'code 88 of 89
        Dim strFactuurMaand As String 'Jaar-Mnd
            
            'invoer periode in bovenste rij
            iCode = InputBox("Voer in code: 88 of 89", "Invoer UWV gegevens")
            strFactuurMaand = InputBox("Voer de factuurmaand in volgens het format ""20XX-XX""", _
            "Invoer UWV gegevens")
            
             If strFactuurMaand <> Format(strFactuurMaand, "yyyy-mm") Then
             MsgBox "U heeft geen geldige factuurmaand ingevuld." _
             & vbCrLf & "Vul de factuurmaand in volgens de notatie ""20XX-XX""." _
             & vbNewLine & vbNewLine & "Bijvoorbeeld: voor de factuurmaand ""juli 2020"" vult u in: ""2020-05""." _
             , vbCritical, "Invoer UWV gegevens"
             If strFactuurMaand <> Format(strFactuurMaand, "yyyy-mm") Then Exit Sub
             End If
    
            'afbreken geen inkomen ingevuld
            If iCode = "" Then
                MsgBox "U hebt nog geen inkomen ingevuld.", vbCritical, _
                , "Inkomen invoeren"
            
            Exit Sub
            End If
            
            'afbreken geen geldige code ingevuld
            If iCode <> 88 And iCode <> 89 Then
            MsgBox "U heeft geen geldige code ingevuld.", vbCritical, "Invoer UWV gegevens"
            
            Exit Sub
            End If
            
            If iCode = 88 Then
                Worksheets("test1").Range("H1").End(xlDown).Offset(1, 0) _
                = strFactuurMaand
            End If
            
            If iCode = 89 Then
                Worksheets("test2").Range("M1").End(xlDown).Offset(1, 0) _
                = strFactuurMaand
            End If
            
            'ínvoer cellen extern sheet
            On Error Resume Next
            Set rngCopyFrom = Application.InputBox("Selecteer de cellen die u wilt invoegen.", _
            Type:=8)
            On Error GoTo 0
            If Not rngCopyFrom Is Nothing Then
            If iCode = 88 Then
              rngCopyFrom.Copy (Worksheets("test1").Range("A1").End(xlDown).Offset(1, 0))
            
            End If
            If iCode = 89 Then
              rngCopyFrom.Copy (Worksheets("test2").Range("B1").End(xlDown).Offset(1, 0))
            End If
        End If
    End Sub
    Attached Files Attached Files

  2. #2
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: delete specific cell value if user clicks on cancel in inputbox

    Perhaps
    Dim keyCell As Range
        ' ...
    
        If iCode = 88 Then
            Set keyCell = Worksheets("test1").Range("H1").End(xlDown).Offset(1, 0)
            KeyCell.Value = strFactuurMaand
        ElseIf iCode = 89 Then
            Set KeyCell = Worksheets("test2").Range("M1").End(xlDown).Offset(1, 0)
            KeyCell.Value = strFactuurMaand
        Else
            Set KeyCell = ActiveSheet.Cells(Rows.Count,1)
        End If
            
        'ínvoer cellen extern sheet
        On Error Resume Next
        Set rngCopyFrom = Application.InputBox("Selecteer de cellen die u wilt invoegen.", Type:=8)
        On Error GoTo 0
        
        If rngCopyFrom Is Nothing Then
            KeyCell.Value = vbNullString
        Else
            If iCode = 88
                rngCopyFrom.Copy (Worksheets("test1").Range("A1").End(xlDown).Offset(1, 0))
            ElseIf iCode = 89 Then
                rngCopyFrom.Copy (Worksheets("test2").Range("B1").End(xlDown).Offset(1, 0))
            End If
        End If
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    425

    Re: delete specific cell value if user clicks on cancel in inputbox

    Thank you! It works.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] MACRO: If user clicks cancel in inputbox,then do nothing. Problem with inputbox appearance
    By Tona in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-05-2015, 04:33 AM
  2. [SOLVED] InputBox User Clicked Cancel
    By goss in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-05-2014, 03:19 PM
  3. [SOLVED] Repeat Inputbox if user click cancel or if user enter character not in selection criteria
    By Faridwahidi in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-24-2014, 02:44 AM
  4. [SOLVED] Looping sub not detecting user cancel from inputbox
    By jshaw82 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-07-2013, 06:14 PM
  5. Inputbox Cancel returns null value, can replace with original cell data
    By bg_enigma1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-23-2012, 05:08 PM
  6. How to catch when the user clicks a cell?
    By WhiteEagle in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-26-2007, 10:17 AM
  7. Run a macro when a user clicks in a certain cell
    By NickySA in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-16-2007, 08:20 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1