How to add value to a combobox which is on a worksheet (not form)

[Gülredy]

"My problem is that I cant give value to a combobox in the way that I want to!
"code place was here"
This works fine, if I run the program from the VB editor, but if a user opens the xls document, there will be no options to choose from in the combobox.

How can I do that, when the user opens, those items are already in the combobox?

I'm beginner to this, but I don't have time to learn just for the beginnings!"


I figured out that problem. I had to put the code into the Private Sub Workbook_Open() section!

Now another question:

I have this xls file (teszt2-.zip) where the user have to enter, the author, year of publications etc, and then with a record button i have to copy those cells value to the Recorded items section (Like A4 cell value to A9, B4 value to B9, C4 value to C9, and D4 value to D9)

How is it possible?

I can do it for one record like this:

Range("A9").Value = Range("A4").Value
Range("B9").Value = Range("B4").Value
Range("C9").Value = Range("C4").Value
Range("D9").Value = ComboBox1.Value

But this is not good because I have to do it as much as the user hits the record button.
So every of the new record have to be in a new line, with every button hit!

[westconn1]

place code in workbook open event?

or use text property to put 1st item, then additems in comboboxdropbuttonclick event if list count below number of items to load

[Gülredy]

I've figured it out myself, but I did as the same as you told. Can you answer my second question? I don't wanted to make another topic for that!

[vbfbryce]

I'm not clear on exactly what you want to do (2nd question). Please explain further.

[Gülredy]

Okay I'll try to make it clear as its possible.
Heres a picture how it looks like in excel:



So as you can see there's a button called Rögzítés (record) which have to record those values that are written into the light green area (where the Szerző, Cím, Kiadás éve, Kategória are) and paste them below the dark green line (where the Rögzített tételek are), and after it recorded (or copy pasted it) its earses the values what have been written into the light green area!

That is not a problem, as this code does this:

Range("A9").Value = Range("A4").Value
Range("A4").Value = ""
Range("B9").Value = Range("B4").Value
Range("B4").Value = ""
Range("C9").Value = Range("C4").Value
Range("C4").Value = ""
Range("D9").Value = ComboBox1.Value
ComboBox1.Value = ""

The problem is its only able to record ONE "object", into the first line (A99) under the dark green line. Under object I mean those values what the user writes into the light green area (where the Szerző, Cím, Kiadás éve, Kategória are) and I need it to record new ones in new lines with every "Rögzítés" (record) button hit!

So heres what I mean in pictures:



I hope you understand what I want now!

[vbfbryce]

I understand now. Will put something together shortly.

[vbfbryce]

Try something like this (done in Excel 2010):

Sub Button1_Click()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rngCopy As Range
    Dim rngPaste As Range
    Dim nextRow As Long     'next open row to copy into
    
    Set wb = Workbooks("copy data.xlsm")   'the name of my workbook
    Set ws = wb.Sheets("sheet1")   'name of my sheet
    Set rngCopy = ws.Range("a4", "d4")  'where to copy from
    nextRow = Range("a" & ws.Rows.Count).End(xlUp).Row + 1
    Set rngPaste = ws.Range("a" & nextRow)  'where to copy to
    
    rngCopy.Copy
    rngPaste.PasteSpecial
    
End Sub

[Gülredy]

I've got a runtime error '9' Subscript out of range at this line
Set wb = Workbooks("copy teszt2-.xlsm") (teszt2-.xlsm is the name of my file)

[vbfbryce]

"Set wb = Workbooks("copy teszt2-.xlsm") (teszt2-.xlsm is the name of my file) "

If your file is "teszt2-.xlsm" then you should have this:

set wb = workbooks("teszt2-.xlsm")

[Gülredy]

