User form codes

[lady_alina]

Hi,

Location Furniture Item Vendor

I created a user form in excel its working fine but the problem now is in each location there can be same type of different items of furniture say for example - Look at the example below:

Location Furniture Count of furniture Item Count of items
1 Chairs 10 D1 10
1 Chairs 2 D2 2
1 Table 1 T1 1

My question is can I enter in furniture box using a comma and it gets separated by itself in different rows and Location gets copied as many times as i entered furniture separated by comma.

Or can anyone give me a better solutions please. I can shwo you my code if required. Need this urgently, please help.

 

[dmt32]

Hi welcome to the board.
As an example you can use Split Function to do what you want something like this:

Dim arr As Variant
    Dim x As Integer
   
   'Return a zero-based one-dimensional array
    arr = Split(TextBox1.Text, ",")
    For x = LBound(arr) To UBound(arr)
        MsgBox arr(x)
    Next x

But would be helpful if you post code you have - Plenty on board here to help you modify it.

Hope Helpful

Dave

 

[lady_alina]

sorry here is the table

[TABLE="class: grid, width: 2, align: left"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Furniture[/TD]
[TD]Count of Furniture[/TD]
[TD]Item[/TD]
[TD]Count of item[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Chairs[/TD]
[TD]10[/TD]
[TD]C1[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Chairs[/TD]
[TD]2[/TD]
[TD]C2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Table[/TD]
[TD]1[/TD]
[TD]T1[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]









Hi,

I created a user form in excel its working fine but the problem now is in each location there can be same type of different items of furniture say for example - Look at the example below:

My question is can I enter in furniture box using a comma and it gets separated by itself in different rows and Location gets copied as many times as i entered furniture separated by comma.

Or can anyone give me a better solutions please. I can show you my code if required. Need this urgently, please help.

 

[lady_alina]

Thanks Dmt32 for your quick reply.. here is the code i used for my form. Can you tell me where to use the code you've given in this code:



Private Sub CancelButton_Click()

Unload Me

End Sub

Private Sub ClearButton_Click()

Call UserForm_Initialize

End Sub

Private Sub ComboBox1_Change()

End Sub

Private Sub CommandButton1_Click()

End Sub

Private Sub Label5_Click()

End Sub

Private Sub OKButton_Click()

Dim emptyRow As Long

'Make Sheet2 active
Sheet2.Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 6

'Transfer information
Cells(emptyRow, 1).Value = LocationTextBox.Value
Cells(emptyRow, 2).Value = Location2TextBox.Value
Cells(emptyRow, 3).Value = ClassComboBox.Value
Cells(emptyRow, 4).Value = ItemTextBox.Value
Cells(emptyRow, 5).Value = CostTextBox.Value
Cells(emptyRow, 6).Value = VendorTextBox.Value
Cells(emptyRow, 7).Value = ReferencetoitemURLTextBox.Value
Cells(emptyRow, 8).Value = CurrentitemsTextBox.Value
Cells(emptyRow, 9).Value = OrderingitemsTextBox.Value
Cells(emptyRow, 10).Value = NotesTextBox.Value

End Sub

Private Sub UserForm_Initialize()

'Empty LocationTextBox
LocationTextBox.Value = ""

'Empty Location2TextBox
Location2TextBox.Value = ""

'Empty ClassComboBox
ClassComboBox.Clear

'Fill ClassComboBox
With ClassComboBox
.AddItem "Desk"
.AddItem "Table"
.AddItem "Storage"
.AddItem "Accessories"
.AddItem "Wall"
.AddItem "Floor"
.AddItem "Technology"
End With

'Empty ItemTextBox
ItemTextBox.Value = ""

'Empty Costtextbox
CostTextBox.Value = ""

'Empty VendorTextBox
VendorTextBox.Value = ""

'Empty ReferencetoItemURLtextbox
ReferencetoitemURLTextBox.Value = ""

'Empty CurrentItemstextbox
CurrentitemsTextBox.Value = ""

'Empty OrderingItemsURLtextbox
OrderingitemsTextBox.Value = ""

'Empty Notestextbox
NotesTextBox.Value = ""

'Set Focus on LocationTextBox
LocationTextBox.SetFocus

End Sub

 

[dmt32]

Hi,
are you able to place copy of your workbook in public dropbox?
I am tight on time today but will, unless another joins thread with a solution, have a look & see if I can assist you later.

Dave

 

[lady_alina]

Sorry cannot share the work book. I tried your code however its giving me error , see below please

Private Sub CLASSTextBox_Change()Dim arr As Variant
Dim x As Integer

'Return a zero-based one-dimensional array
arr = Split(CLASSTextBox.Text = x, ",") - this line turns yellow
For x = LBound(arr) To UBound(arr)
MsgBox arr(x)
Next x
End Sub

 

[lady_alina]

sorry if I'm making a silly mistake but I'm really new to this

 

[lady_alina]

Also the sheet is protected and i want the form to work on protected sheet only. Here is the code for my user form:





Private Sub CLOSEButton_Click()

Unload Me

End Sub

Private Sub ClearButton_Click()

Call UserForm_Initialize

End Sub



Private Sub OKButton_Click()

Dim emptyRow As Long

'Make Sheet2 active
Sheet2.Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 6

'Transfer information
Cells(emptyRow, 1).Value = LOCATIONTextBox.Value
Cells(emptyRow, 2).Value = LOCATION2TextBox.Value
Cells(emptyRow, 3).Value = CLASSTextBox.Value
Cells(emptyRow, 4).Value = CLASSCOUNTTextBox.Value
Cells(emptyRow, 5).Value = COSTTextBox.Value
Cells(emptyRow, 6).Value = ITEMTextBox.Value
Cells(emptyRow, 7).Value = ITEMCOUNTTextBox.Value
Cells(emptyRow, 8).Value = VENDORTextBox.Value
Cells(emptyRow, 9).Value = ITEMURLTextBox.Value
Cells(emptyRow, 10).Value = CURRENTComboBox.Value
Cells(emptyRow, 11).Value = ORDERComboBox.Value
Cells(emptyRow, 12).Value = MFGTextBox.Value
Cells(emptyRow, 13).Value = NOTESTextBox.Value

End Sub



Private Sub UserForm_Initialize()
'Empty LocationTextBox
LOCATIONTextBox.Value = "" -------This should copy by itself into rows when classitem breaks into rows

'Empty Location2TextBox
LOCATION2TextBox.Value = "" -------This should copy by itself into rows when classitem breaks into rows

'Empty ClassTextBox
CLASSTextBox.Value = "" ----- this column will have comma and I want the entries to break and show in different rows when ok is clicked

'Empty Classcounttextbox
CLASSCOUNTTextBox.Value = "" -------- want to fix this field for numbers only, meaning if text is enter it should show error

'Empty Classcosttextbox
COSTTextBox.Value = "" -------- want to fix this field for numbers only, meaning if text is enter it should show error

'Empty ItemTextBox
ITEMTextBox.Value = ""

'Empty ItemcountTextBox
ITEMCOUNTTextBox.Value = "" -------- want to fix this field for numbers only, meaning if text is enter it should show error

'Empty VendorTextBox
VENDORTextBox.Value = ""

'Empty ItemURLtextbox
ITEMURLTextBox.Value = ""

'Empty CurrentComboBox
CURRENTComboBox.Clear

'Fill CurrentComboBox
With CURRENTComboBox
.AddItem "Yes"
.AddItem "No"
End With

'Empty Ordercombobox
ORDERComboBox.Clear

'Fill OrderComboBox
With ORDERComboBox
.AddItem "Yes"
.AddItem "No"
End With

'Empty MFGtextbox
MFGTextBox.Value = ""

'Empty Notestextbox
NOTESTextBox.Value = ""

'Set Focus on LocationTextBox
LOCATIONTextBox.SetFocus


End Sub

 

[lady_alina]

Its really urgent can somebody help

 

[NoSparks]

Give this a try for your OK button
If you use a password, uncomment the password parts

Private Sub OKButton_Click()
    Dim arr As Variant
    Dim x As Integer
    Dim emptyRow As Long
    'Dim myPassword As String
    
'myPassword = "whatever"

'Make Sheet2 active
Sheet2.Activate
ActiveSheet.Unprotect 'Password:=myPassword

arr = Split(CLASSTextBox.Text, ",")
For x = LBound(arr) To UBound(arr)
   
    'Determine emptyRow
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 6
    
    'Transfer information
    Cells(emptyRow, 1).Value = LOCATIONTextBox.Value
    Cells(emptyRow, 2).Value = LOCATION2TextBox.Value
    Cells(emptyRow, 3).Value = arr(x)     'CLASSTextBox.Value *
    Cells(emptyRow, 4).Value = CLASSCOUNTTextBox.Value
    Cells(emptyRow, 5).Value = COSTTextBox.Value
    Cells(emptyRow, 6).Value = ITEMTextBox.Value
    Cells(emptyRow, 7).Value = ITEMCOUNTTextBox.Value
    Cells(emptyRow, 8).Value = VENDORTextBox.Value
    Cells(emptyRow, 9).Value = ITEMURLTextBox.Value
    Cells(emptyRow, 10).Value = CURRENTComboBox.Value
    Cells(emptyRow, 11).Value = ORDERComboBox.Value
    Cells(emptyRow, 12).Value = MFGTextBox.Value
    Cells(emptyRow, 13).Value = NOTESTextBox.Value
Next x
ActiveSheet.Protect 'Password:=myPassword
End Sub