PDA

View Full Version : Changing data selected in a UserForm



Petrogeo
12-15-2006, 09:46 AM
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
12-15-2006, 05:33 PM
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
12-16-2006, 09:33 AM
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
12-16-2006, 09:49 AM
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
12-16-2006, 10:37 AM
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
12-16-2006, 11:20 AM
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
12-17-2006, 07:16 PM
"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
12-18-2006, 05:33 AM
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
12-18-2006, 12:20 PM
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
12-18-2006, 12:45 PM
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
12-18-2006, 03:23 PM
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
12-19-2006, 12:25 PM
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
12-19-2006, 12:58 PM
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
12-19-2006, 04:12 PM
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
12-20-2006, 09:49 AM
I?m working on this... now I have a reasonable results and I can change several values.

Petrogeo
01-03-2007, 11:46 AM
Does anybody know how to change the date format on the code?? mm/dd/yy to dd/mm/yy ????

Zack Barresse
01-03-2007, 11:56 AM
Is it an actual date? If it is an excel recognized date, just use the Format() function. Post your code if not.

Petrogeo
01-03-2007, 12:42 PM
The code is:


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

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

Zack Barresse
01-03-2007, 01:58 PM
txtData.Value = Format(Date, "dd/mm/yy")

Petrogeo
01-04-2007, 10:14 AM
Thanks Firefyrt....:) :)

Petrogeo
01-09-2007, 05:19 AM
I used the code above but the format still in mm/dd/yy on the cell when I click OK button.


Private Sub UserForm_Initialize()
txtData.Value = Format(Date, "dd/mm/yy")
End Sub

Private Sub btnOK_Click()
ActiveWorkbook.Sheets("Seq").Activate
ActiveCell.Value = txtData.Value
End Sub


Help!:bug: :bug:

Charlize
01-09-2007, 05:31 AM
use a variable declared as date
dim dt as date
dt = format(txtdata.value,"dd/mm/yy")
activecell.value = dt
I think that when you format a date in a textbox it becomes a string (even if you gave the format of the date to fill the textbox). When you rewrite to the sheet you must define the type of data (date in this case) to be sure to get the value that you want (If the country where you lives uses a different notation then mm/dd/yy you will have to think about this.).

Charlize

moa
01-09-2007, 06:16 AM
txtData.Value = Format$(Date, "dd/mm/yy")

moa
01-09-2007, 06:19 AM
Please ignore above, something funny going on...

What I meant to write was:

Have you formatted the cell?

Charlize
01-09-2007, 06:37 AM
Try this to clarify what I mean. When the date of your computer is formatted as dd/mm/yy, excel will switch day and month when your day is less then 13 (because there are 12 months in a year) when you use a string declared variable.
Sub test_date()
Dim dt1 As Date
Dim dt2 As String
'give dt1 and dt2 the same value (09/01/2007)
'and watch your sheet when putting values in sheet
dt1 = InputBox("Give date dd/mm/yy", "Give date")
dt2 = InputBox("Give date dd/mm/yy", "Give date")
ActiveCell.Value = dt1
ActiveCell.Offset(0, 1).Value = dt2
End Sub

Petrogeo
01-09-2007, 01:02 PM
Thanks, Charlize and Moa... it worked fine... :beerchug:

rgds
Petrogeo

Petrogeo
03-05-2007, 12:49 PM
Hi Guys,
I can't alternate (ALT+Tab) to another excel worksheet during I'm using the userform in my worksheet. Does anybody have a solution for this???
rgds,
Petrogeo

Zack Barresse
03-05-2007, 01:59 PM
Hi there,

If you have Excel 2000 or higher, make the userform Modeless (when you Show it).

Charlize
03-05-2007, 02:28 PM
ieuserform1.show 0