I'm trying to write a macro that automatically italicize the a range of cells when you select a cell, and de-italicize it if you click it again. In this example, when select B12, the macro will automatically select B12:H12 and italize them. And if you select B12 again, it will automatically select B12:H12 and de-italize it.
This is what I have so far:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) MyAddress = ActiveCell.Row If MyAddress > 11 And MyAddress < 159 Then If Not Intersect(Columns(2), Target) Is Nothing Then Intersect(Columns(2), Target).Resize(, 7).Select End If
Select Case Target.Font.Italic Case "True" Target.Font.Italic = False Case "False" Target.Font.Italic = True End Select End If End Sub
On the attached example i have a user form (click Add New Hedge button) and on this form is a textbox for 'Avg. Price'. The value of entered from this box will go into the next available row in column on 'Unsettled Hedges' worksheet.
My problem is sometimes i would to enter the price as a fraction, e.g. 1/2 and other i would like to enter the price as a decimal, e.g. 1.5. At the moment if i enter the price as a decimal figure this will convert it to a fraction in column D of the 'unsettled hedges' worksheet.
What do i need to change in my code to make it enter the price in either format?
I have used the format [hh]:mm in a cell for 24 hr clock calculations. Why do I have to enter the numbers with a colon when I populate the cells? Is there a way to set it up so I just type in the four numbers and the colon between the hours and minutes populates itself?
I am needing a custom format for the following. I need a cell entry to be 1 letter, three numbers, a dash, 5 numbers, a dash, two numbers, a dash, and then two number. Example: A109-54785-13-00. The first letter will almost always be an "A."
To further complicate the matter, the entry is copied from an email and pasted without the dashes. In the above example, the number in the email would read, "A109547851300." I copy it from the email and paste it in the proper cell in the worksheet. I would like the custom format to automatically enter the dashes at the appropriate spot.
If it was all numbers, this would be easy. I created a custom format for the numbers, but when the A is included, Excel no longer treats it as a number and the custom format did not work.
Is this possible in Excel (without VBA).
I could use the custom number format I created and then later go back and add the "A" at the beginning, but that is as much work as manually adding the dashes.
I have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.
How can I format cells to contain Minutes, Seconds and Hundredths of seconds to be used in calculations eg 1.24.99 means 1 minute and 24.99 seconds. Example calculation is: 1.24.99 - 1.24.90 =0.0.09
I have an excel sheet wherein I want to enter the following formula into cell G11 when aparticular Macro is run by the user. I am using the following command but it is showing some error.
I have a doc which requires users to enter their info/request, BUT for whatever reason the users always seem to leave a cell/box empty and leaving me guessing
Is there a way to make cells/boxes BLINK and once info/value is entered the cells/boxes will stop BLINKing
I was wondering how I could automatically enter a value next in a sequence in a cell when an adjacent cell has data in it and continue doing so indefinitely (or until the 65536th row). I could do this with autofil, but The sequence starts in the middle of the worksheet (there is a title and document data in the top few rows). The layout is as follows:
Cell A17 has "Part 1" in it (and always will before the form gets filled out), and all cells in colum A beneath that will be blank. Column B is where the user enters a dimension. So, at the start, entering a value into cell B17 won't do anything. However, if they enter a value into cell B18, I would like cell A18 to automatically be filled with the next part number in the sequence (in this case, "Part 2" in cell A18). The user will never skip rows when they enter data so the sequence will always be +1 to the previous cell in the column.
I'm doing this so they can just print out the completed sheet and not have to edit out the empty "Part *" cells that have no corrosponding dimensions.
Is there a simple way to tailor autofill to do this or would I need a macro?
As simple as this sounds, I can't seem to make this work. I am looking for code for the following:
I would like to enter the word "Empty" into a cell if the cell is blank. The range can be variable. The only columns that contain an empty cell are columns B or C which can have any number of rows.
I really know nothing about vba so here goes. I would like to enter data in a row with 4 cells of info. then hit enter and return to the first cell and move the row down. all four cells must have data entered. and all four must move down. i tried some code as below i found and i modified but it did not work as expected. this moved the row down when returning the cursor to A2. It also should not copy the data style of the top row.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column <> 1 Then Exit Sub Application.EnableEvents = False
I am trying to get this code working. at the moment it allowed me to make multiple selection in a listbox. now i want each of the selected item to different cell.
What I am after is a user form that pops up when I enter ‘Y’ in cells in Column ‘L’ which displays the contents of the cells in that row e.g. if I enter Y in cell L2 a user form would pop up displaying the contents of cells A2 to M2
The A2 to M2 column headers are:-
First Name Surname Code Address line 1 Address line 2 Address line 3 Address line 4 Address line 5 Post code Telephone Comments
The user form can be Read only i.e. I do not need to edit the cells (although that would be good) as it will be used as a source document to input into another application, however, a ‘Print’ button to print the form would be beneficial. The only other button required would be a ‘Close Form’ option.
I have a userform with 5 text boxes and a command button.
I also have a sheet with a large amount of data on it, arranged into sections with headings (sheet1).
The user will enter the section heading he desires into textbox1, then other data into all of the other text boxes. Then click the command button.
I need a code that will search sheet1 for the heading they entered into textbox one, then find the next empty cell BELOW the heading (in the same row). It will then put whatever the user entered in textbox2 into that cell, textbox3 in the cell directly to the right of it and textbox4 to the right of that one, and so on.
I have a sheet where I have a column of cells where I manually enter in a date. the format is d-mmm-yy. Before the end of the year rolled around I was able to enter in say only 12-31 and it would automatically change that to 12-dec-09, now when I do that it spits out 12-dec-10. It's not a big deal to type in the extra -09 to get it right but was wondering if there was a quick/easy fix to get it to know that I don't want a date in the future?
All dates I type in will be in the recent past...never more than 3 months or so old and never later than today's date.
User selects a date from a pop up calendar. The date and user ID is entered into the active cell of the active sheet. This I have. I would like at the same time to have it enter the value of just the date (without the user ID) to cell A1 on the sheet labeled final.
Also, while I'm on the topic, I have another workbook where I would like to do the exact same thing with the exception that the user will only be allowed to select a date equal to today or up to 60 days from today?
is it possible to enter data on a spredsheet while a form is opened or must the from be closed first. I thought I was smart to have a form remain open so users could change parameters but at the same time allow them to enter data in uprotected cells.
I am trying to force users to enter data in 5 separate cells in a worksheet. I have tried editing code I have found here, but just can't get it to work.
It is in the second sheet of the workbook and the sheet is called Invoice - the cells I want to force entry into (and the message I need) are as follows.
J2 - You must enter the salesperson's name J4 - You must enter the customers name J5 - You must enter the customer's address J6 - You must enter the customer's postcode L2 - You must enter the Invoice number
I am working on blood pressures. I want cell C1 to place either, "Prehypertension","Stage I Hypertension" or, "Stage II Hypertension" depending on the values of cells A1 or B1 ....
I receive a flat text file every week which I would like to grab with excel and extract only the data I need and enter the data into separate cells and loop until I reach the end of the flat file. I got a subroutine written that allows me to open my text file and it will enter all the data however I need to know how to parse only the stuff I need and enter it into the right cells and loop until I reach the end of my text file. Here is what I have so far:
Sub testFSNew() Dim fs As Object ' scripting.filesystemobject Dim txtIn As Object ' scripting.textstream Dim strFile As String 'File Name Dim strLine As String 'Current line being read.
[code].....
Now so far this opens the text file and dumps all the data into an excel spreadsheet however when I say all I mean it dumps everything into the first cell and does not separate it, the following is an example of the text in the flat file. I will only put in the first 5 rows because their is 5000 rows in the real file.
Ok so the first problem is I don't need the first line it's a header line and if you will notice everyline of the file ends with either a 5 or a 4 but it is information about each employee, so the next line would end in a 5 and that would be the beginning of the next employee.
P.S. I noticed in the preview post that this message board truncated my flat file data, so keep in mind that each line is indeed 1 line ending in either 5 or a 4
I am trying to use visual basic editor in excel. I have all ready set up my user form where information can be entered, but I ave having trouble getting the information that is entered in the user form into the correct cells in excel.
I am wanting my information to enter the tables and then automatically move into the next available cells below.
Private Sub cmdadd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("details of cars in stock") iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row If Trim(Me.txtreg.Value) = "" Then Me.txtreg.SetFocus MsgBox "please enter a registraion number" Exit Sub End If ws.Cells(iRow, 1).Value = Me.txtreg.Value Me.txtreg.Value = ""
I have a spreadsheet where a date is entered in column A with data in B-F. I am trying to write code to look in column A for a specific date and enter additional data into columns G-L on the same row. It needs to be able to skip the rows that are blank. So I have the dates of 12/1, 12/2 and 12/3 in column A rows 1,2 and 3 and I want to enter data for 12/2 on row 2 skipping the blank cells in row 1 for 12/1. Here is the code I have below.
VB:
Private Sub CBSecure_Click() Dim my_name As String Dim r As Variant Dim l As Long sFind = DockDoorCal.Value If Trim(sFind) = "" Then Exit Sub
I need to add something to the code below to have my first row of data entered into cell "Q8". As it is now the data is starting on row Q9 instead of Q8, but the rest of it is filling in correctly.
Have developed a form for my associates and want them to be able to move to the cellls I want them to fill data in to by merely hitting the TAB or ENTER key vs. having to move the cursor to each cell requiring them to input data.
Want cursor to move in a specific cell order. You would think you could merely say 1M, 2C, etc.