+ Reply to Thread
Results 1 to 3 of 3

Argument not Optional

Hybrid View

  1. #1
    Registered User
    Join Date
    02-09-2016
    Location
    Riverside, RI
    MS-Off Ver
    Vista
    Posts
    6

    Argument not Optional

    Sub Button3_Click(Tsht As Worksheet)
    Dim FlagR1 As Range
    Dim FlagR2 As Range
    Dim FlagR3 As Range
    Dim FlagR4 As Range
    Dim FlagR5 As Range
    Dim FlagR6 As Range
    Dim FlagR7 As Range
    Dim FlagR8 As Range
    Dim FlagR9 As Range
    Dim FlagR10 As Range
    
    Dim credit As Range
    Dim debit As Range
    
    Set Tsht = ThisWorkbook.Sheets("MASTER USD UPLOAD")
    Set FlagR1 = Tsht.Cells.Range("B21:B40")
    Set FlagR2 = Tsht.Cells.Range("B42:B48")
    Set FlagR3 = Tsht.Cells.Range("B50:B54")
    Set FlagR4 = Tsht.Cells.Range("B56:B96")
    Set FlagR5 = Tsht.Cells.Range("B98:B112")
    Set FlagR6 = Tsht.Cells.Range("B114:B123")
    Set FlagR7 = Tsht.Cells.Range("B125:B130")
    Set FlagR8 = Tsht.Cells.Range("B132:B144")
    Set FlagR9 = Tsht.Cells.Range("B146:B147")
    Set FlagR10 = Tsht.Cells.Range("B149:B166")
    
    Set credit = Tsht.Cells.Range("J180")
    Set debit = Tsht.Cells.Range("I180")
    
        For Each cell In FlagR1
        cell.value = "O"
        Next
        For Each cell In FlagR2
        cell.value = "O"
        Next
        For Each cell In FlagR3
        cell.value = "O"
        Next
        For Each cell In FlagR4
        cell.value = "O"
        Next
        For Each cell In FlagR5
        cell.value = "O"
        Next
        For Each cell In FlagR6
        cell.value = "O"
        Next
        For Each cell In FlagR7
        cell.value = "O"
        Next
        For Each cell In FlagR8
        cell.value = "O"
        Next
        For Each cell In FlagR9
        cell.value = "O"
        Next
        For Each cell In FlagR10
        cell.value = "O"
    
    End Sub
    Here is my code, for a simple button. I have been playing around with it for hours, so it may look odd.

    I am trying to fill in cells, within certain ranges, when the button is clicked, however, I keep getting the "Argument not Optional" error, no matter what I change.
    Last edited by alansidman; 02-25-2016 at 04:58 PM. Reason: code tags

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Argument not Optional

    Any chance you could upload a sample workbook?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    02-25-2016
    Location
    Washington, the state
    MS-Off Ver
    2013
    Posts
    1

    Re: Argument not Optional

    Wesrus,
    A couple of things to get your code working.
    1. Change the subroutine prototype. The normal prototype for a form button is like button3_click(). If you place a parameter in the prototype as you did, then you'll have problems.
    2. Next, since there will be no definition for Tsht, you'll need to define that amoung your Dim as Dim Tsht as worksheet
    3. Next, you'll need to define your range cell also in your Dims as dim cell as range
    4. Last, the line where you set the value of Tsht, must be a valid reference to a sheet. If you want something super simple just for testing, try set Tsht = activesheet

    Hope this helps
    Attached Files Attached Files

+ 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] Optional declared argument is not optional
    By tnuis in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-28-2015, 06:59 AM
  2. [SOLVED] Getting error 'argument is not optional'
    By mso3 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-12-2015, 10:01 PM
  3. 'Argument not optional'
    By swoop99 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-15-2011, 03:27 PM
  4. Argument Not Optional
    By mccrimmon in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-14-2011, 12:59 PM
  5. VBA getting argument not optional error
    By sulavsingh6 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-14-2009, 01:06 PM
  6. Argument not optional Error 449! Need Help
    By bad_boyu in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-26-2006, 08:00 AM
  7. optional argument in a function
    By visitor in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-13-2005, 03:06 PM

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