+ Reply to Thread
Results 1 to 4 of 4

Little more advice on this code

  1. #1
    Greg B
    Guest

    Little more advice on this code

    Hi all Dave Peterson helped me by writing this code but I need it to do a
    little more and I cant seemto work it out.

    What the code below does isshows the meeting that are coming up and copies
    the information to another worksheet which I will use another way. But at
    the moment it only copies the first cell.

    The list's rowsource is a2:e65536 so I probably need to have the code copy
    the whole line.

    Any ideas would be appreciated.

    Here is the code
    Private Sub CommandButton1_Click()
    Dim destCell As Range

    With Worksheets("Popup")
    Set destCell = .Columns.End(xlUp).Offset(1, 0)
    End With

    With ListBox1
    If .ListIndex > -1 Then
    destCell.Value = .List(.ListIndex)
    End If
    End With
    Unload Me
    messagebox.Show

    End Sub
    Thanks again

    Greg



  2. #2
    Dave Peterson
    Guest

    Re: Little more advice on this code

    You changed a bit of the code.

    This won't work.
    Set destCell = .Columns.End(xlUp).Offset(1, 0)

    So you have a listbox that has 5 columns in it. And you want to copy each of
    the 5 columns to the cell?

    Something like this might work:

    Option Explicit
    Private Sub CommandButton1_Click()
    Dim destCell As Range
    Dim iCtr As Long

    With Worksheets("Popup")
    Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
    End With

    With Me.ListBox1
    If .ListIndex > -1 Then
    For iCtr = 1 To .ColumnCount
    destCell.Offset(0, iCtr - 1).Value _
    = .List(.ListIndex, iCtr - 1)
    Next iCtr
    End If
    End With

    Unload Me

    End Sub

    Do you really have almost 64k rows in that listbox?

    Greg B wrote:
    >
    > Hi all Dave Peterson helped me by writing this code but I need it to do a
    > little more and I cant seemto work it out.
    >
    > What the code below does isshows the meeting that are coming up and copies
    > the information to another worksheet which I will use another way. But at
    > the moment it only copies the first cell.
    >
    > The list's rowsource is a2:e65536 so I probably need to have the code copy
    > the whole line.
    >
    > Any ideas would be appreciated.
    >
    > Here is the code
    > Private Sub CommandButton1_Click()
    > Dim destCell As Range
    >
    > With Worksheets("Popup")
    > Set destCell = .Columns.End(xlUp).Offset(1, 0)
    > End With
    >
    > With ListBox1
    > If .ListIndex > -1 Then
    > destCell.Value = .List(.ListIndex)
    > End If
    > End With
    > Unload Me
    > messagebox.Show
    >
    > End Sub
    > Thanks again
    >
    > Greg


    --

    Dave Peterson

  3. #3
    Dave Peterson
    Guest

    Re: Little more advice on this code

    You've got a reply at one of your other posts.

    Greg B wrote:
    >
    > Hi all Dave Peterson helped me by writing this code but I need it to do a
    > little more and I cant seemto work it out.
    >
    > What the code below does isshows the meeting that are coming up and copies
    > the information to another worksheet which I will use another way. But at
    > the moment it only copies the first cell.
    >
    > The list's rowsource is a2:e65536 so I probably need to have the code copy
    > the whole line.
    >
    > Any ideas would be appreciated.
    >
    > Here is the code
    > Private Sub CommandButton1_Click()
    > Dim destCell As Range
    >
    > With Worksheets("Popup")
    > Set destCell = .Columns.End(xlUp).Offset(1, 0)
    > End With
    >
    > With ListBox1
    > If .ListIndex > -1 Then
    > destCell.Value = .List(.ListIndex)
    > End If
    > End With
    > Unload Me
    > messagebox.Show
    >
    > End Sub
    > Thanks again
    >
    > Greg


    --

    Dave Peterson

  4. #4
    Dave Peterson
    Guest

    Re: Little more advice on this code

    Whoops. I didn't notice that the message was cross posted.

    Dave Peterson wrote:
    >
    > You've got a reply at one of your other posts.
    >
    > Greg B wrote:
    > >
    > > Hi all Dave Peterson helped me by writing this code but I need it to do a
    > > little more and I cant seemto work it out.
    > >
    > > What the code below does isshows the meeting that are coming up and copies
    > > the information to another worksheet which I will use another way. But at
    > > the moment it only copies the first cell.
    > >
    > > The list's rowsource is a2:e65536 so I probably need to have the code copy
    > > the whole line.
    > >
    > > Any ideas would be appreciated.
    > >
    > > Here is the code
    > > Private Sub CommandButton1_Click()
    > > Dim destCell As Range
    > >
    > > With Worksheets("Popup")
    > > Set destCell = .Columns.End(xlUp).Offset(1, 0)
    > > End With
    > >
    > > With ListBox1
    > > If .ListIndex > -1 Then
    > > destCell.Value = .List(.ListIndex)
    > > End If
    > > End With
    > > Unload Me
    > > messagebox.Show
    > >
    > > End Sub
    > > Thanks again
    > >
    > > Greg

    >
    > --
    >
    > Dave Peterson


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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