Excel VBA UserForm / Linking text in combobox/textbox to selected line in listbox

[Young Grasshopper]

Hello world!

I have a rough draft of an userform that i would appriciate some help with.
The section in question will look something like this:

Right, so this is what i would like it to do:
Let's say I select "test kampanje 1" and set the budget to 30 avg daily, and checking of search partners as illustrated above.

Then when I select "test kampanje 2", all the text and comboboxes underneath clears, and I set the avg daily budget to 100 and checking of add negative keywords f.ex.
And then the same for test kampanje 3 and 4 and so on..

But I want the info to be stored/saved, so when I click back to "test kampanje 1" again, the boxes autofill with earlier information (30 avg daily budget, and search partners).
What would be best practice for achieve this?

I will need to paste this information with VBA code later without selecting one and one line, so do the information needs to be store somewhere, like a data sheet or something maybe?
The number of lines in the Campaign listbox is updated with form initializing, and can vary from 1 to 100 lines.


I really don't know how to go about this, so would appriciate any suggestions

 

[Legacy 143009]

If I were you I would declare a public variable array outside the functions so everyone can access it. Then I would add a condition in listbox click event to check if the array is empty. If not, call form array. I would also add new value insert on budget textbox or partner checkbox change events.

Dim listboxValues() As Variant
Dim previousIndex As Long
Private Sub UserForm_Initialize()
  ListBox1.List = Array("test kampanje 1", "test kampanje 2", "test kampanje 3")
  ReDim listboxValues(ListBox1.ListCount, 2)
End Sub
Private Sub ListBox1_Click()
  listboxValues(previousIndex, 2) = PartnerCheckbox.Value
  If listboxValues(getSelectedIndex(), 1) <> "" Then
    BudgetTextbox.Value = listboxValues(getSelectedIndex(), 1)
    PartnerCheckbox.Value = listboxValues(getSelectedIndex(), 2)
  End If
  previousIndex = getSelectedIndex()
End Sub
Private Sub BudgetTextbox_Change()
    listboxValues(getSelectedIndex(), 1) = BudgetTextbox.Value
End Sub
Private Sub PartnerCheckbox_Click()
    listboxValues(getSelectedIndex(), 2) = PartnerCheckbox.Value
End Sub
Function getSelectedIndex() As Long
  With Me.ListBox1
    If .ListIndex < 0 Then
      Exit Function
    Else
      getSelectedIndex = .ListIndex + 1
    End If
  End With
End Function

 

[Young Grasshopper]

Thanks for the replay, Flashbond.
Sorry, but i forgot to mention that listbox values will be different every time I start with a new customer , so I can't state the array values like "test kampanje 1", it needs to work from the values that are in the listbox at the given time.

F.ex
For Customer A the campaigns could called A B C etc, while next time i use the workbook for customer B the campaigns could be called 1 2 3 etc.. So this will be an empty worktool where the information will be filled out for each customer.

So this wouldn't work since the array is filled with list names, right?

 

[Legacy 143009]

Thanks for the replay, Flashbond.
Sorry, but i forgot to mention that listbox values will be different every time I start with a new customer , so I can't state the array values like "test kampanje 1", it needs to work from the values that are in the listbox at the given time.

F.ex
For Customer A the campaigns could called A B C etc, while next time i use the workbook for customer B the campaigns could be called 1 2 3 etc.. So this will be an empty worktool where the information will be filled out for each customer.

So this wouldn't work since the array is filled with list names, right?

I added the list items just for example. You should populate them by yourself for each session. But as soon as you close the UserForm, you will lose the information stored in listboxValues(). You should write them somewehere else in workbook (in a cell) if you want to keep them permanently.

 

[Young Grasshopper]

Right, just so i understand you correctly, when you say populate for each session, do you mean manually?
The list is populated by this code now:

Private Sub UserForm_Initialize()
Dim List As New Collection
Dim rnArea As Range, rnCell As Range
Dim vaValues As Variant

