Edit Form and update them in xlsheet

[suresh]

Hi Team,
Thanks for your continues help. I need your advices on couple of things:

1. In my previous thread (Progress bar help method not found (http://vbaexpress.com/forum/showthread.php?t=7343)) I have attached the spreadsheet and I would like to edit the Forms (ServerForm, NodeForm, AccountForm) and when I click on Done or OK button, it should ask for Save or Not.
When we click on save, it should be able to save the values in the specified column in the xlsheet.
To achieve this as initial step I made the properties of all text boxes Locked=False. Could you please guide me the next step?

2. Is it possible to display the Search results in a Form or in browser(IE/Mozilla) rather than in Target XLSheet?

Thanks,
Suresh.

[mdmackillop]

Hi Suresh,
Regarding part 1, here's one suggestion.
Add Tag values to each control equal to the spreadsheet offset. You can then loop through the controls using the tag values to write the results to the appropriate columns.
Something like

Sub FillData()
Dim c, i As Long
i = Worksheets("Servers").Columns("B:B").Find(What:=ServerName, _
After:=[B1], LookIn:=xlValues, _
LookAt:=xlWhole).Row
For Each c In Controls
If Not c.Tag = "" Then
Worksheets("Servers").Range("B1").Offset(i - 1, c.Tag) = c.text
End If
Next
End Sub

[XLGibbs]

Sight unseen, it is possible to display the results in a multi column List box..

instead of writing to a sheet, you would use the additem method to add the records to the list box.

[suresh]

Hi mdmackillop,
Thanks for your quick response. I am newbie to VBA. I am unable to embed this code. Could you please tell me where I need to include this code in in my form?

Hi Gibbs,
Thanks for the help. I was able to display the results in list format. Need to fine tune it.
Thanks,
Suresh.

[mdmackillop]

Hi Suresh,
The code should be pasted into the code area for the relevant form. A button can be added to the form to run the macro.
Regards
MD

[suresh]

Hi MD,
Thanks for your guidelines. I will try them today and update the status.

Thanks,
Suresh.

[mdmackillop]

Hi Suresh,
Your forms are quite complex, so let us know if you need assistance.
Regards
MD

[suresh]

Hi MD,
Sorry for my late response. I went to Grand Canyon with my friends :beerchug:
Looks like editing the appropriate cell through Form is biggest challenge for me :( I have tried in couple of methods but was not able to acheive the target result.
Here is the methods I have Tried.

Method1:
1. I have changed "Locked=False" to every Text box in the form
2. I gave the Tag values to every Text Box (Like B1:B25 for Server Name Text Box, C1:C25 for Server Ip Addresses,...etc)
3. Embedded your code in the following way:

I have created a DONE Button and embedded your code in "DoneButton_Click" procedure
I am getting the following error while debugging the code:
Complier Error:
For Each Control variable Must be Variant or Object (Got the error at "For Each c In Controls")

Method2:
1. I have changed "Locked=False" to every Text box in the form
2. I gave the Tag values to every Text Box (Like B1:B25 for Server Name Text Box, C1:C25 for Server Ip Addresses,...etc)
Private Sub UserForm_Initialize()
Dim myControl As MSForms.Control
Dim myLabel As MSForms.Label
'add all the textboxes to collection of cFormTextBox
For Each myControl In Me.Controls
'if its a textbox
If TypeName(myControl) = "TextBox" Then
'instance a new custom class
Set ctlTXT = New cFormTextBox
'pass the form and control to the new class
ctlTXT.Init myControl, Me
colTXT.Add ctlTXT
End If
Next
End Sub

In Method2, when I am trying to update the Server IP address Field for a specific server, It is updating the New IP address for ALL Servers??? I am with no idea on what to do now:dunno :help . Could you please guide me?
Once I am done with this, Hopefully will not bug you guys atleast for a month: pray2: .

Here I am sending the sample file. Now I am testing for only ServerForm. Please have alook at ServerForm.

Thanks in advance,
Suresh

[mdmackillop]

Hi Suresh
Because you are using offsets from B1 to fill your form, it makes sense to use the same reference to write edited data back to the sheet. I've therefore assigned a tag value equal to the offset in Sub Userform_Activate to each of the textboxes on the form.
If the contents of the textbox are NOT to be written to the worksheet, then the Tag should be deleted.
The code looks for the ServerrName in column B and returns this as a row number (i). It then cycles through each control, checking the Tag values. If it is not empty, it writes the cell contents into a cell offset from range B1 by (i-1) rows and (Tag) columns

For Each c In Me.Controls
If Not c.Tag = "" Then
Worksheets("Servers").Range("B1").Offset(i - 1, c.Tag) = c.text
End If
Next
Regarding the Compile error, whenever you use a For Each X in Y loop, X must always be declared a Variant, even if you know the data type is integer, long, string or whatever.
Either "Dim c as Variant", or simply "Dim c", as Variant is implied.
I've also tidied your Activate code to reduce the verbiage in filling the controls. It's not necessary to use the Userform name with each control on the same form, and the With statement omits repetition of the same range address.

With Worksheets("Servers").Range("B1")
ServerName.text = .Offset(i, 0).text 'Server Name
ServerIP.text = .Offset(i, 1).text 'Server IP
ServerClientIP.text = .Offset(i, 2).text 'Server Client IP
'.................
End With

Regards
MD

[suresh]

Hi MD,
Thanks somuch for your quick response. I will have a look at this and get back to you in couple of hours.

Thanks,
Suresh.

[suresh]

Hi MD,
Thanks somuch for your kind help.
Your magic is perfectly working for me. I need to test this for other forms. At any rate, when I test your sample file, I am able to edit the sheets through the form.
But when I try the same with my production version, I am able to edit the form but with a small error:

run time error '438'
Object doesn't support this property or method.

I am getting error in the highlighted line.

For Each c In Me.Controls
If Not c.Tag = "" Then
Debug.Print c.Tag
Worksheets("Servers").Range("B1").Offset(i - 1, c.Tag) = c.text

Could you please tell me why I am getting this error?
I think I am missing something in the production version??

Thanks in advance,
suresh.

[mdmackillop]

Hi Suresh
Since the debug.print is there, check the output in the immediate window. I suspect that one of your tags is not a number.

[suresh]

Hi MD,
Your suspect was 200% correct. Few of our tags were not numbers and I have corrected the tags and now it is working perfectly :thumb Thanks somuch for your help.
I have one more quick help :help
Our existing form will edit/update the existing information(based on the first cell, server Name).
If we want to create a NEW server details with that form, how could we achieve?? Do we need to create a seperate form with the new tag values?
or is there any way to create a new server information with our existing form?
Just outof curious, How long you been working on VBA? Because I am wondering you are resolving the issues in minutes which I am unable to solve for days :(

Thanks in advance.
Suresh.

[mdmackillop]

Hi Suresh
There is no reason why you can't add new servers in this way. The code searches for the servername in column B, if it's not found, then the new server could most easily be added at the end of the list. It could also be inserted at a designated position, depending upon your criteria


Private Sub DoneButton_Click()
Dim Ans As Integer
Dim i As Long
Dim c As Variant
'If UpdateFlag = 1 Then
' MsgBox (i)
' Ans = MsgBox("Ok to save changes", vbYesNo, "Save Changes")
' If Ans = vbYes Then
'Sub FillData()
On Error Resume Next
i = Worksheets("Servers").Columns("B:B").Find(What:=ServerName, _
After:=[B1], LookIn:=xlValues, LookAt:=xlWhole).Row
'If server name is not found then get number of last row + 1
If Err = 91 Then
i = Worksheets("Servers").Cells(Rows.Count, "B").End(xlUp).Row() + 1
Cells(i, 1).Value = Cells(i - 1, 1) + 1
End If
For Each c In Me.Controls
If Not c.Tag = "" Then
Debug.Print c.Tag
Worksheets("Servers").Range("B1").Offset(i - 1, c.Tag) = c.text
End If
Next

Unload ServerForm

End Sub


Re point 2, I did a little VBA in Access by learning to manipulate example files, and Excel using the recorder function, but with little real understanding of either. I started trying to answer questions in Experts Exchange in Nov 03 until I relocated to this forum. I really learned all my VBA by using the help files to answer questions for others, and by examining the real experts' solutions, but I'm sure that a structured course is the best way to go, especially if you need to get there quickly.
I'm still learning, and there are areas of VBA, like Classes where I've never really ventured.
Regards
MD

[suresh]

Hi MD,
Thanks MD. Everything is working cool:thumb . Good to know that you are good at Access and VBA. Thank god I have a friend who is good at many technologies:beerchug: .
Sorry to keep this thread for long time even the issues are resolved with your help. I just got couple more questions in my mind.: pray2:

I need your help in couple more aspects.:help

1. Is there any way to update the Last update field(In Nodes table), whenever a record gets updated. This should happen automatically.

2. Is there any way to write a Macro to read stuff right into the tables(Nodes and UpdateList worksheets). Should have files that are comma delimited. (servername, nodename).
Macro would read in file. If matches it would read in data. If it doesn?t match a new record would be created in Nodes table.

Forexample:
We have 2 worksheets called Nodes, UpdateList.
When we ran the macro UpdateList Records need to compare with the Nodes Records.
If there is any NEW record in UpdateList, That record need to be added to Nodes tables.
If there is a Record in Nodes sheet but not in UpdateList, That record should be deleted from Nodes worksheet.

I dont know whether I have explained in good way or not. please give me some advices to achive this.

Thanks,
Suresh.

[suresh]

Hi MD,
Many Happy returns of the day. I wish you a very happy Birth day.

Thanks,
Suresh.

[suresh]

Hi Team,
Thanks somuch for your help. I am going to close this thread, since the initial problem as I mentioned in the subject line is resolved. May be in couple of days, I will come up with clear picture of the updating xlsheet with new values. I will ask your help, If need anything from you guys.

My special thanks to MD, Gibbs and Rembo. :friends:

Thanks,
Suresh.