That was the first thing I did , but still the same error.
Even renamed my file to test2.xlsm but same error at same line :S
Also renamed sheet1 to Munka1 (because in my language its called this)

    Set wb = Workbooks("copy test2.xlsm")   'the name of my workbook
    Set ws = wb.Sheets("Munka1")   'name of my sheet
    Set rngCopy = ws.Range("a4", "d4")  'where to copy from
    nextRow = Range("a" & ws.Rows.Count).End(xlUp).Row + 1
    Set rngPaste = ws.Range("a" & nextRow)

[vbfbryce]

Is the word "copy" part of your file name, or no?

If no, this:

Set wb = Workbooks("copy test2.xlsm") 'the name of my workbook

should be this:

Set wb = Workbooks("test2.xlsm") 'the name of my workbook

[Gülredy]

Ohh it works! At least it copies the first three values!
But it don't Copy the value one from the combo box!
And another problem that it copies the formation too (the green background).

Can you deal with these problems too? It has to be this specific! But dont misunderstand me, I'm already greatful for your help!

[vbfbryce]

PasteSpecial can take an argument, for example:

Worksheets("Munka2").Range("A1:D10000").PasteSpecial xlPasteValues

will paste the VALUE, not the format or formula.

I don't know what you mean by this:

"But it don't Copy the value one from the combo box!"

[Gülredy]

This is what I'm talk about!
It doesent copies the Kategória's value for me!
And it even copies the format settings of the cells (but its not even a problem)

[Gülredy]

Are you still here?

[vbfbryce]

Here is some changed code for your Command Button 1:

Private Sub CommandButton1_Click()
    
'Rögzítés button

'Mezõk kitöltésének vizsgálata:
    If Len(Range("A4")) = 0 Then
        MsgBox "Adjon meg egy Szerzõt!"
        Cancel = True
        Exit Sub
    End If
    
    If Len(Range("B4")) = 0 Then
        MsgBox "Adjon meg egy Címet!"
        Cancel = True
        Exit Sub
    End If
    
    If Len(Range("C4")) = 0 Then
        MsgBox "Adjon meg a Kiadási évet!"
        Cancel = True
        Exit Sub
    End If
    
    If ComboBox1.ListIndex < 0 Then
        MsgBox "Nem választott kategóriát"
        Cancel = True
        Exit Sub
    End If
'---------------------------------------------
'Rögzítés folyamata
    Dim comboValue As String    '*********************************

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rngCopy As Range
    Dim rngPaste As Range
    Dim nextRow As Long     'next open row to copy into
    
    comboValue = ComboBox1.Value    'retrieve value of combo box *****
    
    Set wb = Workbooks("munka.xlsm")   'the name of my workbook
    Set ws = wb.Sheets("Munka1")   'name of my sheet
    Set rngCopy = ws.Range("a4", "c4")  'where to copy from
    nextRow = Range("a" & ws.Rows.Count).End(xlUp).Row + 1
    Set rngPaste = ws.Range("a" & nextRow)  'where to copy to
    
    rngCopy.Copy
    rngPaste.PasteSpecial xlPasteValues 'changed this *********
    
    ws.Range("d" & nextRow).Value = comboValue  'use value from combo ****

'--------------------------------------------
   
End Sub

[Gülredy]

NICE!
Thank you very much for your time! It works very good!
I added a little code snippet becuse the data which is written into by the user, have to be deleted every time I hit the Rögzít button!

I don't want to be ungreatful, but could I ask a little more of your help?

There's the export button, which should export all the data from A9 : D9 and everything which is under it (like A10 : D10, A11 : D11 etc until theres data) from the Munka1 (alias sheet1). (in a simple way, every data which is under the dark green line)

I have a codesnippet for it but it only saves the first line from there to a file:

Private Sub CommandButton3_Click()
'Export button
 Dim ce As Range
    Open "D:\export_from_xls.txt" For Append As #1
    fileba = ""
    
    For Each ce In Range("A9:D9")
        fileba = fileba & ce.Value & ";"
    Next ce
    Print #1, fileba
    Close #1
'--------------------------------------------
End Sub

