Sleeper: UserForm with Feed back from the Excel Sheet

[cortiz1bog]

Hello,

I am new at the forum, I am trying to use a Userform to input info into an excel sheet, at some point, I need to get informacion back from the excel sheet and into the Userform text fields in order for the user to know if the information that user just enter is correct, otherwise to change some of the fields.

I am attaching to code and place where I have the questios:

Hope my first post work and looks as clean as everybody, thanks


Private Sub CmdAceptar_Click()
ActiveWorkbook.Sheets("EGRESOS").Activate
Range("N182").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = TxtFecha.Value
ActiveCell.Offset(0, 1) = TxtIDProveedor.Value
ActiveCell.Offset(0, 3) = TxtFactura.Value
ActiveCell.Offset(0, 4) = TxtCodigo1.Value
ActiveCell.Offset(0, 6) = TxtNoGravado1.Value
ActiveCell.Offset(0, 7) = TxtGravado1.Value
ActiveCell.Offset(0, 8) = TxtIva1.Value
ActiveCell.Offset(0, 12) = TxtCodigo2.Value
ActiveCell.Offset(0, 14) = TxtNoGravado2.Value
ActiveCell.Offset(0, 15) = TxtGravado2.Value
ActiveCell.Offset(0, 16) = TxtIva2.Value
ActiveCell.Offset(0, 20) = TxtCodigo3.Value
ActiveCell.Offset(0, 22) = TxtNoGravado3.Value
ActiveCell.Offset(0, 23) = TxtGravado3.Value
ActiveCell.Offset(0, 24) = TxtIva3.Value
Active.....????....TxtFeedbackTotal = ""
Active.....????....TxtFeedbackProveedor = ""


Here is where I need some help:
In order to validate the information just enter on the above fields I nedd to get feedback from the excel sheet and I do not know how to do it ? so I have the two text fields as you see ?FeedbackTotal and Feedback Proveedor? to have the resulting information from the sheet to be ENTER and be SHWON on the Userform on those two text boxes, as you can imagine the info coming back to the text boxes belong to the line just enter with .
The cells location are:


For the, TxtFeedbackTotal:
Range("N182").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select

To the cell at the left of the above position (Colunm M), not sure if the term ?ActiveCell.Offset(1, (minus) -1).Select

? may qualify as the correct form of explaining it

For the, TxtFeedbackProveedor:


Range("N182").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 2).Select

Once the information comes back from the excel sheet the user can decide wheather to go back and change any of the above fields or to continue to press the Aceptar (OK) button to get the next to two MSGBoxes and on and on

(Call Cell Location Total -1 and Proveedor +2) target



MsgBox "Se Incluyo Esta Informacion" (the information has been enter)
Response = MsgBox("Quiere Segir Entrando Recibos?", vbYesNo)
'(Do you want to continue with another record?)
If Response = vbYes Then
TxtFecha.Value = ""
TxtIDProveedor.Value = ""
TxtFactura.Value = ""
TxtCodigo1.Value = ""
TxtNoGravado1.Value = ""
TxtGravado1.Value = ""
TxtIva1.Value = ""
TxtCodigo2.Value = ""
TxtNoGravado2.Value = ""
TxtGravado2.Value = ""
TxtIva2.Value = ""
TxtCodigo3.Value = ""
TxtNoGravado3.Value = ""
TxtGravado3.Value = ""
TxtIva3.Value = ""
TxtFecha.SetFocus
Else
Unload Me
End If
End Sub

Private Sub CmdCancelar_Click()
Unload Me
End Sub


Need help here too
As I Started to build the form the, the botton "Borrar" (Clear form) was working just fine, now I keep geting error 424 time execution something and can not get pass it
Need some suggestions ? the idea is the clear, in case the info need to be clear and re-start the form
See bellow:


Private Sub CmdClearForm_Click()
Call UserForm.Initialize
End Sub

