VBA - Selection Box

Jul 13, 2007

I have a code (pasted below) that copys a line of data from one workbook into another. Each time i run the code it enters the data on the next available line. This is all working ok however what I would really like to do is when i run the code have it pop up a selection box asking me which sheet i would like to copy from. The reason i need this is that the sheet that gives the data isn't always named the same although the format is exactly the same every time. In this example the data sheet is "copy of NewProductForm), this name may change and i may also have other different active workbooks open at the same time and I don't want the macro to get data from them.

Code :

Sub GetData()
Windows("Copy of NewProductForm.xls").Activate
Rows("48:48").Select
Selection.Copy
Windows("Book2.xls").Activate
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Copy of NewProductForm.xls").Activate
Application.CutCopyMode = False
Range("A41:AH41").Select
Windows("Book2.xls").Activate
End Sub

View 9 Replies


ADVERTISEMENT

Displaying Input Box Based On User Selection Of Dropdown Selection?

Jun 3, 2013

If a user selects certain options from a drop down list created by cell verification (from list), is it possible to display an input box and have the resulting input populate on another worksheet in the workbook? I have an attendance template I'm working with and if a user selects OT (Over Time) then a input box is displayed prompting for how many hours. The overtime is then tracked on another worksheet.

View 1 Replies View Related

Increment Cell On Selection And Lock/Protect From Re-Selection

Oct 3, 2006

I tried very hard to design a leave roster for user to mark their leave application. The criteria are as follows:

