472,956 Members | 2,602 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,956 software developers and data experts.

Excel vba problem help!!!


I have combobox list with product options:
Z3 etc.

and I want to to repeat the same macro (copy and paste range) every time when sb choose product from the list.
Attached you will find a .xls with the recorded macro I don't know how to relate the functioning of the macro from the choosing of the value from Combobox

I know that I should use Select Case but I don't know how.
Thank you for your help in advance.

Here is the recorded macro:
Expand|Select|Wrap|Line Numbers
  1. Sub PROD_Z()
  2. '
  3. ' PROD_Z Macro
  4. '
  6. '
  7.     Columns("I:L").Select
  8.     Range("I2").Activate
  9.     Selection.Copy
  10.     Columns("N:N").Select
  11.     Range("N2").Activate
  12.     ActiveSheet.Paste
  13.     Range("Q1").Select
  14.     Application.CutCopyMode = False
  15.     ActiveCell.FormulaR1C1 = "Z2"
  16.     Range("Q3:Q18").Select
  17.     Selection.ClearContents
  18. End Sub
Attached Files
File Type: xlsx CAT_Z.xlsx (31.6 KB, 165 views)
Jan 4 '18 #1
7 4670
228 Expert 128KB

I don't quite understand what you are trying to do with your macro itself but if you are trying to pass the value from the combo box into the macro then:

Assuming you are using a combo box inserted from the Developer menu and assuming your values Z1,Z2 and Z3 are fed into the 'Input Range' property from a worksheet range then do the following:

Set aside two cells for later use, I shall refer to them as InputCell and ReturnCell.

1) Point your combo box's 'Cell Link' property at InputCell. This will put the index of the combo box into the cell when you change the selection.

2) In ReturnCell input the formula( =Index([the range for Z1,Z2, etc],InputCell). This will convert the number in InputCell into the real Z1, Z2, Z3 value.

3) Right Click the combobox and select Assign Macro.

4) Select your macro and click OK.

5) In your macro, read the value from ReturnCell:

Expand|Select|Wrap|Line Numbers
  1. Dim foo As String
  2. foo = Range([ReturnCell]).Value2
If you put ReturnCell on a different sheet then adjust accordingly.

Then (if I understand what you are trying to do)

Expand|Select|Wrap|Line Numbers
  1. ActiveCell.FormulaR1C1 = "Z2"

Expand|Select|Wrap|Line Numbers
  1. ActiveCell.FormulaR1C1 = foo

P.S Think up a better name for your variable than foo!
Jan 5 '18 #2
Thank you for your reply.

I change the macro and now it looks like underneath:

If I click the button ADD PRODUCT it add products one time.
But I want to relate the functioning of the macro to the button.
Everytime when I click the button it will copy and paste range offset(0,5)
and add Z2,Z3,Z4... name in cell Q1(Z1), V1(Z2) etc.

Here is the code:
Expand|Select|Wrap|Line Numbers
  1. Sub CopyPaste()
  2. '
  3. ' CopyPaste Macro
  4. '
  5. Dim i As Long
  6. For i = 1 To 492
  7. 'Copies the specified selection to the Clipboard
  8. Range("I:L").Select
  9. Selection.Copy
  10. 'Paste the specified selection to the specified range
  11. Range("I1").Offset(0, 5 * i).Select
  12. ActiveSheet.Paste
  13. 'Unhidde the hidden columns
  14. Selection.EntireColumn.Hidden = False
  15. Next i
  16. 'Cancels Cut or Copy mode and removes the moving border
  17. Application.CutCopyMode = False
  18. End Sub
How to change the code to use by CommandButton.

Everytime when I click the button Add copied range to offset(0,5)
to max 492 times.
Now when I click the button Excel copy and paste range one time (492 times)

Thanks in advance.
Attached Files
File Type: xlsx CAT_Z2.xlsx (44.8 KB, 166 views)
Jan 5 '18 #3
228 Expert 128KB
So from what I understand you want:

A) a command to replicate the product blocks out to the right for each of the Z1, Z2 entries and you want each blue cell to contain the Z entry for that position in the index.


B) a command to add one new block to the end when you click it?

Jan 5 '18 #4
Yes add new block and in blue cell add Z1 Z2 Z3 .. AHEAD until z492

everytime when I click the button copy the block and add next product name Z1
Jan 5 '18 #5
When I click the button Copy the range I:M one time to right offset (0,5) and input in cell Q1 "Z1"
and click the button
Copy the range I:M one time to right offset (0,5) and input in cell V1 "Z2"

loop to x492 times

My macro do it but one time all replciation (x492) not one every time when I click the button

I hope this makes sense.

I'm new in VBA so I have no idea how to change my code to what I want to achieve.
Jan 5 '18 #6
32,547 Expert Mod 16PB
Another similar thread was posted as EXCEL VBA copy paste loop macro.

Anyone should feel free to PM me directly if they see that a member has posted what amounts to the same question more than once so I can ensure one is removed before it gets too messy.
Jan 8 '18 #7
Please delete my threads as I found the solution.
Jan 8 '18 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

by: steve | last post by:
Hi, I have generated a datagrid with the following columns: | Date-Time | Station ID | Parameter1 | .... | Parameter 2| I would like to: Create an Excel file and preferably give the user...
by: samycbe | last post by:
Hi Friends, I want to open the excel and show the print dialouge box in excel when a command button click on vb. Please help me samy
by: jtswim01 | last post by:
I have a very basic issue in excel and I can't for the life of me find the solution. I have a dense worksheet of data and I want to distill some of the information into a much shorter list. The...
by: johnlim20088 | last post by:
Hi, Hi someone can help me on this? Currently I have a Listdata.aspx page with datagrid, I wish to export my datagrid content to excel with following code ( with button export click event):- ...
by: Louis | last post by:
I am trying to use php to update a spreadsheet (MS Excel or OpenOffice Calc) with data from MySQL. I looked into PEAR::Spreadsheet_Excel_Writer if it will do the job. If I understand it...
by: budigila | last post by:
Hiya peeps, Okies, I have been trying to work this out for a while now to no avail... I am a beginner to this whole coding thing but have made great strides in my project. Basically what I am...
by: JC2710 | last post by:
Hi I have a problem which I cant quite solve. I have a query that groups records together to get a Total Price..... Group....... Total Quantity............Price ...
by: KodeKrazy | last post by:
I'm trying to read an Excel worksheet and do a find/replace for all of the commas "," in any of the cells in the sheet and replace them with a dash "-" I can get as far as getting the workbook...
by: hitencontractor | last post by:
I am working on .NET Version 2003 making an SDI application that calls MS Excel 2003. I added a menu item called "MyApp Help" in the end of the menu bar to show Help-> About. The application...
by: MyWaterloo | last post by:
Hi all. I am asking this question here because I have no idea where else to go with it. It involves VBA... but not Access. You guys have always been spot on with Access help so i thought maybe you...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.