Spreadsheet with comboboxes, how to choose value from it

[kalle82]

Hi!

I have a an excel 2003 woorkbook with some sheets. In one of the sheets one of the columns consists of comboboxes with 4 different values. I have a userform where i look up the correct row, and then add the same text as in the comboxbox. But somehow it does not count as if i had choosen the value manually(it counts the comboboxes that have a value). Unfortunately I'm not allowed to do any changes to the file...

But I have an idea, as the comboboxes has the same values. Is there a way I can edit the combobox value by setting the correct range and then tell it to use combobox.index(3) as an example?

Or how do I edit the combobox in a native way?

 

[SkipVought]

hi,

one of the columns consists of comboboxes

Are these comboboxes...

1) Data/Validation
2) ActiveX
3) MS Forms

Type 1) has the value directly in a cell

Types 2) & 3) are not related to any cell, row or column. They are sheet objects and can be referenced either by a NAME or an INDEX.

Beyond that, it is very difficult to understand what it is that you need to do. You talk about a "correct row" and adding "the same text as in the comboxbox" and "somehow it does not count" and "it counts the comboboxes that have a value".

Unfortunately, you have us GUESSING!!! You have provided no code or specific direction.

Please tell us clearly, concisely and completely, what it is that you need to do.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[UnsolvedCoding]

I am a bit foggy on what it is you are trying to do.

If you are trying to count the value of a combobox on the sheet itself then the question is this - does the value show on the sheet? If so something like CountA would work.

If the count is only from the comboboxes then the next question is, if you write code in a different workbook to read the one your using, can you count the number of comboboxes with a value and subtract that from the total number of all combo boxes? This would mean you aren't altering original data and can retain in your own workbook what you want.

Truthfully I am not clear on what you are after, but given what you said those would be my first questions.

 

[kalle82]

Hmm! Sorry for my crapass description..

The file im about to work with has comboboxes inside cells in one column. It's also a data list so you always have the availablity to sort it. I really dont think the comboxes are
[qoute]
Are these comboboxes...

1) Data/Validation
2) ActiveX
3) MS Forms

[/qoute]

I think they are Data/validation boxes.

What i want to do is:

My code has 4 outcomes, if i reach one i want it to go to the cell(already fixed) and then choose the correct value. I have tried to insert text in the cell(i copied from the cell itself) , but the validation fails when my program adds text to that cell.

 

[SkipVought]

If you use the COPY/PASTE METHOD on a Data/Validation cell, it LOOSES the Validation format.

Please post your code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kalle82]

Hi, this is the code I'm using! It's the logtext that fails the validation. is there a way you can tell it to set the combobox.index(4), so that the validation does not break?

Sub avslutaarende(pages, thedate)

Select Case pages

Case Is = 1

identifier = UserForm1.TextBox10.Text
logtext = UserForm1.ComboBox15.Text
Case Is = 2
identifier = UserForm1.TextBox15.Text
logtext = UserForm1.ComboBox16.Text
Case Is = 3
identifier = UserForm1.TextBox55.Text
logtext = UserForm1.ComboBox17.Text
Case Is = 4
identifier = UserForm1.TextBox62.Text
logtext = UserForm1.ComboBox18.Text
Case Is = 5
identifier = UserForm1.TextBox69.Text
logtext = UserForm1.ComboBox19.Text
End Select


Dim intMyString As String
Dim lngLastRow As Long
Dim strRowNoList As String

intMyString = identifier 'Value to search for, change as required.
lngLastRow = Cells(Rows.Count, "B").End(xlUp).Row 'Search Column B, change as required.

For Each cell In Range("B2:B" & lngLastRow) 'Starting cell is B2, change as required.

    If cell.Value = intMyString Then
        If strRowNoList = "" Then
        strRowNoList = strRowNoList & cell.Row
        Else
        strRowNoList = strRowNoList & ", " & cell.Row
        End If
    End If
Next cell

' Here i make the changes, the logtext row is the one that breaks the validation...
Sheets("Ärenden").Range("G" & strRowNoList).Value = thedate
Sheets("Ärenden").Range("K" & strRowNoList).Value = logtext

ActiveWorkbook.Save

End Sub

 

[SkipVought]

Huh?

logtext is a VARIABLE.

Your Comboboxes are in a UserForm.

Where is the Data Validation or Combobox ON THE SHEET?

Also, you cannot put a LIST in a RANGE reference.

Let's start from the top. Without technical verbage, please explain WHAT you are trying to accomplish, rather than HOW you thing it ought to be done.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kalle82]

I have a combobox with four values on my userform.

I WANT IT TO DO THE FOLLOWING
When a user clicks on a commandbutton. It should take the value from the userform combobox, enter this into the datavalidation combobox inside the workbook.

I have alreday accomplished all this. But with my method i loose the validation format of the cell.

But as you said before:

If you use the COPY/PASTE METHOD on a Data/Validation cell, it LOOSES the Validation format.

It makes sense since my logtext variable, is inserted into that box.

Therefore I need a way to let my VBA code enter the value without loosing the validation format in the combobox cell. So a way of having the script choosing value for from the data validation combobox.

Hope that's a little clearer!

 

[SkipVought]

WHERE is the code that puts the value in the data validation cell?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kalle82]

Hi! The code that puts the value in the combobox is as follows

Sheets("Ärenden").Range("K" & strRowNoList).Value = logtext

The logtext variable consists Of the following:

Logtext = combobox1.value

 

[SkipVought]

what value is in strRowNoList?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kalle82]

strRowNoList is the row where the identifier has been found, this for loop iterates throug all cells in B column, and stops when intMystring has been found.

strRowNoList = strRowNoList & cell.Row

intMyString = identifier 'Value to search for, change as required.
lngLastRow = Cells(Rows.Count, "B").End(xlUp).Row 'Search Column B, change as required.

For Each cell In Range("B2:B" & lngLastRow) 'Starting cell is B2, change as required.
'*****GET THE ROW where the value should be changed*** 
    If cell.Value = intMyString Then
        If strRowNoList = "" Then
        strRowNoList = strRowNoList & cell.Row
        Else
        strRowNoList = strRowNoList & ", " & cell.Row
        End If
    End If
Next cell

 

[SkipVought]

So is this statement NOT putting the value in the correct cell?

Sheets("Ärenden").Range("K" & strRowNoList).Value = logtext

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kalle82]

No its works! But as you stated before, it breaks the data validation...

 

[SkipVought]

It does not break it for me.

I can assign ANY value and the DV feature is not affected!

Exactly what do you mean by "BREAKS"?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kalle82]

There is something(probably) a count function in the file that is supposed to count the rows that has a value. When using My macro to add those recirds does not show up when it counts the rows which has a value. Sorry for being so vague I'm not the creator of the file... Only thing i know is when i look i DATA-->VALIDATION, it has show as dropdown checked, and that the values included in the drop down come from cell 1 to 4 for in the in the O column.

I'm thankful for the time you spent on this and I understand if there is nothing to do further with this little information.

Thanks SKIP AND EVERYONE ELSE!

 

[SkipVought]

There is something(probably) a count function in the file that is supposed to count the rows that has a value...Sorry for being so vague

Well if YOU cannot be certain and definitive, how can you expect ANYONE to provide cogent assistance?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]