Modify Texbox Data displayed via combobox selection

[ccsher77]

Hi All,


Firstly this is a contiuation of a project for which I have already recieved som valuable inout from som of the guys on here so Thanks for that.


However as this is a slightly different issue from any previosu threads I have started I though it might be better to start a new one to avoid any confusion.


I have uploaded a copy of the workbook here:


https://skydrive.live.com/redir?resid=F5966AED44B736E9!159&authkey=!AIBmocaG2dwTpFE


As you will see on Sheet 1 I have 2 textboxes and a Command button, the code copies the textbox entries to onto the "Data" sheet into columns B & C sheet on the next empty row (there is a seperate code which indexes the number in column A by 1 everytime a new entry is made. - This all Works as expected.


In Sheet 3 there is a Combobox, 2 Text Boxes and A command button.

The textboxes are populated with information from the Data Sheet based on the selction made in the combobox. - This works perfectly as well.


However What I want is the ability for the user to amend the info displayed in the textboxes and then when the command button is pressed this info is copied back into the correct cells in the data sheet.


As you will see this works perfectly well for textbox 1 but the code then seems to revert back to the combobox change line before the modified data from textbox is updated.


I really am stumped as to why I cant get this to work


Any help is as always very much appreciated

 

[Hui]

Craig


Change this module to:

[pre]

Private Sub CommandButton1_Click()
Application.Calculation = xlManual
EnableEvents = False

With Worksheets("Data")
.Cells(Sheets("Sheet3").ComboBox1.ListIndex + 2, 2).Value = Worksheets("Sheet3").TextBox1.Value
.Cells(Sheets("Sheet3").ComboBox1.ListIndex + 2, 3).Value = Worksheets("Sheet3").TextBox2.Value
End With

EnableEvents = True
Application.Calculation = xlAutomatic

End Sub

[/pre]

 

[ccsher77]

Hi Hui,


Thanks for the reply, that code has done the trick.


I really appreciate you guys tkaing the time to help the less proficent amongst us.


In an effort to incrase my understanding and assuming its an easy task would you be able to give me a quick run through of what the changes you have made do? So that in the future i may not need to bug you guys?


I appreciate time is limited for all of us so I wont be offended if you cant,


Anyway thanks again for the help you have already given me today.

 

[Hui]

Craig


The combobox is linked to a Named Formula that is using an Offset function to derive a Dynamic Range it is based on


As a Volatile function as the data is changed the range in the offset changes and so the Offset function calculates a new range, even though it is the same phyical range as the old one


That causes the Combobox to think its source has changed and so it fires a Combobox change event


Turning off calculation and events stops that

 

[ccsher77]

Hi Hui,


Thanks for the explanation, I kind of understand I think!!


Assuming I have understood correctly and whilst your solution works perfectly, would it be beter if I used a standard named range say A1:A5000 as oppose to a dynamic one


Just one further question, If I were to use indivual Cell ranges instead of textboxes i.e Instead of textbox1 on the Input(sheet1) and Summary(sheet2) I use Cell A1 on the sheets how should I adjust to code you kindly supplied.


Thanks