Help required in updating data using Excel Form

[Simayan]

Hi,
Please find attached file. This is my first code. There are two forms
1. Add data i.e. Estimate
2. Edit/Update data i.e. Estimate
I am facing below problems
a. unable to update data
b. how to set date picker data to today's date & ensure same has been interfaced/ used by user
On a separate note if I want to move all the invoke buttons of the forms in a separate sheet what changes need to be done in the codes ( also is there any macro so that the sheet containing invoking buttons opens automatically, when the workbook is opened)
Please let me know in case of any clarification
Simayan

 

[Nebu]

Hi:

Please find the attached.

Thanks

 

[Simayan]

Dear Nebu,

Many Thanks for your help. Could you please let me know where I was making error.

Also please tell me how to set date picker data to today's date & ensure same has been interfaced/ used by user

 

[Nebu]

Hi:

You where not making any mistakes in the case of adding a new project. However, updating an existing customer needs the macro to identify the particular row where the project is in which I had to code. You also wanted to change the buttons to a different sheet I had to change code to suit this.

For setting the date picker to today's date use the following code

Me.yourdatepickername.value=date

Note: compare your old code with mine you can easily figure out the changes I made. If you can execute the macro step-by-step using function key F8 you will comprehend better.

Thanks

 

[Simayan]

Dear Nebu,

Thanks for your reply. Can you tell me if there is any means to ensure a date picker field has been interfaced/ used by user.

Like if any text box/Combo box is not filled I can put a validation check, how same can be done with date picker

 

[Nebu]

Hi:

This is possible, but the code Me.yourdatepickername.value=date will always fill in the date picker with today's date. Hence the date picker will never be empty so on what basis do you want to trigger a validation check, I guess its a catch 22 situation you will have to decide which way you want to go.

Thanks

 

[Simayan]

Hi Nebu,
Hope you are well & doing good
In the attached file there is a form i.e. Update_Estimate. In this form I have added a multipage so that user can select the form based on the option selected.
Such as
Update Estimate - Received from Finance--------page 2 will be activated
Update Estimate - Note to be Submitted--------page 3 will be activated
Update Estimate - Submitted for PO generation--------page 4 will be activated

Update Estimate - PO raised--------page 4 will be activated
But all this pages will refer to same source to retrieve data & update the same.
I have changed the visible & Enabled option for better clarity

Now I am getting error
a. Compile error, variable not defined
b. Run time error '35788' ; An error occurred in a call to windows date and time picker control
Need your inputs
Thanks in advance.
Regards
Simayan

 

[Nebu]

Hi:

I have fixed the date picker error.The error was occurring because of multiple pages I had to change the coding of assigning the current date to the date picker from user form initialize to Multipage change with some if conditions.

Thanks

 

[Simayan]

Thanks Nebu. But the file you have shared post modification, I am unable to activate the corresponding forms from page1 of the multipage i.e.
Update Estimate - Received from Finance--------page 2 will be activated
Update Estimate - Note to be Submitted--------page 3 will be activated
Update Estimate - Submitted for PO generation--------page 4 will be activated
Update Estimate - PO raised--------page 4 will be activated.

but all these form will refer to same source & updating sheet

Also , the form in Page 3 & page 4 is not retrieving the data, should I need to replicate the code individually for each page.

Please share your inputs

 

[Nebu]

Hi:

I have not coded any of the above. I was under the assumption that you already had those codes in place. I have only written the code that will assign today's date to the date picker, so that you won't error out as before. Activating the pages while clicking option buttons on the page 1 is pretty easy to incorporate, other functionalities I have to see in detail.Let me know your thoughts

Thanks

 

[Simayan]

Hi Nebu,

Well I figured out the issue & resolved the same. So now my form is working fine. Attached same for your review.

Just have 1 query, In case I want to add records in batch, that is multiple records at a time; is that possible & how

 

[Nebu]

Hi:
This looks impressive !!!
A few points:

1. I guess your date picker is not picking up the current date for any of the forms.
2. Updating Invoice options are not working properly , guess you have not coded for that.

Regarding batch update: I am afraid that you can do a batch update using this format coz the text boxes can hold only one value at a time.

But if you already have bulk data somewhere why can't you just manipulate it outside the user form and bring it to the data base.

Thanks

 

[Simayan]

Hi Nebu,

Thanks. Well can you please explain what is the error in my Date picker codes.

Yeah Updating Invoice module I haven't coded yet....this will be a code replication of update estimate module so once my entire Add/update estimate is bug free....will work on invoice module

 

[Nebu]

Hi:

It is in the following forms

1.Update an Estimate -submitted for signing.( the active date picker, I know a bunch of the them are not available for editing)

2.Update an Estimate -submitted for PO generation.(same as the above)

Also one small suggestion, once you exit your any of the update forms make the main form visible else every time you will have to click on the Active Tracker button if you want to switch between different update forms .

Hope I made sense.

Glad that you nailed it

Thanks

 

[Simayan]

Hi,

Nebu any idea on how to copy named range from one excel to another

 

[Nebu]

Hi:
Please find the code

Sheet1.Range("yournamedrange name").Copy
Sheet2.Range("a1").PasteSpecial

Thanks

 

[Simayan]

Hi Nebu,

Hope you are fine.

Today I have a query on creating a macro for to update weekly report using Uto vlookup & Auto pivot

Objective: I want to build a weekly sales report in following steps

1. In Op_Sheet we will paste the data from SAP

3. in Base_Data there will an auto vlookup to create final sales data week on week

Problem:

1. My Macro is not working

2. Post the data is extracted it should be paste as value ( data extracted using vlookup)----a dummy/helper column can be used & the entire OP_Sheet sheet will be cleared for reuse for upcoming next week.


Need your help..


Additionally is it possible to update/Change data source the sales pivot made from Base_Data can be automatically using macro...since every week only column is increased.

Thanks in advance

 

[Nebu]

Hi:
I will be on training for couple of days , will look into this over the weekend or next week.

Thanks

 

[Simayan]

Sure Nebu. meanwhile I have worked on the problem & attached the modified version for your referral. Please look into this.

In addition to my previous request How to deselect the data &
is it possible to update/Change data source the sales pivot made from Base_Data can be automatically using macro...since every week only column is increased.

 

[Nebu]

Hi:

sorry for late reply. I was in the middle of a critical project.

Convert your base data tab into a table and draw your pivot from this table , this will give you a dynamic data source for the pivot. I am not sure what do you mean by deselect the data . You can give application.cutcopymode=false after your paste command and write a code to activate the first cell of the row where you have pasted the data.

Thanks
Nebu

 

[Simayan]

Hi Nebu,

Thanks for the feed back. Now my code working fine

Meanwhile I have developed with help materials found in this forum and the previous Code I prepared (in which you guided of course!!)a new macro with better user-interface. Need your suggestion in the following glitches

1. Update Module showing error

2. Delete module showing error

3.If you put ** in search filed multiple records coming...else its working fine i.e. either with * or part string/full string/part string followed by *.
Thanks in Advance

 

[Nebu]

Hi:

I have fixed the 1 & 2 , I am not sure about the 3rd one when I searched at my end it is working properly.

Thanks

 

[Simayan]

Hi Nebu,

Thanks for your help. See the attached pics. The problem I meant given in Pic 1 & 2( getting same records number of times i.e. no of fields I have); while pic 3 is working fine.

On a separate note can you tell how to add column header in List box

 

[Nebu]

Hi:

Please find the attached. I have fixed it.

Thanks

 

[Simayan]

Thanks Nebu. Pretty Cool. really loved the idea of adding label to show column headers. I missed it