Snasui Excel Forum

Code: Select all

Private Sub UserForm_Initialize()
'Dim c1 As Range
'Dim ws1 As Worksheet
'Set ws1 = Worksheets("Datalist")
'For Each c1 In ws1.Range("ulname")
'  With Me.ComboBox1
'    .AddItem c1.Value
'   .List(.ListCount - 1, 1) = c1.Offset(0, 1).Value
' End With
'Next c1


    'Range("A2").Select
'Do While Not IsEmpty(ActiveCell.Value)
 '  ComboBox1.AddItem ActiveCell.Value
  ' ActiveCell.Offset(1, 0).Select
'Loop
End Sub

Code: Select all

Private Sub UserForm_Initialize()
        Me.ComboBox1.RowSource = "ulname"
        Me.ComboBox2.RowSource = "ulcas"
End Sub

Code: Select all

Private Sub ButtonSave_Click()
Dim irow As Long
Dim iPart As Long
Dim ws As Worksheet
Sheet1.Activate
Set ws = Worksheets("¢éÍÁÙÅÊÙµÃ")
'find  first empty row in database
irow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Row
'iPart = Me.ComboBox1.ListIndex
'check for a part number

        'copy the data to the database
With ws
    .Cells(irow, 1).Value = Me.Label5.Caption
    .Cells(irow, 2).Value = Me.ComboBox1.Value
    .Cells(irow, 3).Value = Me.ComboBox2.Value
    .Cells(irow, 4).Value = Me.TextBox1.Value
End With
Range("A" & Rows.Count).End(xlUp).Select
Selection.ListObject.ListRows.Add AlwaysInsert:=True

Me.ButtonName.Enabled = True
Me.ButtonSave.Enabled = False
Me.ButtonClose.Enabled = True
End Sub

Code: Select all

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<CISData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<CustomerInfo>
		<Idno>1</Idno>
		<chmbasenm>WALNUT EXTRACT</chmbasenm>
		<volume>0.1</volume>
	</CustomerInfo>
	<CustomerInfo>
		<Idno>2</Idno>
		<chmbasenm>(HEXAHYDRO-2H-AZEPIN-2-YLIDENE)BISPHOSPHONIC ACID, DISODIUM SALT</chmbasenm>
		<volume>10</volume>
	</CustomerInfo>
	<CustomerInfo/>
</CISData>

Code: Select all

Private Sub ButtonSave_Click()
    Dim irow As Long
    Dim iPart As Long
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Worksheets(2).Activate
    Set ws = Worksheets("¢éÍÁÙÅÊÙµÃ")
    Set ws1 = Worksheets("Datalist")
       ' irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        irow = ws.Cells(Rows.Count, 1) _
          .End(xlUp).Row

    With ws
        With ws1
            With Application
                Dim casno As Range
                Dim ulname As Range
                .Cells(irow, 1).Value = Me.Label4.Caption
                .Cells(irow, 5).Value = Me.ComboBox1.Value
                .Cells(irow, 6).Value = Me.TextBox1.Value
                .Cells(irow, 2).Value = .Index(ws1.Range("C2:C188"), .Match(.Cells(irow, 5), ws1.Range("D2:D188"), 0))
                .Cells(irow, 3).Value = .Index(ws1.Range("B2:B188"), .Match(.Cells(irow, 5), ws1.Range("D2:D188"), 0))
                .Cells(irow, 4).Value = .Index(ws1.Range("A2:A188"), .Match(.Cells(irow, 5), ws1.Range("D2:D188"), 0))
            End With
        End With
        
    End With
    Range("F" & Rows.Count).End(xlUp).Select
Selection.ListObject.ListRows.Add AlwaysInsert:=True

    
    Me.ButtonName.Enabled = True
    Me.ButtonSave.Enabled = False
    Me.ButtonClose.Enabled = True
End Sub