How Do I Modify a Record from a UserForm

[Minitman]

Greetings

I have a UserForm that a user fills out customer information into,
When completed, user hits a Finished button to copy and paste the
information to the bottom of a LONG list.

However, sometimes I need to modify a current customers information
from that same list, make corrections and then copy it over the
original record.

I can't figure out how to do that! It is easy to copy the info in the
UserForm to the first available row at the bottom of the list, but how
do I find the row that this record came from and paste this modified
version there.

Any help would be most appreciated.

TIA

-Minitman

 

[AxeldraX]

you select your data and then use the built-inn form with
Range("A1:C4").Select 'select your data
ActiveSheet.ShowDataForm
it's a simple way to manage your dat

 

[Minitman]

Hey AxeldraX,

Thanks for the reply, however I think you misunderstood what I am
trying to do.

I have a workbook with 3 sheets in it, One is called "Enter", one is
called "INV" (this is the storage sheet for the invoices) and the last
is called "CI" (this is the storage sheet for the customer
information).

The sheet called "Enter" is setup to look like an invoice with a red
command button called "Next Invoice".

To start an invoice, you first press the "Next Invoice" button. This
bring up a UserForm that is designed as a menu, with 8 big command
buttons to choose from. We choose the "Make New Invoice" button.

The menu disappears and a different UserForm pops up. It is looking
for four things: an invoice number, any problems with this invoice
(void, missing or canceled), a service date and a drop down list to
choose an existing customer from a list. Once you have found the
customer in the list that you want to work with, you push one of the
exposed buttons at the button of the form. There are 2 buttons to
choose from, "Match Found" and "No Match Found".

"Match Found" kicks you out of the UserForm and back to "Enter" to
complete the invoice. This is working fine, It is the other choice
that has the problem.

"No Match Found" exposes 2 other buttons, "Create A New Customer
Record" and "Modify An Existing Customer Record".

"Create A New Customer Record" works fine. When you finish filling in
the customers info, you push the "Finished" button and the code copies
it at the bottom of the list in "CI" and then makes a copy of the
first three columns and places this copy on a different page (called
"Lists") and then sorts those three columns. This is also working
fine.

It is this last choice that is giving me a problem. The code brings
the customer information into the UserForm, just like it is supposed
to. I correct the errors and then comes the problem. I need to put
this corrected record into the same row that the incorrect record is
still sitting. I can not see how to do that. All the code I have
seen shows me how to place this record at the bottom of the stack,
which will give me TWO of this record, one corrected and one still
incorrect. This is the way the code is set up now and I need to
change it.

If anyone would like a copy of this workbook to look at and possibly
come up with a solution, I would be more then happy to send one.
Just let me know.

As always, thank you for your time and assistance.

-Minitman
(e-mail address removed)
(remove the NOSPAM to send)

 

[Tom Ogilvy]

You just need to remember where you got the data from. Place this in a
module level variable in the userform. Then use this information to write
back the data. (although it is unclear how, if you don't match the customer
in the database, how you decide which customer needs to be edited).

 

[Minitman]

Hey Tom,

Good to hear from you again.

I kind a figured that was where I was going to get the figures that I
needed.

I guess the question is, how to get my variables to be seen in all of
my open UserForms? I know it has something to do at what level they
are - which is where my knowledge begins to fall short of what is
needed and the help section is a bit lacking in explanation.

If you would like, I can send you a copy of this workbook for you to
look at it. It would be a lot easier then trying to describe how it
is supposed to work.

Thanks

-Minitman

 

[Tom Ogilvy]

If you want multiple userforms in the project to see the variable then
declare it as public at the top if a general/Standard Module. With the
project the active project, Insert =>Module (just to clarify).

then at the top, outside any procedure

Public srcRange as Range

as an example.

Now set it when you load the data and when you want to write it back, use
that as your reference.

 

[Minitman]

Hey Tom,

I must not have understood what you said, I keep getting

Run-time error '91'
Object variable or With block variable not set

My VBA Project tree looks like this:

[-] VBA Project (Invoice Enter.xls)
[-] Microsoft Excel Objects
Sheet 1 (Enter)
Sheet 2 (CI)
Sheet 3 (INV)
Sheet 4 (Lists)
ThisWorkbook
[-] Forms
MakeNewCustInfoForm
MakeNewInvForm1
MenuForm
[-] Modules
Module1
Module2
Module3

The variables "foundcell" and "LastCell" are located in the Form
"MakeNewInvForm1". They are needed in "MakeNewCustInfoForm". I
tried to make "foundcell" and "LastCell" global by putting this

Public LastRow As Integer
Public foundcell As Range

Into Module3, as I thought you recomended.

I modified some code that is used to place the customer infomation at
the bottom of the list in sheet CI but all I get is this error '91'.

Here is the original code:

Sheets("CI").Activate
With Range("A65536").End(xlUp).Offset(1, 1)
.Value = MakeNewInvForm1.EnterDate_TB_02.Text
.Offset(0, 1).Value = MakeNewInvForm1.EnterInvNo_TB_01.Text
.Offset(0, 3).Value = CI_TextBox_3.Text
.Offset(0, 4).Value = CI_TextBox_4.Text

And this is how I modified it:

Set foundcell = Range("CI!A1:CI!A" &LastRow).Find _
(MakeNewInvForm1.GetCustInfo_ListBox_01_04, , xlValues)
Sheets("CI").Activate
With foundcell.Offset(0, 1)
.Value = MakeNewInvForm1.EnterDate_TB_02.Text
.Offset(0, 1).Value = MakeNewInvForm1.EnterInvNo_TB_01.Text
.Offset(0, 3).Value = CI_TextBox_3.Text
.Offset(0, 4).Value = CI_TextBox_4.Text

Can anyone tell me what I did wrong?

Any help would be most appreciated.

TIA

-Minitman

 

[Minitman]

Opps. I called one of variables LastCell when it is really called
LastRow. That was a typo only in this posting, not in the code.
sorry for the confusion.

The problem is still unsolved.

-Minitman

Hey Tom,

I must not have understood what you said, I keep getting

Run-time error '91'
Object variable or With block variable not set

My VBA Project tree looks like this:

[-] VBA Project (Invoice Enter.xls)
[-] Microsoft Excel Objects
Sheet 1 (Enter)
Sheet 2 (CI)
Sheet 3 (INV)
Sheet 4 (Lists)
ThisWorkbook
[-] Forms
MakeNewCustInfoForm
MakeNewInvForm1
MenuForm
[-] Modules
Module1
Module2
Module3

The variables "foundcell" and "LastCell" are located in the Form
"MakeNewInvForm1". They are needed in "MakeNewCustInfoForm". I
tried to make "foundcell" and "LastCell" global by putting this

Public LastRow As Integer
Public foundcell As Range

Into Module3, as I thought you recomended.

I modified some code that is used to place the customer infomation at
the bottom of the list in sheet CI but all I get is this error '91'.

Here is the original code:

Sheets("CI").Activate
With Range("A65536").End(xlUp).Offset(1, 1)
.Value = MakeNewInvForm1.EnterDate_TB_02.Text
.Offset(0, 1).Value = MakeNewInvForm1.EnterInvNo_TB_01.Text
.Offset(0, 3).Value = CI_TextBox_3.Text
.Offset(0, 4).Value = CI_TextBox_4.Text

And this is how I modified it:

Set foundcell = Range("CI!A1:CI!A" &LastRow).Find _
(MakeNewInvForm1.GetCustInfo_ListBox_01_04, , xlValues)
Sheets("CI").Activate
With foundcell.Offset(0, 1)
.Value = MakeNewInvForm1.EnterDate_TB_02.Text
.Offset(0, 1).Value = MakeNewInvForm1.EnterInvNo_TB_01.Text
.Offset(0, 3).Value = CI_TextBox_3.Text
.Offset(0, 4).Value = CI_TextBox_4.Text