Set rnArea = Worksheets("Sheet1").Range(Range("B11:B1000").End(xlUp))

On Error Resume Next
For Each rnCell In rnArea
 If rnCell.Value <> "" Then
List.Add rnCell.Value, CStr(rnCell.Value)
End If
Next rnCell
On Error GoTo 0

For Each vaValues In List
Campaignbox.AddItem vaValues
Next

End Sub

This works great as the list gets populated while I work, so could this also populate the array in some way?
If i work with up to 100 campaigns, it's to much to manually plot all of those into vba.

 

[Legacy 143009]

Here is a sample project. Listboxes adjust itself dynamically according to workbook. It also permanently records the information to Values sheet.

Sample.xlsm

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

 

[Young Grasshopper]

Wow, this is almost exactly what I was looking for, but there is a couple of things I can't figure out in the code.. I tweaked the workbook a littlebit, so it's easier to see what i mean.

Sample_Replay.xlsm

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

The two problems I still have is:
1) I need the list in campaign box to not include duplicates.
When this is going to be used every campaign can have multiple adsets Connected to it. So could look like this:

Campaign 1 - Adset 1
Campaign 1 - Adset 2
Campaign 1 - Adset 3
Campaign 2 - Adset 1
Campaign 2 - Adset 2
and so on..

I have added a code where the adsettbox is populated by the adsets. So if i select Campaign 1 in CampaignBox, I'll get Adset 1, 2 and 3 in the adsettbox. But there is no reason for Campaign 1 to appear more then once in the CampaignBox.

The other thing I'm stuck at is how to automate the Adset Column. Every time the campaignbox i clicked the information from the budget box and the partners box is recorded, as pr your sample.
But the adsettbox will only be clicked sometimes, and for this example the only information that needs to be recorded is the name of the adset (adset1, adset2 etc), but this needs to recorded in a cell right next to the corresponding campaign name, like this:

It don't really matter if the first adsett is pasted adjacent to the "test kampanje 1" in b2 or if both campaign and adsett is pasted in the end of the column like in the picture.
I've tried tweaking the code to make this work, but for some reason, it only works with Adsett1 and whenever I click the others, I get an error. I'm guessing that it's because of how the indexing is done, but i'm to much of a newbe to understand why..

The updated code is in the "sample_replay" also.

Private Sub AdSettBox_Click()
  With Worksheets("Values")
    lRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
    If Not firstRun Then
    writeIndex = Application.Match(AdSettBox.List(previousIndex), .Range("B:B"), 0)
    If Not IsError(writeIndex) Then
      .Cells(writeIndex, 1).Value = CampaingBox.Value
    Else
      .Cells(lRow, 2).Value = AdSettBox.List(previousIndex)
      .Cells(lRow, 1).Value = CampaingBox.Value
    End If
    End If
    readIndex = Application.Match(AdSettBox.Text, .Range("B:B"), 0)
    If Not IsError(readIndex) Then
      CampaingBox.Value = .Cells(readIndex, 1).Value
    End If
  End With
  previousIndex = getSelectedIndex()
  firstRun = False
End Sub

If you could give some more pointers, i would really appreciate it

 

[Legacy 143009]

One question,

Do you have to select adsett everytime? Or can a campaign be recorded without an adsett?

 

[Young Grasshopper]

Adsett will rarely be selected, so absolutely: campaign should be recorded without an Adsett

Adsett have nothing to do with the budget box or partner, but will have it's own connected perameter/textbox called "max cpc".

But when adsett is selected and max cpc is filled, it needs to be recorded in a cell next to the corresponding campaign for it to work. So it's important that when the adsett IS selected, it's it gets recorded, but doesn't really matter where

 

[Legacy 143009]

Sorry for the mess but this is the best I can do. It needed so many validations. I hope you can implement to your project. Best regards..

Sample_Final.xlsm

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl