Update Cells Based on UserForm ComboBox 1

[LGMan]

Hi, I've got to make maintenance of one of my workbooks easier for someone else to do whilst if on my holidays
Currently I have a 'Helper' sheets that contains a small table range B2029 with Locations in B20 to B29, Manager Names in C20:C29 and the Manager email address in D2029.

This data is used within an email routine, so I regularly have to update that list while Managers go on secondment or move on within the organization. This is no bother for me to manually update the Helper sheet, however I want to make is easier for anyone not that familiar with the way my workbook is designed can update the list with confidence.

So I have built a Userform that has a ComboBox CboLoc with the range B20:B29, Textbox TbName and TextBox TbEmail
I can choose from CboLoc, but would like the new value in TbName to update the relevant cell in C20:C29 and the new value in TbEmail to update the relevant cell in D2029.
So If I choose the value 'South' from the ComboBox, and type a new name and new emais details in to the text boxes.
Then if cell B25 contains 'South', I want the value in TbName to replace whats already in C25 and so on for D25 for the email address.
so far I've got..

Private Sub CmdAmend_Click()
Dim MLoc as Range
Set MLoc = Sheets("Helper").Range("B20:B29").Find(What:=Me.CboLoc.Value
Range("C20:C29")=MLoc.Offset(0,1).Value = Me.TbName
End Sub

However this just amends the range C20:C29 into the Values of 'False' not sure where I'm going wrong?

 

[SkipVought]

hi,

Where you make the assignment, you have an [highlight #FCE94F]EQUALITY[/highlight] rather than a VALUE.

Range("C20:C29") = [highlight #FCE94F]MLoc.Offset(0, 1).Value = Me.TbName[/highlight]

That [highlight #FCE94F]EQUALITY[/highlight] is either TRUE or FALSE!

What VALUE do you want to assign? It seems as if you want to assign an ARRAY to this range.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[LGMan]

Hi Skip, I'm trying rather unsuccessfully to find the value from the ComboBox, and update the cell next to it with what's in the TextBox called TbName.
So from from UserForm, I could choose the Location of 'South' and type the Name of 'Mr Tek Tip' in TbName.
If South is located in cell B25, then C25 should be amended to show 'Mr Tek Tip'

Thanks

 

[SkipVought]

It would seem to me that your UserForm is used to LOOKUP a value that the user has selected from a ComboBox in Column B in the Helper sheet and then get the values from Columns C & D into some other sheet/table; yes/no???

Does the lookup VALUE (that is selected from the ComboBox) already exist in this other sheet/table? I suspect so. So, why not just use a WorkSheet_SelectionChange event in the column where these lookup values in the sheet/table exist, and when one of those values is SELECTED, just do the lookup and fill in the blanks, so to speak, for the missing data in that sheet/table. You would not need a UserForm at all!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[LGMan]

Skip, all the values from the ComboBox and Text Boxes on the Userform are in the same sheet in the same table which is covered by Sheets("Helper").Range("B2029"). So the ComboBox uses the values from B20:B29, and it's the corresponding cells in Range C20:C29 and D2029 that I want to update with the values from the 2 Text Boxes

I wanted a Userform as this is more familiar with the guys that would be required to update the table in my absence.

 

[PHV]

I think you meant to do this:
Set MLoc = Sheets("Helper").Range("B20:B29").Find(What:=Me.CboLoc.Value)
MLoc.Offset(0,1).Value = Me.TbName

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LGMan]

Thanks that's exactly it