Private Sub UserForm1_Initialize()
TxtFecha.Value = ""
TxtIDProveedor.Value = ""
TxtFactura.Value = ""
TxtCodigo1.Value = ""
TxtNoGravado1.Value = ""
TxtGravado1.Value = ""
TxtIva1.Value = ""
TxtCodigo2.Value = ""
TxtNoGravado2.Value = ""
TxtGravado2.Value = ""
TxtIva2.Value = ""
TxtCodigo3.Value = ""
TxtNoGravado3.Value = ""
TxtGravado3.Value = ""
TxtIva3.Value = ""
End With
TxtFecha.SetFocus
End
End Sub

From theabove questions, Do I need anything in here?
I know _Change is not the correct form, any ideas?
Do I really need it?


Private Sub TxtFeedbackTotal_Change()
End Sub

Private Sub TextFeedbackProveedor_Change()
End Sub


Private Sub UserForm_Click()
End Sub


Code for the Module


Option Explicit

Sub Show_UserForm()
UserForm1.Show
End Sub


For future reference, how do I get the Code onto the nice litlle greeen square?
I Try the sign VBA but did not work

Thank you

Cesar

[Jacob Hilderbrand]

To format the code like it looks in the VBE use VBA tags.

Put (VBA) at the start of the code and (/VBA) at the end of the code. Just use square brackets [] instead of parentheses.

[johnske]

....For future reference, how do I get the Code onto the nice litlle greeen square?
I Try the sign VBA but did not work

Thank you

CesarHi Cesar, welcome to VBAX.

I've edited your post to include VBA tags, yes, you use the sign VBA but you must select what you want included as code first.

Alternatively, write vba (enclosed in square brackets) at the start of your code and /vba (also enclosed in square brackets) at the end of your code.

HTH,
John

[xCav8r]

Cesar, :hi:

Bienvenido a VBAX

You write in English very well, but some of the questions you are asking are unclear to me (and I think to others as well). I'm wondering if language might be somewhat of a barrier. That said, I'll hold off to see if others can figure out what you want to do and help you. If that doesn't work, however, I volunteer to translate your post from Spanish to English. We've got a lot of smart people here that know Excel, so I'm sure once they understand what you want to do...

[Steiner]

I'm not sure whether I understood you, but I'll give it a try:

1. to put the value of the cell to the left of the current cell into a textbox called TxtFeedbackTotal you'll just need:


TxtFeedbackTotal.Text = ActiveCell.Offset(0,-1).Value

2. The error you get with your clear button might show that a certain textbox you're trying to reset doesn't exist anymore. Maybe a typo or you deleted it?

Daniel

[cortiz1bog]

Thanks for the pointers

And thank you xCav8r for volunteering to translate, here we go:

1-Aqu? es donde necesito ayuda:
Para poder validar que la informacion que se puso dentro de las txtBoxes, desde TxtFecha.Value hasta TxtIva3.Value, necesito mostrar el resultado de la hoja de calculo de dos celdas en la fila(Row) que se acabo de entrar y mostrarselo al usuario en la Userform en TxtFeedbackTotal y en TxtFeedbackProveedor. Antes de que CmdAceptar (OK) cierre la forma, PERO una vez la informacion sea entrada en la hoja de calculo, esta informacion pedidara comparar el Total y el Proveedor con la informacion que el usuario puede comprobar de la fuente de informacion que es un Recibo de Pago. Asi que nunca se sabe exactamente en que celda numero de la columna M se encuentre y se muestre el Total en el Userform, como tampoco el numero de la celda de la columna P se encuentre y se muestre el Proveedor en el Userform.
Tampoco se que debo usar para que el Total y Proveedor se muestren en el Userform

2-Una vez que la informacion vuelva de la hoja de calculo, el usuario podra decidir si debe corregir algo en las TxtBoxes campos o continuar , ahora me doy cuenta que los MsgBox, de pronto deben ir en otro orden, o agregar uno que pida si desea continuar o algo similar

3-Cuando empece a hacer la forma, el boton Borrar(clear form) funcionaba bien, ahora no se porque encuentro error 424 tiempo de ejecucion y no puedo pasar de este punto.