Thanks again for your help! I'm really in your debt!

[vbfbryce]

No problem!

To read all the lines for export (newly added code has the 5 stars behind it):

Dim lastRow As Long     '*****
    Dim rngExport As Range  '*****
    
    comboValue = ComboBox1.Value    'retrieve value of combo box
    
    Set wb = Workbooks("munka.xlsm")   'the name of my workbook
    Set ws = wb.Sheets("Munka1")   'name of my sheet
    
    
    
    
    Set rngCopy = ws.Range("a4", "c4")  'where to copy from
    nextRow = Range("a" & ws.Rows.Count).End(xlUp).Row + 1
    Set rngPaste = ws.Range("a" & nextRow)  'where to copy to
    
    rngCopy.Copy
    rngPaste.PasteSpecial xlPasteValues 'changed this
    
    ws.Range("d" & nextRow).Value = comboValue  'use value from combo
    
    lastRow = ws.Range("a" & ws.Rows.Count).End(xlUp).Row   '*****
    Set rngExport = ws.Range("a9", "d" & lastRow)           '*****
        
    For Each ce In rngExport                                '*****
        'code here                                          '*****
    Next ce                                                 '*****

[Gülredy]

So it have to look like this for me?

Dim lastRow As Long    
Dim rngExport As Range

 lastRow = ws.Range("a" & ws.Rows.Count).End(xlUp).Row   
 Set rngExport = ws.Range("a9", "d" & lastRow)           
    
Open "D:\export_from_xls.txt" For Append As #1
    fileba = ""   
    
For Each ce In rngExport                                
        fileba = fileba & ce.Value & ";"
Next ce
    Print #1, fileba
    Close #1

Because it have to be under the export button for me! So its code have to be seperated from the one which we worked before!

[vbfbryce]

If you want to loop through each CELL in the export range, yes.

If you wanted to loop through "LINES," then no.

For example, the first "CE" would have the first "Szerző" in it, the next would have the first "Cím" and so on, until you got to the last filled row in the range.

[Gülredy]

So then what should I write to the place where you put the comment "'code here" ?
Sorry but I'm a beginner in this langueage!

[vbfbryce]

I'm not sure how the output needs to be / should be. I haven't done too much with appending to text files, but will check it out. Can you show what you'd like the output to look like?

[Gülredy]

It have to bee look like this:
Szerző1;Cím1;Kiadáséve1;Kategória1;
Szerző2;Cím2;Kiadáséve2;Kategória2;

[Gülredy]

I have a solution here:

Open "d:\export_from_xls.txt" For Append As 1#
    fileba = ""
    Sheets("Munka1").Select
    For Each ce In Range("a9:d9" & Range("d65536").End(xlUp).Row)
        If Len(ce) > 0 Then
            fileba = fileba & ce.Offset(0, 0) & ";"
        End If
    Next ce
    Print #1, fileba
    Close #1

But it writes all in one line!
I need every record in a new line

[vbfbryce]

Maybe change to something like this:

Private Sub CommandButton3_Click()
'Export button
 Dim ce As Range
 Dim strTemp As String  '*****
 
    Open "D:\export_from_xls.txt" For Append As #1
    fileba = ""
    For Each ce In Range("A9:D9")    'this range needs to be changed to something variable that would end up being "a9:d21" for example ***
        If ce.Row = 9 And ce.Column = 1 Then
            strTemp = ce.Value & ";"
        ElseIf ce.Column <> 4 Then
            strTemp = strTemp & ce.Value & ";"
        ElseIf ce.Column = 4 Then
            strTemp = strTemp & ce.Value & ";" & vbCrLf
        End If
    Next ce
    fileba = strTemp
    Print #1, fileba
    Close #1
'--------------------------------------------
End Sub

[Gülredy]

I've figured it out, so I got the solution in the morning!
So the whole project is solved.

Thanks for all your help and your time!
I'm greatful!
Have a nice day!