Put listbox value in correct worksheet cell

gino59

Active Member
Joined
Jul 26, 2010
Messages
496
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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 _
          [COLOR=red](Range)("Table1[[#totals],[account]],-1,5"))[/COLOR]
     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?
 
Upvote 0
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</PRE>
</PRE>
However, that would require a cell in the table to have already been selected.</PRE>
??</PRE>
Thanks for the quik response!</PRE>
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top