I would like to have some kind of input mask on my input boxes, the input box is for currency IN GBP (£)
The program itself obviously has no use for the £ sign, but I would like it to display in the input box as a mask.
Is this possible?
I should mention I am using:
Application.InputBox
how can i show asterixes instead of the input data as it is being typed in a cell? somewhat like a password box...
View 6 Replies View RelatedThe cell content is to be 0-7 characters in length.
Alpha characters only, i.e., no numeric.
First character must be uppercase.
Remaining characters may be lowercase or blank.
Is that possible to do in MS Excel 2007?
I'm using the code below to create multiple sheets in a given workbook.
[Code] ......
The code works except for this line:
[Code] ......
What I'm trying to achieve, is to dispaly an 'Input Box' to allow the user to enter a date which will populate cell B3 in each of the created sheets. However, when I run this, although I can display the 'Input Box', when I enter the data, and click 'Ok', I receive a 'Run time' error highlighting this line as the cause.
I am looking for is VB code to firstly have a message box with an input box on (that bit i can do). The text and number put into the message box is then used to search the table for the information that relates to it.
The ideal would be for the code to find the data and then copy it either to the clipboard or to a section of the workbook.
The tables I am searching can be put into which ever format is easiest. Below is an example table;
A B C D E
1 A.1 Side Road Correctly Left
2 A.2 Hit in Rear Left
3 A.3 No known Police ref
4 A.4 Vandalism
I am using the built in moving average function to calculate the moving average of a set of numbers. There are a few things that i would like to do.
First i would like to have the last result displayed in a single cell. Then next to that cell i would like to have a cell that would specify the period of the moving average. I would like to be able to change the period in that cell and have that change it in the actual function. And finally i would like to have the moving average in a chart that would also change its period once that is changed in the respective cell. I realize that this might need some VB coding which i am currently learning.
Can anyone tell me if its possible to input some ones initials into a cell and when you hit enter, their full name is displayed instead of the initials:
ie input FB and the same cell displays Fred Bloggs?
I'm writing a macro that will automatically change the display in a column of cells.
The input in the cell would be a decimal value, (e.g. 1, 1.25, 1.5, 1.75, 2). As of now, I think the only input options are whole numbers and 1/4, 1/2, 3/4 fractions.
After inputting the decimal value, the cell should update to display the value as the following string format :
Input: 1.25
Displays: 1-1/4"
The purpose being to enable fast data entry while displaying in the desired format.
Here is where I know to begin for the automatic update:
Code:
Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'
'
'Code
'
'
Application.EnableEvents = True
End Sub
I am trying to display number of lines which depends on value pass by user. Actually user is passing some value. on which some group of lines has to be display.
eg
for 1 value 9 lines
for 2 value first 9 lines + another 9 lines
for 3 value first 18lines + 9 lines
and so on till 52.(this 9 lines are set of some column and rows)
thus cant use macros..
I have a few questions here.
1) Can a userform procedure be place in the midst a larger macro, so that it can get input from the user before running the rest of the macro? ie If the user leave the input field blank, the macro will exit will a msgbox.
2) I have a range(A1:A3) that i want my userform to display. This range is dynamic. The user will require to input the corresponding country next to it. how do i code the this? I will need to use a listboxes and textboxes right?
for example: userform shows city on the left, user will need to input the country on the right.
A1 - California (user input on the right)
A2 - Shanghai (user input on the right)
A3 - Mumbai (user input on the right)
Can i put into the cells B38-AF38, that would display the sum of B20:AF20 + B24:AF24 + B28:AF28 + B32:AF32 +B36:AF36 in a h:mm format.
When imputing the data into a cell in a h:mm format, I need it to display in that cell and formula bar the h:mm format without having to put an apostrophe in front of "h" or ":mm" value. Once complete, this form is going to go to other trainers to use on their computers and I dont think they would remember to keep putting an apostrophe before the data.
Example:
B20= 1:15 (1h15m) NOT 1:15:00 AM
B24= :30 (30min)
B28= :45 (45min)
B32= 1:45 (1h45m) NOT 1:45:00 AM
B38= 4:15 (4h15min)
I have attached a copy of the form for better clarification.
It doesnt seem to be complicated, but it's certainly a lot more than I thought.
I have a user form with 2 textboxes (used as parameters for an ODBC query). The entry should be a period, as yyyymm. How can I check, for instance when clicking the Ok button after entry, that both textboxes are of the "000000" format and the last 2 numbers are between 01 and 12?
View 9 Replies View RelatedI need the users to be able to enter the time using either a . or a colon :,
So: 13:30 would give 13:30 and 13.30 would give 13:30. They have end up in time format too since I will be performing calculations on them. When that decimal place is entered I would like it to be turned into a dot
Basically I want to be able to type a number and have a colon inserted two spaces to the left. So 123 would become 1:23, 1234 would be 12:34 and 12345 would be 123:45.
I'm working with anywhere from one to several thousand hours in a spreadsheet. I'd like to be able to use a time mask like this:
I'd like to enter dates without having to use the forward slash symbol between the day, month and year values. I've read a post by Dave Hawley explaing how to do it, but I can't get it to work for me. There must be some step that I am not aware of.
View 9 Replies View RelatedWant to enter 6 digits for each cell in a range of cells with VBA mask change to time format hh:mm:ss for each cell when entered. Code below does this for hours and minutes but I haven't been successful in modifying to work including seconds.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim vVal
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub
With Target
vVal = Format(.Value, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
Application.EnableEvents = False
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm"
End If
End With
Application.EnableEvents = True
End Sub
I have entered VBA code and tested it with success. If I then save & close the excel file and then re-open it, the mask time entry is not saved (time does not appear correctly after input) and per instructions I have designated (and am using) the cell input range.
View 9 Replies View RelatedI’m trying to write a VBA script to create disguised prices in an empty column “A” by extracting, and adding additional random generated characters (alpha, numeric, extended character set) from adjacent column “B” to create a scrambled price sheet.
Example Data: (2 columns and 5 rows)
Column A = newly scrambled prices. Column B = product prices.
Input: Column B = $13.95, $5.99, $4.00, $12.50, $10.10
Output: Column A = XCP1D3-VG9S5T.1L1, HUI0F5-SJ9W9X.9A6, RTJ0K4-JM0D0Z.4B4, KET1S2-AK5G0B.3M2, DLE1S0-QK1G0M.0C0...................
I have a long list of data (around 10,000 rows) with one column Client Sensitive (Column A see below) that needs to be changed before using in a pivot so the client won't see this data.
At the moment I'm using a formula but happy to use VBA for this as the rest of the report updates with VBA.
The formula that works but takes far too long is
=IF(COUNTIF($A$1:$A2,$A2)>1,VLOOKUP($A2,$A:$R,18,FALSE),MAX($R$1:$R1)+1)
Where column A is the sensitve data (and the unique identifier) and column R is the column the formula is entered.
The below is what I want, the column R I now use in a pivot to get the results I want then I can hide that column and delete Column A so no Client Sensitve data is with the spreadsheet.
However as I've said it takes far too long to calculate down 10,000 rows..
Column A
Column R
A12345
1
[Code] .....
how I can create a custom mask that will always put a / at the start of each cell in column C. So basically when I enter a number in, say 350, the mask will automatically put a / in front of it - /350 or even better /space350. The only issue is that the number will change in length a little, so not sure if this is a problem or not.
View 4 Replies View RelatedIs there a formula that I can put in AI that will mask the text character from text position 3 through the 2nd to last text position of each business name in AH? Where in the heck do you find XLGenie in the Excel 2007 ribbon? I have it installed and its in my addins list, yet nowhere to be found in the ribbon!
AH
13
ABC Rental
14
Professional Tool Service
15
Wonder Plumbing
16
Jim's Bar & Grill
17
Bobs Hair Salon
I have a simple table with these column: Name, Surname, Address, Date of Birth, etc.
I would like to create a button which open a mask to insert the above data "automatically" in the table/columns.
I tried playing, being able to create the mask UI, but it doesn't work: the command "OK" doesn't insert the data. I am sure it is a stupid thing, but I can't sort it out.
I've got a list of 300 IP addresses in Column A.
I want a formula which will return the router, mask and broadcast IP
Eg. If i have 192.168.1.129, the result are:
router = 192.168.1.1
mask = 255.255.255.0
broadcast = 192.168.1.255
I have a spreadsheet with 5 columns. A1 to A5
Entries are made into columns A2 to A5 but only ever one entry across all cells
If an entry is made into A2 to A5 the I need a "P" to be placed into A1
I am trying to create a macro which gives me an input box. I need to enter 2 numbers into the input box. It would be as follows:
Input Box Msg 1 - "What is your labor cost?" (NUM1)
Input Box Msg 2 - "What is your productivity rate?" (NUM2)
Then I need the macro to take this info and enter it into a formula which changes according to the row which you are on. (I need the macro to work based on which ever cell I currently have selected). The formula would be this if I currently had a cell on row 10 selected:
=(NUM1*(NUM2*$H10))/$H10
So, if I currently had cell L10 selected, and I ran the macro and put 100 in for "NUM1" and 10 for "NUM2" and my sheet had 20 in cell H10, then the macro would enter the number 1,000 into cell L10. If I had L500 selected, then the macro would enter the final number into cell L500 based on what value H500 contained.
I would like to know if it is possible to transfer values of cells this way?
I've attached the excel file for easier reference.
I have to design an input user form (a button that opens an input box or dialog box), which asks user to "Enter number of "columns"".
User enters a number i.e. 2 or 3 etc
It then asks user for the headings of these coulmns and places first column heading in A1 and second column heading in B1, third in C1 and so on ...
I am just starting to get my feet a little damp with VBA and I am trying to make a macro that will act whenever any text is entered in any cell within a 3x3 square. Whenever any text is entered in any of those cells I would like it to enter a text ( "X" ) in a cell which is specified by a cell in the spreadsheet (this cell will output which cell the macro should write the text in in this format "A:1" or "C:3"). How can I make the program act only in reaction to the user entering thier text, and also, how can I get the macro to read A:1 and enter the text into that specific cell? Any help would be so awesome, the book I have is really difficult to navigate.
View 9 Replies View RelatedExample:....
i m wanting Cell B1 to display true if any of the values in column C match otherwise I want to display false.
A 1
B 0
C 0
D 0
But I can't determine how to get this done.
I have attached the spreadsheet for reference. All other sheets pull information from the WHITE sheet.
I want the columns for unit price, discount and total to be set to two decimal places but I also don't want it to show anything if the field has a 0 value (so if i only have a few items I don't get lots of o's).
To get the field to not display a 0 if it is empty on the WHITE sheet I have been using "0;-0;;@" which I found on the net. If I use this then it negates the two decimal places.
This also includes the WHITE sheet.