Results 1 to 10 of 10

Using InputBox to clear information in a cell

Threaded View

  1. #1
    Forum Contributor
    Join Date
    07-30-2014
    Location
    USA
    MS-Off Ver
    Version 14.4.3, Mac 2011
    Posts
    145

    Question Using InputBox to clear information in a cell

    This is long but interesting and was fun to work on!

    Background
    So I've been working on a macro that allows me to efficiently preview/edit the sources for a sheet of data by displaying the information listed in a second almost identical sheet that contains only sources, using either a MsgBox or InputBox.

    The macro is run by making a selection of either a single cell or a range of cells on the first sheet ('Data') and clicking a button to bring up the appropriate box with the value of the same cell on the second sheet ('Source').

    Summary of Macro Function:
    What happens when a user selects data?
    If selecting a single empty cell, shows a message box that reads please select a cell with data.
    If selecting a range of empty cells, shows as above.
    If selecting a single cell of data, shows an input box with current source value as default and the ability to edit.
    If selecting a range of cells that are all blank except one, treats as if selecting a single cell.
    If selecting a range of cells that have multiple values of data, returns message box of sources, no edit capability.
    Range of cells skip blank data cells.

    The Issue
    I noticed that with my current code, if I select a single cell, try to delete the default input box value, and press OK, the action is rendered useless and the current value remains in the 'Sources' Sheet. This is the result of me trying to make the Cancel button not delete the default value.

    If I enter a single space, conditional formatting, which runs off ISBLANK, does not highlight the now sourceless Data. This is because the cell is not effectively blank. I believe the solution is the ability to set the Source cell value back to default when input box is empty and OK is clicked.

    Do you guys have any thoughts?

    Code attached below with a ton of comments! (feel free to point out where I'm stupid, started VBA a little while ago)

    :D

    Red

    Sub Source()
    
    Dim ws1 As Worksheet, ws2 As Worksheet, rng As Range, cell As Range, cellsdata$, ipbox$
    ' Flag will help determine if every cell in range is empty
    Dim Flag As Boolean
    Flag = False
    
    ' Variable used to count the number of non-empty cells in a range
    Dim MultiCount As Integer
    MultiCount = 0
    
    ' Save points for worksheet 1 and worksheet 2 values for later
    Dim ws1save$, ws2save$
    
    
    Set ws1 = ActiveSheet
    If ws1.Name = "Data" Then Set ws2 = Sheets("Sources")
    If ws1.Name = "Sources" Then Set ws2 = Sheets("Data")
    
    Set rng = Selection
    
        If rng.Count > 1 Then
        
            For Each cell In rng
            
                ' Will not count empty cell(s) in the range
                If Not IsEmpty(ws1.Range(cell.Address)) Then
                    
                    ' Confirm there is a cell that is not empty
                    Flag = True
                    
                    ' Will add 1 to itself for every cell in the range that is not empty.
                    ' Works so that if you select all empty range except 1 cell, you can edit that cell's source.
                    MultiCount = MultiCount + 1
                
                    ' If selection is more than 1 cell, this vector grows itself.
                    cellsdata = cellsdata & "> [" & ws1.Range(cell.Address) & "] : " & ws2.Range(cell.Address) & _
                        vbNewLine
                        
                    ' Will be used if MultiCount = 1 to select the single cell's values within the range
                    ws1save = ws1.Range(cell.Address)
                    ws2save = ws2.Range(cell.Address)
                        
                End If
                
            ' Next cell means do the same for the next selection in the rng -- the following cellsdata includes
            ' the one before it
            Next cell
                
            ' If a cell was confirmed to not be empty within the range, then
            If Flag Then
                
                If MultiCount = 1 Then
    
                        ' Allows editing of source and shows saved value of ws2 in text box
                        ipbox = InputBox(cellsdata & vbNewLine & vbNewLine & "_________________________" & _
                            vbNewLine & "Please enter the correct source for [" & ws1save & "] below.", _
                                "Source of Data", ws2save)
                        ' If you cancel or leave the box blank, it keeps the original source
                        If Not ipbox = vbNullString Then ws2save = ipbox
                        
                 Else
                    
                ' Creates the message box, adds the data:source information, and informs about editing only
                ' possible by selecting one cell
                MsgBox cellsdata & vbNewLine & "______________________________________" & _
                   vbNewLine & "To edit, please select one cell at a time.", , "Source of Data"
            
                End If
            
            ' If flag was never set to true (range is empty)
            Else
                
                ' Politely tells you to select a cell with data
                MsgBox cellsdata & vbNewLine & "______________________________________" & _
                   vbNewLine & "Please select a cell with data.", , "Source of Data"
                
            End If
        
        ' If selection is not more than 1
        Else
        
            ' Will not run for an empty cell
            If Not IsEmpty(ws1.Range(rng.Address)) Then
            
                ' Allows inputbox to show for only one data:source
                cellsdata = "> [" & ws1.Range(rng.Address) & "] : " & ws2.Range(rng.Address)
            
                ' Allows editing of source and shows current value of ws2 in text box
                ipbox = InputBox(cellsdata & vbNewLine & vbNewLine & "_________________________" & _
                    vbNewLine & "Please enter the correct source for [" & ws1.Range(rng.Address) & "] below.", _
                        "Source of Data", ws2.Range(rng.Address))
                ' If you cancel or leave the box blank, it keeps the original source
                If Not ipbox = vbNullString Then ws2.Range(rng.Address) = ipbox
                
            Else
                ' Politely tells you to select a cell with data
                MsgBox cellsdata & vbNewLine & "______________________________________" & _
                   vbNewLine & "Please select a cell with data.", , "Source of Data"
            
            End If
                           
        End If
    
    End Sub
    Last edited by RedSummer; 04-16-2015 at 09:16 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA code to clear the contents (or value) of a cell without clearing the formula
    By BazzaBit in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-10-2013, 08:16 PM
  2. [SOLVED] Inputbox to code for input text in cells.
    By abjac in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-07-2013, 12:24 PM
  3. How to clear formula without clearing cell contents
    By prayami in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-03-2008, 04:33 AM
  4. Clear a worksheet without clearing formulas?
    By doodah in forum Excel General
    Replies: 4
    Last Post: 01-02-2006, 04:59 PM
  5. Replies: 2
    Last Post: 03-11-2005, 11:06 AM

Tags for this Thread

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