Can anyone tell me what I did wrong?

Any help would be most appreciated.

TIA

-Minitman

If you want multiple userforms in the project to see the variable then
declare it as public at the top if a general/Standard Module. With the
project the active project, Insert =>Module (just to clarify).

then at the top, outside any procedure

Public srcRange as Range

as an example.

Now set it when you load the data and when you want to write it back, use
that as your reference.

 

[Tom Ogilvy]

As long as you didn't declare teh variables anywhere else, the foundcell
should be usable in any userform once it has been set.

I have used this method many times. It is basic

do you use

END

anywhere in your code (if so, don't)

Obviously you are doing something wrong if it doesn't work, but I can't say
what it is.

It is possible that the target value is not found - you don't protect for
this

Set foundcell = Range("CI!A1:CI!A" &LastRow).Find _
(MakeNewInvForm1.GetCustInfo_ListBox_01_04, , xlValues)
Sheets("CI").Activate
if not foundcell is nothing then
With foundcell.Offset(0, 1)
.Value = MakeNewInvForm1.EnterDate_TB_02.Text
.Offset(0, 1).Value = MakeNewInvForm1.EnterInvNo_TB_01.Text
.Offset(0, 3).Value = CI_TextBox_3.Text
.Offset(0, 4).Value = CI_TextBox_4.Text
End with
Else
msgbox "Not found"
End if



--
Regards,
Tom Ogilvy

Hey Tom,

I must not have understood what you said, I keep getting

Run-time error '91'
Object variable or With block variable not set

My VBA Project tree looks like this:

[-] VBA Project (Invoice Enter.xls)
[-] Microsoft Excel Objects
Sheet 1 (Enter)
Sheet 2 (CI)
Sheet 3 (INV)
Sheet 4 (Lists)
ThisWorkbook
[-] Forms
MakeNewCustInfoForm
MakeNewInvForm1
MenuForm
[-] Modules
Module1
Module2
Module3

The variables "foundcell" and "LastCell" are located in the Form
"MakeNewInvForm1". They are needed in "MakeNewCustInfoForm". I
tried to make "foundcell" and "LastCell" global by putting this

Public LastRow As Integer
Public foundcell As Range

Into Module3, as I thought you recomended.

I modified some code that is used to place the customer infomation at
the bottom of the list in sheet CI but all I get is this error '91'.

Here is the original code:

Sheets("CI").Activate
With Range("A65536").End(xlUp).Offset(1, 1)
.Value = MakeNewInvForm1.EnterDate_TB_02.Text
.Offset(0, 1).Value = MakeNewInvForm1.EnterInvNo_TB_01.Text
.Offset(0, 3).Value = CI_TextBox_3.Text
.Offset(0, 4).Value = CI_TextBox_4.Text

And this is how I modified it:

Set foundcell = Range("CI!A1:CI!A" &LastRow).Find _
(MakeNewInvForm1.GetCustInfo_ListBox_01_04, , xlValues)
Sheets("CI").Activate
With foundcell.Offset(0, 1)
.Value = MakeNewInvForm1.EnterDate_TB_02.Text
.Offset(0, 1).Value = MakeNewInvForm1.EnterInvNo_TB_01.Text
.Offset(0, 3).Value = CI_TextBox_3.Text
.Offset(0, 4).Value = CI_TextBox_4.Text

Can anyone tell me what I did wrong?

Any help would be most appreciated.

TIA

-Minitman

If you want multiple userforms in the project to see the variable then
declare it as public at the top if a general/Standard Module. With the
project the active project, Insert =>Module (just to clarify).

then at the top, outside any procedure

Public srcRange as Range

as an example.

Now set it when you load the data and when you want to write it back, use
that as your reference.