Linking Macros to a Button for designing User Form

[Logan CMA]

Dear Experts,

I was wondered to see this awesome Forum after a long hectic days I suffered with Excel workings..

Just I joined, its really great site to watch on..

I need a solution for my designing of user forms. I have created my macros by recording after a long tests, now, I have to assign it to a Button Control, however, I have written my macros in a active sheet and when I design my forms in a same sheet its getting into trouble.FTE Analysis_Stage1.xlsm

Can anyone of you help me in separating my design in a diff sheet and my macro running in a diff sheet?

Many Thanks in advance,
Logan.

[Mordred]

Hi Logan, your code doesn't make much sense to me. You basically delete all cells from Column A to N and then you select A1:L616 which are now empty, copy them, paste them to the next sheet and then try to perform a text to columns on blank cells. Surely this is not your intention is it, to copy blank cells and paste them to the next sheet?

[Logan CMA]

Sir, I have a bulk data to populate there..... the code I have written there will work fine when I paste my data into it and run... due to personal information in the date. I havent disclosed there. My apologies for that.

[Mordred]

Hi Logan, I guess I'm not understanding how this can be working fine

Please Login or Register  to view this content.

When the lines

Please Login or Register  to view this content.

will be copying blank cells because of all the deleting before that. The error comes because the Selection.TextToColumns line has no text in the cells.

[Logan CMA]

Sir, there are some 20+ columns, in that I am deleting all the unwanted columns for my analysis and left with only few columns in the next sheet. The text to column function is to clean the data which stored in text format.

[Mordred]

Hi Logan, sorry, sometimes I just need to understand these things before I proceed.

[Logan CMA]

Sir, thanks for your patience. Other than sharing data, I can answer all your questions. Please ask me.

[Mordred]

Hi Logan, I've been trying to go through your code and understand its purpose but I cannot. The reasons are two-fold. 1, there's no data in the workbook and 2, there is so much deleting and selecting that it's very difficult to keep track of what needs to be done. I'm sorry, maybe someone else will be able to help.

[Logan CMA]

Example.xlsxDear Mordred,

Sorry for the delay. I have made some data model in align with my data to make you clear understandings on my coding. In that I have highlighted columns in Brown which needs for my analysis. Hope this helps.

Thanks a ton for you patience.

Logan,

[jaslake]

Hi Logan

The Code is running on "Input WFA". If I understand your issue add the indicated line of Code

Please Login or Register  to view this content.

[Logan CMA]

Hi John,

thanks for your reply. Yes, the code Fte.Test12 is running on Input WFA sheet. What I want is to add a another sheet and design my Control Buttons to assign the Macro. Can you help?

[jaslake]

Hi Logan

In playing with your uploaded File I thought I understood the issue. I'm with Mordred...I don't understand...sorry.

Please explain again what you're attempting to do...perhaps one of us can help.

[tigeravatar]

Logan CMA,

Here is a cleaned up version of your FteTest12 code with comments:

Please Login or Register  to view this content.

[Logan CMA]

Sorry for confusions...

I have a code called Fte_test12 which can run in a Sheet called "Input WFA".
I dont have any problems with my coding, however, now I need to create a user forms (Button) to assign a Macro to it for presentation purpose. I tried, having a separate sheet for this design but it is not working for me.

The need from you is, please help me to have my design sheet separate and Input WFA sheet separate without disturbing a code to run. Many Thanks...

[tigeravatar]

Logan, did you see my earlier post? It does exactly that. All you need to do is update the sheet names in the code as necessary (if you need to change them at all)

[Logan CMA]

Yes Sir, just tried this code but it thrown some run time error. Can you paste the code in my file and attach here please?

Hearty Thanks for you quick help.. It will solve my headache...

[tigeravatar]

Logan,

Attached is a modified verion of your Example.xlsx workbook
I renamed the sheet with the raw, original data to 'Input WFA'
The sheet where the resulting, cleaned data will be displayed is named 'Data Cleanse'
On Sheet1 I put a button labelled "FteTest12" and it is assigned to this macro:

Please Login or Register  to view this content.

When I click the button, the macro runs as intended and displays the cleaned data

[Logan CMA]

Hi Tiger,

The code is working fine now with my data model. But when I tried this with my original file, the name Full name column has error "#VALUE!".

Actually, the name in original file is Chris, Logan (Logan = First Name) (Chris = Last Name), however in data cleanse file I need this as Logan Chris.

In my original Macro recording code, what I did was I split data by Comma separator and the used Trim Function to clean, then combined First name and Last Name to render it as Full name.

Can you fix this please?

Thanks a ton.

[tigeravatar]

Updated code, corrected slight problem with the name swap portion:

Please Login or Register  to view this content.

[Logan CMA]

Example2.xlsmDear Tiger.

Awesome Job.. The code working fine as I expected.

Final query, as I stated in my first post I have two codes to link 1 (Fte_test12) 2 (LcMapping1).

Here, I have attached my code with the sheet. Kindly merge the two codes and assign it to a same button.


This is nothing but some look up formulas to map data from LC map sheet to Data Cleanse sheet.

Many thanks in advance. Please accept my reputation too...

[tigeravatar]

Is the LC Map code getting its data from the 'Input WFA' sheet (the raw, original data)? Or is it designed to be run after the FteTest12 code so it should be getting its data from the 'Data Cleanse' sheet?

[Logan CMA]

Tiger,

Sorry for late reply. Yes, are right. The LC Map code will run after the Fte-test code and it will take data from Data Cleance sheet. Thanks for understanding...

[tigeravatar]

Alright, I'm a little confused by your sample file because there is nothing from the 'Input WFA' that can be used as a starting point for VLookup to the 'LC Map' sheet. In 'LC Map', column A is filled with "LOND001439" etc, and there is nothing anywhere close to that in the original data on sheet 'Input WFA' let alone the 'Data Cleanse' sheet. In your code, you are using column M from the 'Data Cleanse' sheet as the lookup value, but there is no column M in the 'Data Cleanse' sheet after running the FteTest12 code. So how is it supposed to look anything up??

[Logan CMA]

Dear Tiger,

Again my apologies for making you confused. Here the snapshot that coding for LC Map

1) It will run from data cleanse sheet (after Fte_test12 run)
2) It split data in column K (LN05/NL05005683) by "/" separator as LN05 and NL05005683 in two diff columns.
3) This NL05005683 column will be named as Local CC
4) now, it will look up values in LC Map file and insert next column and name as SERP CC
5)second Vlook up by using SERP CC column to bring CC Name column
6)third Vlook up by using SERP CC column to bring PC
7)fourth Vlook up by using PC column to bring PC Name column.

I have run the same code in this example for your ease of reference. Many Thanks for your co-operation

Example2.xlsmExample2.xlsm

[tigeravatar]

Logan,

here is the combined code:

Please Login or Register  to view this content.

[Logan CMA]

Hey Tiger,

Its awesome sir...... Its exactly meeting my expectations... No words to express....simply amazing.
I would like to join as your assistant towards this helping hands forum. Can you accept this request please?

Btw, whats your real name? other than Excel Tiger?

[tigeravatar]

You're very welcome, Logan

My real is Jeremy, and I can't really accept a request to become my "assistant". If you'd like to answer questions, just dive in and give it a try For questions you can't answer yet, see the answers of others until you can start answering those questions yourself. If you'd like to more formally learn Excel, this thread has several good links: http://www.excelforum.com/excel-prog...materials.html