Command Button

Tejas Kore

Board Regular
Joined
Nov 2, 2017
Messages
72
Office Version
  1. 365
Platform
  1. Windows
Hi Friends,

I have names of few persons in first column as sample data. The code below displays data(names of those person) and allows user to select any name and paste that name to cell(2,2).Now I want to select names multiple times and paste each selected name one after another .I am unable to implement this functionality as code written for Command button executes completely.... I mean if I initialise and add variable for looping purpose that initialisation statement gets executed every time because of which it becomes useless.

How can I achieve this ?
I have highlighted the corresponding code with RED color.

Code:
Sub Sample()
    CreateUserForm
End Sub
Function CreateUserForm()
Dim var As Variant
Dim myForm As Object
Dim NewFrame As MSForms.Frame
Dim NewButton As MSForms.CommandButton
'Dim NewComboBox As MSForms.ComboBox
Dim NewListBox As MSForms.ListBox
'Dim NewTextBox As MSForms.TextBox
'Dim NewLabel As MSForms.Label
'Dim NewOptionButton As MSForms.OptionButton
Dim NewCheckBox As MSForms.CheckBox
Dim X As Integer
Dim Line As Integer


'This is to stop screen flashing while creating form
Application.VBE.MainWindow.Visible = False


Set myForm = ThisWorkbook.VBProject.VBComponents.Add(3)


'Create the User Form
With myForm
    .Properties("Caption") = "New Form"
    .Properties("Width") = 300
    .Properties("Height") = 270
End With


'Create ListBox
Set NewListBox = myForm.designer.Controls.Add("Forms.listbox.1")
With NewListBox
    .Name = "lst_1"
    .Top = 10
    .Left = 10
    .Width = 150
    .Height = 230
    .Font.Size = 8
    .Font.Name = "Tahoma"
    .BorderStyle = fmBorderStyleOpaque
    .SpecialEffect = fmSpecialEffectSunken
End With


'Create CommandButton Create
Set NewButton = myForm.designer.Controls.Add("Forms.commandbutton.1")
With NewButton
    .Name = "cmd_1"
    .Caption = "clickMe"
    .Accelerator = "M"
    .Top = 10
    .Left = 200
    .Width = 66
    .Height = 20
    .Font.Size = 8
    .Font.Name = "Tahoma"
    .BackStyle = fmBackStyleOpaque
End With


'MsgBox (last)
'add code for listBox
'lstBoxData = "Data 1,Data 2,Data 3,Data 4"


myForm.codemodule.insertlines 1, "Private Sub UserForm_Initialize()"
myForm.codemodule.insertlines 2, "last = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row"
myForm.codemodule.insertlines 3, "   For i = 2 to last "
myForm.codemodule.insertlines 4, "      me.lst_1.addItem Activesheet.Cells(i,1).Value "
myForm.codemodule.insertlines 5, "   Next i "
myForm.codemodule.insertlines 6, "End Sub"


'add code for Comand Button
'i = 2
[COLOR=#ff0000]
[/COLOR]LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
[COLOR=#ff0000]
myForm.codemodule.insertlines 7, "Private Sub cmd_1_Click()"[/COLOR]
[COLOR=#FF0000]myForm.codemodule.insertlines 8, " i = 2"[/COLOR][COLOR=#FF0000]
myForm.codemodule.insertlines 9, "  If me.lst_1.text <>"""" Then"[/COLOR]
[COLOR=#ff0000]myForm.codemodule.insertlines 10, "     msgbox (""You selected item: "" & me.lst_1.text )"[/COLOR]
[COLOR=#ff0000]myForm.codemodule.insertlines 11, "     ActiveSheet.Cells(i,2).Value"[/COLOR]
[COLOR=#ff0000]myForm.codemodule.insertlines 12, "      i = i + 1"[/COLOR]
[COLOR=#ff0000]myForm.codemodule.insertlines 13, " End If"[/COLOR]
[COLOR=#ff0000]myForm.codemodule.insertlines 14, "End Sub"[/COLOR]


'Show the form
VBA.UserForms.Add(myForm.Name).Show
MsgBox ("I am back")
'Delete the form (Optional)
ThisWorkbook.VBProject.VBComponents.Remove myForm
End Function
 
Last edited by a moderator:

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Forum statistics

Threads
1,213,506
Messages
6,114,027
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