4-De la primer pregunta, tengo que agregar algo mas de codigo, Aqu??
Yo se que, _Change no es correcto debo usar _AfterUpdate, ideas?
Que es, lo que realmente necesito?
Para estas dos txtboxes : TxtFeedbackTotal y TextFeedbackProveedor

Thanks Steiner, I will look into, kind of tire for today, I will check on tomorrow, 99

Cesar

[cortiz1bog]

I made some changes to the code as suggested by Steiner - Now I am getting the feed back from excel-sheet to the Userform that I need.

Of course, now I have more questions


Private Sub CmdAceptar_Click()
ActiveWorkbook.Sheets("EGRESOS").Activate
Range("N182").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = TxtFecha.Value
ActiveCell.Offset(0, 1) = TxtIDProveedor.Value
ActiveCell.Offset(0, 3) = TxtFactura.Value
ActiveCell.Offset(0, 4) = TxtCodigo1.Value
ActiveCell.Offset(0, 6) = TxtNoGravado1.Value
ActiveCell.Offset(0, 7) = TxtGravado1.Value
ActiveCell.Offset(0, 8) = TxtIva1.Value
ActiveCell.Offset(0, 12) = TxtCodigo2.Value
ActiveCell.Offset(0, 14) = TxtNoGravado2.Value
ActiveCell.Offset(0, 15) = TxtGravado2.Value
ActiveCell.Offset(0, 16) = TxtIva2.Value
ActiveCell.Offset(0, 20) = TxtCodigo3.Value
ActiveCell.Offset(0, 22) = TxtNoGravado3.Value
ActiveCell.Offset(0, 23) = TxtGravado3.Value
ActiveCell.Offset(0, 24) = TxtIva3.Value
MsgBox "Se Incluyo Esta Informacion"
TxtFeedbackTotal.Text = ActiveCell.Offset(0, -1).Value
TxtFeedbackProveedor.Text = ActiveCell.Offset(0, 2).Value

At this point:
If the User reallize that the information just enter is incorrect and need to go back and make some changes, how can I go back to the same information and add/edit/change ? and continue ...

I try to put a MsgBox to Validate the information, but could not get it to work, never mind to go back and edit the same info, at some point I was able to go back, but the result was to input a new record-entry, could not get back and edit the same fields.

The only MsgBox tha let me work and continue was the vbOk as you can see on the rest of the code.
Any more suggestions?


Response = MsgBox("Est? la Informaci?n Correctamente Entrada? Coincide el Total?", vbOKOnly)
Response = MsgBox("Quiere Segir Entrando Recibos?", vbYesNo)
If Response = vbYes Then
TxtFecha.Value = ""
TxtIDProveedor.Value = ""
TxtFactura.Value = ""
TxtCodigo1.Value = ""
TxtNoGravado1.Value = ""
TxtGravado1.Value = ""
TxtIva1.Value = ""
TxtCodigo2.Value = ""
TxtNoGravado2.Value = ""
TxtGravado2.Value = ""
TxtIva2.Value = ""
TxtCodigo3.Value = ""
TxtNoGravado3.Value = ""
TxtGravado3.Value = ""
TxtIva3.Value = ""
TxtFeedbackTotal = ""
TxtFeedbackProveedor = ""
TxtFecha.SetFocus
Else
Unload Me
End If
End Sub

Private Sub CmdBorrarForm_Click()
Call UserForm.Initialize
End Sub

Private Sub CmdCancelar_Click()
Unload Me
End Sub

Private Sub UserForm1_Initialize()
TxtFecha.Value = ""
TxtIDProveedor.Value = ""
TxtFactura.Value = ""
TxtCodigo1.Value = ""
TxtNoGravado1.Value = ""
TxtGravado1.Value = ""
TxtIva1.Value = ""
TxtCodigo2.Value = ""
TxtNoGravado2.Value = ""
TxtGravado2.Value = ""
TxtIva2.Value = ""
TxtCodigo3.Value = ""
TxtNoGravado3.Value = ""
TxtGravado3.Value = ""
TxtIva3.Value = ""
TxtFeedbackTotal = ""
TxtFeedbackProveedor = ""
TxtFecha.SetFocus
End
End Sub