1. 4 applications per date
2. a region of worksheet (i.e. "A2 to H20) will be defined for users to "click" on the cells (within the defined region) to mark their application.
3. once a cell is clicked (i.e. marked), it cannot be altered.
4. each click will automatically increase the total by 1.

I got the following code to deal with the situation but user can still click on any cell outside the region (in fact I fail to define the region).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim m
ad = Mid(ActiveCell.Address, 2, 1)
m = Range(ad & 24).Value 'here a formula "CountA(A2,A20)" will be place in the cell (24, c).......................

View 2 Replies View Related

Copy & Paste Selection Based On Combo Box Selection

Jan 30, 2007

I have attached a portion of an excel file I am using at the moment. What I require is a piece of VBA code which will allow me to copy selected data based on a combo box selection. What needs to happen is this :-

If the selection in the "Index" worksheet combo box related to "Month" in cell G19 is for instance "December", I would like the macro to compare this cell value to the cell values in cells C96, C124, C152, C180, C209, C236, C263 and C290 in the "Tech Services" worksheet and where the values match.......copy the commentary (Range C126:Z147 in December's case) to the range C34:Z55.

View 9 Replies View Related

Disable Selection In Listbox On Selection Of Item

Apr 4, 2013

I am working on a project where user has to select an item from a listbox, however there is a condition that if user select an item, other items of listbox should be disabled so that he/she can not select any other item.

Is there any way to do that as I tried

VB : Listbox.enabled = False
and
VB : Listbox.locket = True

in Listbox - Click/Change but no desirable results got.

View 1 Replies View Related

Restrict Drop-Down Selection Based On Selection From Another

Jul 20, 2007

I want to have a drop down list in a cell so that the value in the cell can be only selected from two columns of data. Additionally once the data from the first column has been selected I want to be able to limit the inputs the user can select from in the second column.

e.g. In cell C115 I want to have the value BDS05.

I want to be able to select the value BDS from a drop down list of values and once that value has been selected I want to be able select 05 from a list of values from 01 to 14
If I select BCS as the first value then I want the second set of values to be limited to 01 to 02 etc.

I have read about combo boxes and list boxes and I'm a bit confused about the best way to achieve this (or even if I can).

View 6 Replies View Related

Run Selection Change Event Only If The Row Selection Changes

Mar 26, 2007

I'm using the following code in a worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
col = ActiveCell.Column
Range("output") = ActiveCell.Offset(0, -(col - 4)).Value
End Sub

In case it's not obvious, the macro places the value in the active row and 4th column of the worksheet into the range "output".
The problem is, the worksheet is large and somewhat slow to recalculate. This macro forces a recalc on any selection change, but i only need it to run when the row selection changes, not the column.
I'm sure there's a straightforward way to reprogram this.

View 7 Replies View Related

Investment Selection With Cell Selection

May 4, 2007

I'm trying to build an investment calculator. Tried searching "calculator" as keyword but return nothing similiar.

There are eight cell : [ A ][ B ][ C ][ D ][ E ][ F ][ G ][h]

[ H ] is price of the leverage

[A] is X balance
[b] is X lot size
[C] is X amount of money use to get x lot
[D] is X percentage of money use to get x lot
[E] is X point gain
[f] is X money gain
[G] is balance + money gain

Ignore the formulae for leverage to determine 1 lot price. Take 1 lot = 250.

What I'm looking for is when I enter any value at cell B, C or D, it will automatically calculate the value for either B, C or D cell.

Let say, the balance is 10000.

If I enter 2 at cell B. Then, it will calculate the value for cell C & D.

If I enter 750 at cell C, then it will calculate value for cell B & D.

If I enter 25 at cell D, it will calculate value for cell B & C.

how to use OR Selection in excel.

View 9 Replies View Related

If Selection Or Any Part Of The Selection

May 24, 2007

If the selection (or any part of the selection) = bla bla bla

View 9 Replies View Related

First & Last Row Of Selection

Dec 22, 2006

I am trying to format a range of cells based on a user selection. The user may select 8 rows (rows 20 - 27) or the might just select A20 to A27 or the may select A20 to C27 on all of the occasions I would like to find the 1st row and the last row of a selection. I have found the command

x = ActiveWindow.RangeSelection.Address

which I could try and extract the row numbers, but is there an easier way?

View 7 Replies View Related

Min Row And Max Row Of A Selection

Jan 24, 2007

I want to program a macro that reads out the min row number and max row number of a selection.

Lets say a user selected the Range A1:D4.
After executing the macro I want the following output:
The min row number of the selection is: 1
The max row number of the selction is: 4

View 9 Replies View Related

Move Selection One To The Right?

Mar 18, 2014

For a specific equation to work I need to know the distance whenever the acceleration is maximum.

The last part is a piece of cake, using MAX I get the desired results.

However, returning the distance is difficult.

What I'd like to do is return the value of the cell to the right of the maximum acceleration.

Using

=ADDRESS(MATCH(MAX(K2:K754);K2:K754;0);COLUMN(K2:K754))

I got the address of MAX, but that's about as far as I get.

View 9 Replies View Related

Sum Only Value Of List Selection?

Jul 17, 2009

I have a list of 4 customers and their individual revenue, which is summed up in cell E12.

What needs to be done to make Excel only sum revenue of say customer A and D in cell 12 upon selection of these through standard filter (see attached sample)?

View 2 Replies View Related

How To Print A Selection To PDF

Apr 9, 2013

I've attached a sheet. In this sheet I would like to print the selection....C9:17 and BL9:BW17. I would like to print it onto a PDF and I would like to expand it so it is very visible. Actually, it doesn't need to be PDF. If I could just print this selection and make it visible that would work. I had a problem using print selection because the columns are separated. I also tried fit to page but that makes it too small.Mod's note: file attachment removed at OP's request

View 2 Replies View Related

Summing With #DIV/0 In Selection

Oct 22, 2008

I'm having a problem summing after running arrays to analyse data. I have a list of four possible outcomes and I need sum up the totals, but sometimes there are no results, so there is #DIV/0 in the list I need to find the total for.

is there some code or some kind of formatting that would give me a total even if #DIV/0 is in the list?

View 3 Replies View Related

Testing A Selection

Nov 2, 2008

I've currently written the following code to make sure exactly 2 single cell selections are made. But now, I want to extend this code to make sure only cells from rows 5 & 7 are selected and the cells are in the same column.

View 2 Replies View Related

Using Trim On A Selection

Dec 18, 2008

I want to create a macro that will allow me to highlight a column and have the macro Trim every cell with text in the column, preferably putting the results over the original text. The column in question has text in every cell, until the column ends.

(That is, there are no numbers and no blanks until the data ends altogether.) I do not want to remove internal spaces in the text, just the leading and trailing ones.

I am sure this is fairly simple, but I'm not sure how to get it to look at every cell and then terminate properly.

View 8 Replies View Related

Not Allow Cell Selection ...

Jun 16, 2009

It is actually a form - much like Access. I have protected it so it can't be changed. They can click on a particular cell, but as soon as they try to enter anything it gives them a message and entry is not allowed.

Is it possible to even prevent them from selecting an individual cell, i.e., restricting them to only clicking the control buttons ? I realize this is a spreadsheet, other products would probably have worked better, etc., however it's kind of like the last minor hurdle before releasing it for general use. The world certainly will not stop if I can't - just thought I would check with the expers.

View 4 Replies View Related

About Range Selection

Jul 22, 2009

A question regarding "AboutRangeSelection" procedure on Chapter 11 in the **, Excel 2003 Power Programming with VBA by John Walkenbach.

View 3 Replies View Related

Check If Selection Changes To Anywhere Row 1

Jan 31, 2012

This works...

If Not Intersect(Target, Range("E1")) Is Nothing Then

However..

How do I change Range("E1") to any cell on row 1?

View 3 Replies View Related

Selection From Two Columns

Aug 8, 2013

I have two strings in two cells, both are dynamic values. I need to plot the values of any of the column that changed in the third cell either the two cells change its values.

View 5 Replies View Related

Crop To Selection

Apr 17, 2007

I want an easy way to crop to my selected cells, can this be done in Excel?

View 9 Replies View Related

Inverting A Selection

Sep 5, 2007

Is it possible to invert a selection? I have a heap of data that is scattered all over the place and I can to a Search and Find All to locate the data I want but I want to get rid of everything else, so can I invert the selection some how?

View 9 Replies View Related

Unique Selection

Feb 5, 2008

column A having the following

Economy
Premium
Economy
Premium
Standard
Economy
Economy
Premium
Standard

I want ot select only Economy without using filter option

View 9 Replies View Related

On Selection Event

Apr 14, 2008

I would like to create some VBA code that changes the color of the cells I have selected, as soon as I let go of my mouse click. Additionally, is there a way to identify where the range begins and ends so I can test to make sure the range is inside a certain area?

View 9 Replies View Related

Change Selection With VBA

Nov 27, 2008

My user wants to change it so that, when the word "Triang" is selected from a dropdown box on each line the formatting goes back to none......or ClearFormats.

Can this be done with a Worksheet_selectionChange or similar.
I can't even get my head around this at the moment.
What the user is saying is, if the Formats haven't been cleared on certain lines, the user of the sheet hasn't addressed the needs for that particular line.

For r = lrow To 15 Step -1
If Range("M" & r).Value "" = True Then
Range("N" & r).Value = "Triang"
Range("N" & r).Interior.Color = vbRed
Range("V" & r).Value = "Triang"
Range("V" & r).Interior.Color = vbRed
End If
Next r

View 9 Replies View Related

VBA With Worksheet Selection

Mar 6, 2009

I am trying to do the following...(pretty easy).

When the "Single DSC Summary" worksheet is selected, upon selection the activecell needs to be B7. In B7 is a validation list.

Once a selection is made in the validation list, the activecell should immediately be J7, which is an indirect validation list to B7.

I want to do this because everyone loves the idea of the validation list, but because you cant tell where the list is until you select the cell, they get confused. This way the activecell will always be the validation lists.

I have tried highlighting..bolding..even arrows pointing to the cell...and I still get calls.

View 9 Replies View Related

Insert Selection To Next Available Row +1

May 14, 2009

I have a table in rows 19:39, and I have assigned this macro to a button. If a new table needs to be inserted "just press the button", and I would like it to insert a new table below the last table created. So, I thought it was Selection.Insert Shift:=xlLastRow + 1, however, this just seems to push down the previous table created by pressing the button.

(I have more code that only formats the table after insert, but am not showing it here, since for testing I have 'd it all out).

Thanks for any help in advance.


Rows("19:39").Select
Selection.Copy
Selection.Insert Shift:=xlLastRow + 1
Application.CutCopyMode = False

View 9 Replies View Related

Number Selection

Oct 30, 2009

To select cells of a certain value I would use something like this:

If Range("E" & n).value = 90 Then
Range("E" & n).Select
End If

But I need to select cells with numbers starting with a certain number although they may not neccesarally be the same number of digets, ie i would equally want to select 789, 78910, and 700000000

How would you do this in VBA?
______________________________________________
Also, is it possible to use the above script to select multiple values ie

If Range("E" & n).value = 90 or 165 Then
Range("E" & n).Select
End If

View 9 Replies View Related

Using Selection With Is Empty

Dec 23, 2009

I'm having some trouble getting a private sub to initiate and I'm sure it's because I don't have a target specified.

The first part of code puts a checkmark into a cell if it is clicked and that initiates another code called "BUILDER":

Private sub worksheet_selectionchange(ByVal target As Range)
If IsEmpty(target) Then
target.Formula = "=CHAR(252)"
target.Value = target.Value
With target.Font
.Name = "Wingdings"
.FontStyle = "Bold"
.Size = 8
End With.........

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved