populate a cell with data from a comboBox.

[sheffieldlad]

hi all,

This is a similar thread to one I created a couple of days ago.
I now know that what I was asking in the other thread is impossible so I've decided to change my way of solving my problem.

This time I have set up a user form designed to add the data in my comboBox to a worksheet in the same workbook I am using.
With a comboBox called cboSubbies and defined a data range for the box called myRange. all is working well with the following code.
The comboBox is populated with the data I am asking it to look for.

Private Sub UserForm_Initialize()
 
  Dim myRange As Range
  Dim ws As Worksheet
  
  With Application
  .ScreenUpdating = False
  .StatusBar = True
  End With
  
  Set ws = Worksheets("Subbies")
  For Each myRange In ws.Range("subs")
  Application.StatusBar = "adding " & myRange
    Me.cboSubbies.AddItem myRange.Value
  Next myRange
  
  With Application
  .ScreenUpdating = True
  .StatusBar = False
  End With
  
End Sub

Now, I also have a button on my form.
When I click the button I would like the selected data to be entered into a cell in a sheet called paperwork.

It should be possible to add more than 1 entry by reloading the form and selecting another value.

The target cells on my target sheet (paperwork) are from A6 to A15.

The first time I add data from my form I want it to go to A6.
The 2nd time A7 etc.

I probably need to do something along the lines of the following...

Select paperwork as the active worksheet.

check to see if cell A6 is empty (="") if it is <> "" copy my data there. if it already has data in it move through the range till I find an empty cell.
If all cells are full inform the user with a pop up box and don't copy my data across.

This will be simple to some of you but to a novice like me it's a nightmare.
I'm confident I know more or less what I NEED to do but I haven't got a clue HOW to do it.

Any help would be appreciated.

Paul.

[stnkynts]

Let's abitrarily call this button on your form myButton. While in VB editor and you double click on myButton you should be able to add code to it. Add this:

Private Sub myButton_Click()
Dim ws As Worksheet:    Set ws = Sheets("paperwork")
Dim icell As Range

ws.Activate

For Each icell In ws.Range("A6:A15")
    If IsEmpty(icell) Then
        icell.Value = cboSubbies.Value
        Unload Me 'This can be removed if you don't want to close the UserForm
        Exit Sub
    End If
Next icell

Unload Me
MsgBox ("There are no available spots remaining.")

End Sub

[sheffieldlad]

Let's abitrarily call this button on your form myButton. While in VB editor and you double click on myButton you should be able to add code to it. Add this:

Private Sub myButton_Click()
Dim ws As Worksheet:    Set ws = Sheets("paperwork")
Dim icell As Range

ws.Activate

For Each icell In ws.Range("A6:A15")
    If IsEmpty(icell) Then
        icell.Value = cboSubbies.Value
        Unload Me 'This can be removed if you don't want to close the UserForm
        Exit Sub
    End If
Next icell

Unload Me
MsgBox ("There are no available spots remaining.")

End Sub

I must have been writing my post as you submitted yours

Many thanks, I'll try it now.

[sheffieldlad]

Ok, I got it all to work but I need to find the end of my range of cells.....

This is the code I have so far when I click the button to copy information to my target cells....

Private Sub CommandButton1_Click()

Dim Cells As Range
Dim myData As String


myData = UserForm1.cboSubbies.Value

ActiveWorkbook.Sheets("paperwork").Activate

Dim ws As Worksheet

Set ws = Worksheets("paperwork")

For Each Cells In ws.Range("targetCells")
If Cells.Value = "" Then GoTo copy:




Next Cells
copy:
Cells.Value = myData

TheEnd:


End Sub

This adds my information to my target cells just fine until All the cells are full.
It then gives me an error.

I need to find the end of my range of target cells.

Can anyone point me in the right direction please?

Many thanks,

Paul.

[sheffieldlad]

That works.

I changed my code to the following.

Private Sub CommandButton1_Click()

Dim Cells As Range
Dim myData As String


myData = UserForm1.cboSubbies.Value

ActiveWorkbook.Sheets("paperwork").Activate

Dim ws As Worksheet

Set ws = Worksheets("paperwork")

For Each Cells In ws.Range("targetCells")

If IsEmpty(Cells) Then Cells.Value = myData




Next Cells

MsgBox ("All cells are full. Please delete a sub contractor.")

Many thanks

[sheffieldlad]

OOps, had to add a way to jump out of the loop otherwise all of my target cells get filled with the same data.

Private Sub CommandButton1_Click()

Dim Cells As Range
Dim myData As String


myData = UserForm1.cboSubbies.Value

ActiveWorkbook.Sheets("paperwork").Activate

Dim ws As Worksheet

Set ws = Worksheets("paperwork")

For Each Cells In ws.Range("targetCells")

If IsEmpty(Cells) Then
Cells.Value = myData
GoTo theEnd:
End If




Next Cells

MsgBox ("All cells are full. Please delete a sub contractor.")

theEnd:


End Sub

Thanks again.