I must say that I am making some progress and feel much better with the help that you folks are giving me, thanks

Cesar

[Steiner]

I'm not sure whether I got your problem correct, but why don't try the following approach:

1. let the user enter data into the userform
2. transfer the data to the worksheet (I guess you need this for step 3 so you don't need to do calculation in the form)
3. transfer some data from the worksheet to the userform
4. ask the user if it's Ok (yes + no + cancel)
5a - yes: go to the next record
5b - no : call exit sub to end processing, so the record is still the same and the data is still in the userform, so the user can correct it without having to input all infos again
5c - cancel: rollback, clear the information you just put into the worksheet and all information in the userform

Daniel

[cortiz1bog]

Thanks for your answer Steiner,
Yes, you put it so simple, I wish I have asked the questions this way, now I know for the future.
I need help with the code for the following questions:
4, 5B an 5C - also, at some point I need to save the date in the workbook, thx again.
Cesar

[Steiner]

The above was just meant as a proposal on a possible approach to solve your problem, because I just did not know whether I got the problem right at all.

[Steiner]

I put up a small example, because your form is just too much for testing:bug: ). The basic code in the form is this:


Private Sub CommandButton1_Click()
Range("A2").Select
ActiveCell.Offset(0, 0).Value = TextBox1.Text
ActiveCell.Offset(0, 1).Value = TextBox2.Text
TextBox3.Text = ActiveCell.Offset(0, 2).Text
Select Case MsgBox("Is this Ok?", vbYesNoCancel, "Question")
Case vbYes
'Everything ok, leave data in Worksheet and close Form
Unload Me
Case vbNo
'User wants to correct the data
'leave the data right where it is, but don't close the form
Exit Sub
Case vbCancel
'User wants to abort entry
'clear the data in the worksheet and close form
ActiveCell.Offset(0, 0).Value = ""
ActiveCell.Offset(0, 1).Value = ""
Unload Me
End Select
End Sub

Maybe you can use this as a starting point. Once such a small example is working, you can try it on your project.

Daniel

[cortiz1bog]

Thanks Steiner I will try it and let you guys know how it turn out

Cesar

[cortiz1bog]

Thanks for the idea Steiner, I when back make some changes as per your suggestions and now is working just find.
I took me a little while, since I am just starting to lear how the code works, if you could or any body else, how can I clean up the code, that will really help me with my progress, thanks.

Here is the code and it works:


