VBA update spreadsheet

eyedentity · 01-05-2009, 02:58 AM

Hi

I'm working on a spreadsheet with contact details, Tel, Fax Contact name etc.

I have a userform that can populate the details from the spreadsheet

The Userform uses a combobox where the company can be selected and then the textboxes populates the contact details.

My problem is i want changes that are made in the textboxes to be saved to the spreadsheet.

David M58 · 01-05-2009, 06:55 PM

One way is to add a button to the UserForm that will transfer data from the TextBoxes to the spreadsheet.

For example, suppose you have a ComboBox named ComboBox1, and when you select a company name, a TextBox named Phone is filled with the corresponding phone number, which is stored in column C of the spreadsheet.

You can set up a CommandButton to transfer data from TextBoxes to the spreadsheet after data in the form's Phone field is entered or changed. Suppose the phone number for the first company name is in C1, the phone number for the second company name is in C2, and so on. Suppose the CommandButton is named UpdateButton. You can set up the button with code such as:

Code:

Private Sub UpdateButton_Click()
    Cells(ComboBox1.ListIndex + 1, 3).Value = Phone.Value
End Sub

Suppose the user has selected the second company name and changed the phone number. With the second item in the ComboBox selected, the ListIndex will be 1 (for the first item, it will be 0). The code adds 1 to the ListIndex to get the row where the data should be saved (in this example, row 2). Once the code determines the correct cell (C2 in this example), it will look at the data in the TextBox named Phone and put it in that cell.

Modify as necessary.

01-05-2009, 02:58 AM	#1 (permalink)
eyedentity Registered User Join Date: Jan 2009 Posts: 1 OS: xp pro	VBA update spreadsheet Hi I'm working on a spreadsheet with contact details, Tel, Fax Contact name etc. I have a userform that can populate the details from the spreadsheet The Userform uses a combobox where the company can be selected and then the textboxes populates the contact details. My problem is i want changes that are made in the textboxes to be saved to the spreadsheet.

01-05-2009, 06:55 PM	#2 (permalink)
David M58 Registered User Join Date: May 2008 Location: Baltimore, Maryland Posts: 160 OS: Windows XP SP3	Re: VBA update spreadsheet One way is to add a button to the UserForm that will transfer data from the TextBoxes to the spreadsheet. For example, suppose you have a ComboBox named ComboBox1, and when you select a company name, a TextBox named Phone is filled with the corresponding phone number, which is stored in column C of the spreadsheet. You can set up a CommandButton to transfer data from TextBoxes to the spreadsheet after data in the form's Phone field is entered or changed. Suppose the phone number for the first company name is in C1, the phone number for the second company name is in C2, and so on. Suppose the CommandButton is named UpdateButton. You can set up the button with code such as: Code: Private Sub UpdateButton_Click() Cells(ComboBox1.ListIndex + 1, 3).Value = Phone.Value End Sub Suppose the user has selected the second company name and changed the phone number. With the second item in the ComboBox selected, the ListIndex will be 1 (for the first item, it will be 0). The code adds 1 to the ListIndex to get the row where the data should be saved (in this example, row 2). Once the code determines the correct cell (C2 in this example), it will look at the data in the TextBox named Phone and put it in that cell. Modify as necessary. Last edited by David M58; 01-05-2009 at 06:56 PM.