Results 1 to 4 of 4

Put listbox value in correct worksheet cell

This is a discussion on Put listbox value in correct worksheet cell within the Excel Questions forums, part of the Question Forums category; Hi all! Trying to make sure that a value chosen from a userform listbox gets placed in the proper worksheet ...

  1. #1
    Board Regular gino59's Avatar
    Join Date
    Jul 2010
    Location
    MA - USA
    Posts
    419

    Question Put listbox value in correct worksheet cell

    Hi all!

    Trying to make sure that a value chosen from a userform listbox gets placed in the proper worksheet table cell (xl2007). After selecting one item from the list box and clicking enter, the value selected should get placed in the next designated cell.

    Code:
     
    Private Sub btnEnter_Click()
         Dim NextRow As Long
         Sheets("Sheet1").Activate
         NextRow = Range.Offset _
              (Range)("Table1[[#totals],[account]],-1,5"))
         Cells(NextRow, 5) = lstAccount.Text
    End Sub
    I get Compile error: Argument not optional on Range. But offset is listed as a valid argument. Any ideas?

    Many thanks!

    Gino

  2. #2
    Board Regular
    Join Date
    Jul 2010
    Location
    New Delhi
    Posts
    348

    Default Re: Put listbox value in correct worksheet cell

    Quote Originally Posted by gino59 View Post
    Hi all!

    Trying to make sure that a value chosen from a userform listbox gets placed in the proper worksheet table cell (xl2007). After selecting one item from the list box and clicking enter, the value selected should get placed in the next designated cell.

    Code:
     
    Private Sub btnEnter_Click()
         Dim NextRow As Long
         Sheets("Sheet1").Activate
         NextRow = Range.Offset _
              (Range)("Table1[[#totals],[account]],-1,5"))
         Cells(NextRow, 5) = lstAccount.Text
    End Sub
    I get Compile error: Argument not optional on Range. But offset is listed as a valid argument. Any ideas?

    Many thanks!

    Gino
    What does (Range) signify in the line in Red?
    Hope This Helps !
    Prabby
    --------------------
    WannaB MrExcel MVP
    - tRYING tO fIGURE oUT hOW !

  3. #3
    Board Regular gino59's Avatar
    Join Date
    Jul 2010
    Location
    MA - USA
    Posts
    419

    Default Re: Put listbox value in correct worksheet cell

    Hi Prabby,

    Range should be to signify the "table range"?? I'm guessing!?!?!

    The help files show to use this...


    Code:
    Worksheets("Sheet1").ActivateActiveCell.Offset(rowOffset:=3, columnOffset:=3).Activate

    However, that would require a cell in the table to have already been selected.
    ??
    Thanks for the quik response!

  4. #4
    Board Regular gino59's Avatar
    Join Date
    Jul 2010
    Location
    MA - USA
    Posts
    419

    Thumbs up Re: Put listbox value in correct worksheet cell

    Got it!

    Code:
     
      Dim NextRow As Long
         Sheets("Sheet1").Activate
         
         Range("Table1[[#Totals],[account]]").Select
          ActiveCell.Offset(rowoffset:=-1, columnoffset:=0).Activate
          ActiveCell = lstAccount.Text
    Aaaarrrggghhh!!! Something that simple and here I am complicating the CRAPOLA out of it!!

    Thanks all!!

    Cheers, Gino

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com