+ Reply to Thread
Results 1 to 4 of 4

Replace Application.InputBox with a string.

Hybrid View

  1. #1
    Registered User
    Join Date
    05-21-2014
    Posts
    30

    Replace Application.InputBox with a string.

    I got this code over the internet, and I modify a bit to fit my needs.
    There is one thing I don't really know how to get rip.
    I want to replace the application.inputbox part with my own string there. I do not want the user input anymore. However, I do not know really know how to do that part. Can anyone please help.


    Sub FilterData()
        Dim ws1Master As Worksheet, wsNew As Worksheet, wsFilter As Worksheet
        Dim Datarng As Range, FilterRange As Range, objRange As Range
        Dim rowcount As Long
        Dim colcount As Integer, FilterCol As Integer, FilterRow As Long
        Dim SheetName As String
        Dim BillAcctID As String
        Dim CellAddress As String
    
    Call GetFile
    
    'look for billing_acct_id col
    BillAcctID = Cells.Find(What:="Billing_Acct_ID", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Select
    
    'get the cell address of billing_acct_id col
    CellAddress = ActiveCell.Address
    
    
        'master sheet
        Set ws1Master = ActiveSheet
        'set the Column you
        'are filtering
        
    
    
    top:
        On Error Resume Next
        'Set objRange = Application.InputBox("Select Field Name To Filter", "Range Input", CellAddress, , , , , 8)
    
    
        
        On Error GoTo 0
        If objRange Is Nothing Then
            Exit Sub
        ElseIf objRange.Columns.Count > 1 Then
            GoTo top
        End If
    
        FilterCol = objRange.Column
        FilterRow = objRange.Row
    
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
        End With
        On Error GoTo progend
        'add filter sheet
        Set wsFilter = Sheets.Add
        With ws1Master
            .Activate
            .Unprotect Password:=""  'add password if needed
            
            rowcount = .Cells(.Rows.Count, FilterCol).End(xlUp).Row
            colcount = .Cells(FilterRow, .Columns.Count).End(xlToLeft).Column
    
            If FilterCol > colcount Then
                Err.Raise 65000, "", "FilterCol Setting Is Outside Data Range.", "", 0
            End If
    
            Set Datarng = .Range(.Cells(FilterRow, 1), .Cells(rowcount, colcount))
            'extract Unique values from FilterCol
            .Range(.Cells(FilterRow, FilterCol), _
                   .Cells(rowcount, _
                          FilterCol)).AdvancedFilter _
                          Action:=xlFilterCopy, _
                          CopyToRange:=wsFilter.Range("A1"), _
                          Unique:=True
            rowcount = wsFilter.Cells(wsFilter.Rows.Count, "A").End(xlUp).Row
            
            'set Criteria
            wsFilter.Range("B1").Value = wsFilter.Range("A1").Value
            For Each FilterRange In wsFilter.Range("A2:A" & rowcount)
                'check for blank cell in range
                If Len(FilterRange.Value) > 0 Then
                    'add the FilterRange to criteria
                    wsFilter.Range("B2").Value = FilterRange.Value
                    SheetName = RTrim(Left(FilterRange.Value, 31))
                    'if FilterRange sheet exists
                    'update it
                    If SheetExists(SheetName) Then
                        Sheets(SheetName).Cells.Clear
                    Else
                        'add new sheet
                        Set wsNew = Sheets.Add(After:=Worksheets(Worksheets.Count))
                        wsNew.Name = SheetName
                    End If
                    
                    
                    Datarng.AdvancedFilter Action:=xlFilterCopy, _
                                           CriteriaRange:=wsFilter.Range("B1:B2"), _
                                           CopyToRange:=Sheets(SheetName).Range("A1"), _
                                           Unique:=False
                End If
            Next
            .Select
        End With
    progend:
        wsFilter.Delete
        With Application
            .ScreenUpdating = True
            .DisplayAlerts = True
        End With
        If Err > 0 Then
            MsgBox (Error(Err)), 16, "Error"
            Err.Clear
        End If
        
    Call Splitbook
    
    ActiveWorkbook.Close savechanges:=False
    
    Call OpenFile
        
    End Sub
    Last edited by bbmak; 05-27-2014 at 04:14 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Replace Application.InputBox with a string.

    E.g.,

    Set objRange = Range("A1:D10")
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Replace Application.InputBox with a string.

    Hi bbmak,

    Welcome and thanks for putting in code tags.

    In the line that follows, the '8' at the end means that InputBox is not returning a string, but a range of cells. It appears that the 'range of cells' is really only one cell.
    Set objRange = Application.InputBox("Select Field Name To Filter", "Range Input", CellAddress, , , , , 8)
    In the context of your macro, the line would be replaced by something like:
    Set objRange = Range("D3")   'for one cell
    
    'or
    
    Set objRange = Range("D2:D12")   'for a range of cells
    I hope this helps.

    Lewis

  4. #4
    Registered User
    Join Date
    05-21-2014
    Posts
    30

    Re: Replace Application.InputBox with a string.

    Thank guys, I got it.

+ 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] Inputbox vs. Application.InputBox
    By miyachow in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-08-2013, 12:53 PM
  2. [SOLVED] Application.Inputbox
    By Hilton1982 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-14-2012, 09:16 AM
  3. Screen positioning inputbox that is set as Application.InputBox
    By vdongen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-06-2010, 07:59 AM
  4. Application.inputbox
    By davegugg in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-08-2009, 11:27 AM
  5. [SOLVED] Replace application.RTD property by Application.RTDServers collect
    By John.Greenan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-07-2005, 10:05 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