Changing data selected in a UserForm

[Petrogeo]

Hi Guys... I?m working in a project that I need to use a userform to change or replace data in a sheet dababase. I would like to use the same userform to view and change/replace this data using view/change button.

Does anybody could help me??

Please find below the file attached.

[mdmackillop]

Hi Petrogeo
You can use UserForm_Initialize to read in data from the spreadsheet. You can also set a button to update amended data from the form to the spreadsheet. Here's a simple example

[Petrogeo]

Thanks for response... good idea to insert the update button... but it didn?t work very well... first: the combobox "cboSeq" didn?t get all the values from the spreadsheet... So the update values are made just for the row selected.
Any other idea?

[mdmackillop]

Apologies for not being psychic.
What is the logic behind the combobox; why not just use a textbox?

So the update values are made just for the row selected.

I don't understand what you mean.

[Petrogeo]

Md, one of the reasons is because the "Seq" column on the spreadsheet could have equal values, for example: one "Seq" could be acquired in different days, different lines... etc..

I?m sending the file with some modifications.. thanks

[mdmackillop]

You need to explain what you're trying to achieve. If seq is limited to the read in values, what happens if you need a new one? You also have repeated values in seq. How are you intending to make use of these?

[Petrogeo]

"Seq" numbers is not limited, I can enter new one on "imput Data" form. What I need to know is how could I modify Line numbers, Date, FSP... and apply this mofications on the same sequence (Seq)... pressing "view/update" button ???

thanks in advance

[Charlize]

Use a unique number (the rowno) that you call for example Id_no. This Id_no can be used to search on (since it is unique). When you are on a form and want to change something we search on a row in column A for this unique number. When found, we select the whole row. When you change something, you could say something like

ActiveCell.EntireRow.Cells(1, 1).Value = textbox1.Value
ActiveCell.EntireRow.Cells(1, 2).Value = textbox2.Value
ActiveCell.EntireRow.Cells(1, 3).Value = textbox3.Value[/VBA]
To search a value (id_no) you could use this :[VBA]
      Dim Result As Variant
      Dim Lookvalue As Long      
      Result = Empty
'xllastrow = function to look for last row
      With ActiveSheet.Range("a1:a" & xlLastRow)
   Lookvalue = TextBox1.Value 'value to look for
   Set Result = .Find(What:=Lookvalue, LookIn:=xlValues)
   If Not Result Is Nothing Then Result.Rows.EntireRow.Select Else Exit Sub
End With
'form to change the values in the row you just found
Row_found_that_we_want_to_change.Show

Charlize

[Petrogeo]

Thanks Charlize,

I will try to use your tips on my VBA... and change the TextBox "search" to a comboBox "selection". Do you think it?s possible??

cheers

[Zack Barresse]

Don't dimension range variables as variants. Do not do this...

Dim Result As Variant

Instead, do it like this...

Dim Result As Range

HTH

[Charlize]

A modified version with an 'Add' to show you the idea behind the rowno. Values entered in seqno and cabosno are read in a combobox so you can use them again or fill in a new value. Try to add some values.

When you click the button to view/add/change it will work starting from row 3.

Charlize

[ikk]

hello

i whas looking for something simillar like this dbase like thing , i tested it but the update function doesn't seem to bee working on the cells that need to be updated ? am i missing something ?
thanks for the help otherwise

[Petrogeo]

Hi,

I was testing the same function but it didn?t work either... I?m still trying to solve this problem... any other idea??

thanks in advance

[Charlize]

Tried the update/edit button on my version (adapted version of mdmackillop) and for me it works. Only for seqno and date (I believe) but if you add the code for the rest of the values it will work. Have you tried changing seqno and date ? Watch your sheet when you push the button.

Charlize

[Petrogeo]

I?m working on this... now I have a reasonable results and I can change several values.

[Petrogeo]

Does anybody know how to change the date format on the code?? mm/dd/yy to dd/mm/yy ????

[Zack Barresse]

Is it an actual date? If it is an excel recognized date, just use the Format() function. Post your code if not.

[Petrogeo]

The code is:

Private Sub UserForm_Initialize()
txtData.Value = Date
End Sub

How do i format date to dd/mm/yy ??

[Zack Barresse]

txtData.Value = Format(Date, "dd/mm/yy")

[Petrogeo]

Thanks Firefyrt....