Private Sub CmdAceptar_Click()
ActiveWorkbook.Sheets("EGRESOS").Activate
Range("N65536").Select
Selection.End(xlUp).Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Offset(0, 0).Value = TxtFecha.Text
ActiveCell.Offset(0, 1).Value = TxtIDProveedor.Text
ActiveCell.Offset(0, 3).Value = TxtFactura.Text
ActiveCell.Offset(0, 4).Value = TxtCodigo1.Text
ActiveCell.Offset(0, 6).Value = TxtNoGravado1.Text
ActiveCell.Offset(0, 7).Value = TxtGravado1.Text
ActiveCell.Offset(0, 8).Value = TxtIva1.Text
ActiveCell.Offset(0, 12).Value = TxtCodigo2.Text
ActiveCell.Offset(0, 14).Value = TxtNoGravado2.Text
ActiveCell.Offset(0, 15).Value = TxtGravado2.Text
ActiveCell.Offset(0, 16).Value = TxtIva2.Text
ActiveCell.Offset(0, 20).Value = TxtCodigo3.Text
ActiveCell.Offset(0, 22).Value = TxtNoGravado3.Text
ActiveCell.Offset(0, 23).Value = TxtGravado3.Text
ActiveCell.Offset(0, 24).Value = TxtIva3.Text
TxtFeedbackTotal.Text = ActiveCell.Offset(0, -1).Text
TxtFeedbackProveedor.Text = ActiveCell.Offset(0, 2).Text
TxtFecha.SetFocus
MsgBox "Se Incluyo Esta Informacion"
Select Case MsgBox("Is this Ok?", vbYesNoCancel, "Question")
Case vbYes
'Everything ok, leave data in sheets ("EGRESOS")
Response = MsgBox("Quiere Segir Entrando Recibos?", vbYesNo)
If Response = vbYes Then
TxtFecha.Text = ""
TxtIDProveedor.Text = ""
TxtFactura.Text = ""
TxtCodigo1.Text = ""
TxtNoGravado1.Text = ""
TxtGravado1.Text = ""
TxtIva1.Text = ""
TxtCodigo2.Text = ""
TxtNoGravado2.Text = ""
TxtGravado2.Text = ""
TxtIva2.Text = ""
TxtCodigo3.Text = ""
TxtNoGravado3.Text = ""
TxtGravado3.Text = ""
TxtIva3.Text = ""
TxtFecha.SetFocus
Else
Unload Me
End If
Case vbNo
'User wants to correct the data
'leave the data right where it is, but don't close the form
'clear the data in the sheets ("EGRESOS")
ActiveCell.Offset(0, 0).Clear
ActiveCell.Offset(0, 1).Clear
ActiveCell.Offset(0, 3).Clear
ActiveCell.Offset(0, 4).Clear
ActiveCell.Offset(0, 6).Clear
ActiveCell.Offset(0, 7).Clear
ActiveCell.Offset(0, 8).Clear
ActiveCell.Offset(0, 12).Clear
ActiveCell.Offset(0, 14).Clear
ActiveCell.Offset(0, 15).Clear
ActiveCell.Offset(0, 16).Clear
ActiveCell.Offset(0, 20).Clear
ActiveCell.Offset(0, 22).Clear
ActiveCell.Offset(0, 23).Clear
ActiveCell.Offset(0, 24).Clear
TxtFecha.SetFocus
Exit Sub
Case vbCancel
'User wants to abort entry
'clear the data in the sheets ("EGRESOS") and close form
ActiveCell.Offset(0, 0).Clear
ActiveCell.Offset(0, 1).Clear
ActiveCell.Offset(0, 3).Clear
ActiveCell.Offset(0, 4).Clear
ActiveCell.Offset(0, 6).Clear
ActiveCell.Offset(0, 7).Clear
ActiveCell.Offset(0, 8).Clear
ActiveCell.Offset(0, 12).Clear
ActiveCell.Offset(0, 14).Clear
ActiveCell.Offset(0, 15).Clear
ActiveCell.Offset(0, 16).Clear
ActiveCell.Offset(0, 20).Clear
ActiveCell.Offset(0, 22).Clear
ActiveCell.Offset(0, 23).Clear
ActiveCell.Offset(0, 24).Clear
TxtFecha.SetFocus
Unload Me
End
End Select
End Sub


Thanks again

Cesar

[Steiner]

One improvement could be to place the whole list of Offset.Clear commands into the own method. You then could simplay call this method instead of the whole list of Clear-Commands.
Saves you some typing and more importantly it saves you from errors which may result when you decide to add a new cell and forget to change all places where you clear the cells.

Daniel

[cortiz1bog]

Can you give a idea how to put it into single method - I may know how to call it but, writiting the code is where I get into trouble, a simple example may do, thanks
Cesar

[Steiner]

It's just a small improvement (maybe I did not express it right) but might save some trouble later on:[VBA]


