spreadsheet that has between 4,000 to 6,000 rows

[hardeiin]

I have a spreadsheet that has between 4,000 to 6,000 rows filled in at any give time.

On column "C" click I want a userform to show with column c, a, & z's data in it. Not sure if it should fill 3 textboxes or a multi column list box. I am assuming the later. All of this I understand and know how to do.

The part I am looking for suggestions on would be I want to be able to keep the userform open and add to the textboxes or listbox and only have the userform close when I click the "ok" button.

A example would be:

I click on C20...userform opens and multi column list box is filled with C20, A20, & Z20's data.....I click on C100 and C100, A100, & Z100 is also added to the multi column listbox as a second row...I then click on C41 and C41, A41, & Z41 is added as a 3rd row. Now on clicking "OK" button do macro behind "ok" button click and userform closes.


I have never worked or atleast can not remember working with userfrom vbmodeless, but I am thinking this would be a candidate for it. I also assume since a double click on the cell initially showed the userform that I would need to add a "if userform is already open" do this else do that.

The end result of the project would allow me to individually select 1 row or 1,000 rows and data from 3 columns be added to a userform.

Maybe there way to get it tested in php for my web users?

Your thoughts would be greatly appreciated.

Thanks,

 

[bertie]

Hi and welcome to the board.

Click Insert => Module and paste in the code to launch the form

[COLOR=darkblue]Sub[/COLOR] LoadForm()
   Load UserForm1
   UserForm1.Show vbModeless
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

For my test I used a form with one listBox and one command button. The code for the command button is below. This goes into the form module.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] [COLOR=red]cmdUpdate[/COLOR]_Click()
   [COLOR=darkblue]Dim[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
 
   rw = Selection.Row
 
   [COLOR=darkblue]With[/COLOR] Sheets("[COLOR=red]Sheet1[/COLOR]")
      [COLOR=red]ListBox1[/COLOR].AddItem .Range("C" & rw) & vbTab _
                     & .Range("A" & rw) & vbTab _
                     & .Range("Z" & rw)
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Edit: To use select a row in the worksheet and press the Update button.

 

[Norie]

hardelin

What exactly is 'behind' the OK button?

Do you want the user to be able to edit the data from the lines/records they select?

By the way, why the php test?

If this is destined to go online then I don't really think doing it with Excel will work, not even sure you could do much with php either.

 

[Norie]

Try this.

1 Add a userform (UserForm1).

2 Add a listbox (ListBox1) and command button (CommandButton1).

3 Add this code in the userform module:

Option Explicit
Private Sub CommandButton1_Click()
    Unload Me
End Sub

4 Add this code to the worksheet module of the worksheet you want this to work for.

Option Explicit
 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 3 Then
        AddItems UserForm1.ListBox1, Target, "A", "C", "Z"
        UserForm1.Show vbModeless
        Cancel = True
    End If
End Sub

Sub AddItems(lst As MSForms.ListBox, rw As Range, ParamArray cols())
Dim I As Long
Dim rng As Range
 
    Set rng = Intersect(rw.EntireRow, Columns(cols(0)).EntireColumn)
    
    lst.AddItem rng.Value

    For I = 2 To lst.ColumnCount
        Set rng = Intersect(rw.EntireRow, Columns(cols(I - 1)).EntireColumn)
        lst.List(lst.ListCount - 1, I - 1) = rng.Value
    Next I
    
End Sub

It seems to work but I'm not 100% sure if it's a good idea to keep on showing the userform each time.

Not sure if that might impact somewhere - perhaps even end up with some sort of memory problem.