Private Sub CmdAceptar_Click()
ActiveWorkbook.Sheets("EGRESOS").Activate
Range("N65536").Select
Selection.End(xlUp).Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Offset(0, 0).Value = TxtFecha.Text
ActiveCell.Offset(0, 1).Value = TxtIDProveedor.Text
ActiveCell.Offset(0, 3).Value = TxtFactura.Text
ActiveCell.Offset(0, 4).Value = TxtCodigo1.Text
ActiveCell.Offset(0, 6).Value = TxtNoGravado1.Text
ActiveCell.Offset(0, 7).Value = TxtGravado1.Text
ActiveCell.Offset(0, 8).Value = TxtIva1.Text
ActiveCell.Offset(0, 12).Value = TxtCodigo2.Text
ActiveCell.Offset(0, 14).Value = TxtNoGravado2.Text
ActiveCell.Offset(0, 15).Value = TxtGravado2.Text
ActiveCell.Offset(0, 16).Value = TxtIva2.Text
ActiveCell.Offset(0, 20).Value = TxtCodigo3.Text
ActiveCell.Offset(0, 22).Value = TxtNoGravado3.Text
ActiveCell.Offset(0, 23).Value = TxtGravado3.Text
ActiveCell.Offset(0, 24).Value = TxtIva3.Text
TxtFeedbackTotal.Text = ActiveCell.Offset(0, -1).Text
TxtFeedbackProveedor.Text = ActiveCell.Offset(0, 2).Text
TxtFecha.SetFocus
MsgBox "Se Incluyo Esta Informacion"
Select Case MsgBox("Is this Ok?", vbYesNoCancel, "Question")
Case vbYes
'Everything ok, leave data in sheets ("EGRESOS")
Response = MsgBox("Quiere Segir Entrando Recibos?", vbYesNo)
If Response = vbYes Then
TxtFecha.Text = ""
TxtIDProveedor.Text = ""
TxtFactura.Text = ""
TxtCodigo1.Text = ""
TxtNoGravado1.Text = ""
TxtGravado1.Text = ""
TxtIva1.Text = ""
TxtCodigo2.Text = ""
TxtNoGravado2.Text = ""
TxtGravado2.Text = ""
TxtIva2.Text = ""
TxtCodigo3.Text = ""
TxtNoGravado3.Text = ""
TxtGravado3.Text = ""
TxtIva3.Text = ""
TxtFecha.SetFocus
Else
Unload Me
End If
Case vbNo
'User wants to correct the data
'leave the data right where it is, but don't close the form
'clear the data in the sheets ("EGRESOS")
ClearWsEntries
TxtFecha.SetFocus
Exit Sub
Case vbCancel
'User wants to abort entry
'clear the data in the sheets ("EGRESOS") and close form
ClearWsEntries
TxtFecha.SetFocus
Unload Me
End
End Select
End Sub

Private Sub ClearWsEntries()
ActiveCell.Offset(0, 0).Clear
ActiveCell.Offset(0, 1).Clear
ActiveCell.Offset(0, 3).Clear
ActiveCell.Offset(0, 4).Clear
ActiveCell.Offset(0, 6).Clear
ActiveCell.Offset(0, 7).Clear
ActiveCell.Offset(0, 8).Clear
ActiveCell.Offset(0, 12).Clear
ActiveCell.Offset(0, 14).Clear
ActiveCell.Offset(0, 15).Clear
ActiveCell.Offset(0, 16).Clear
ActiveCell.Offset(0, 20).Clear
ActiveCell.Offset(0, 22).Clear
ActiveCell.Offset(0, 23).Clear
ActiveCell.Offset(0, 24).Clear
End Sub

Daniel

[cortiz1bog]

Thank you so very much, that did it, it works great...
Using the same context I have other forms, everything is just find

Since my workbook is a little heavy, if I transfer everything to Access would the same forms would work?...
Yes I understand I will have to make some changes to call call Access and the different forms and sheets but can excel forms, work to enter and edit data into Access?

Cesar

[Steiner]

Not so easily. For example all references to a worksheet (e.g. the whole ActiveCell.Offset stuff) won't work, as Access uses a completely different structure, Access simply does not know what a worksheet might be (unless you use Access to enter data into an Excel sheet, but that's a different story). It might be easier to build whole new forms in Access than trying to transfer the old ones.

Daniel

[cortiz1bog]

Thanks for the Heads-up...
I will let you know what comes out with new forms in Access, keep in touch

C/