Free Microsoft Excel 2013 Quick Reference

Insert value on double click Results

I have a list of PartNumber ranging from B2:B6880 and I am vlooking them up to other worksheets for values of Safety-Stock, Qty-on-hand, PO-Qty, etc..

For example, I insert column N to vlookup for the Safety-Stock values. Double-clicking the black cross-sign that appears on the bottom-right of cell N2 Vlookup formula doesn't copy the N2 formula to N6880 because there are some blank cells in column M and column O.

I am hoping someone can help me with a code which can allow to copy cell N2 formula to the last cell in column N where the whole row is not totally blank, ie. at least has a cell in the row populated with value.
Eg, if this row as mentioned above is row 6880, then row 6881 onwards will be totally blank.

Appreciate so much if someone can help with with a code to do this.
Thanks so much in advance!

Hi,
I am new and I hope I can get your help
Could you please show me the program code that I need.

For purpose of automation
I have data file with the name Alex-Q3-2003 in c:datafile
My Workbook name is reval_book.xls and when I open Alex_Q3-2003 I run macro to extract info from given file name as bellow

Question

How can I automate the process that when I open or run reval_book.xls( double click on the reval_book.xls) get the data file name automatically that is c:datafileAlex-Q3-2003 (The only file in the directory) and insert it in $B6 (input file name) and split the name Alex-Q3-2003 and put “Alex” in $B1 (Customer name) and in $B3 (Quarter) Q3-2003

(Input and Output Directory is hard coded)

A B

Customer name

Quarter

Input file name

Input directory c:datafile

Output directory c:datafile -OUT

The macro to get the file name did not work getting run time error 52 with Bad file name or number

ub Get_filename()

Dim FullName
Dim ShortName

FullName = Dir(" c:datafile*.txt", 7)
ShortName = Left(FullName, Len(FullName) - 4)
Range("B6").Value = ShortName

End Sub

Could you please advise

Thank you for your help

I have a userform that is used to add rows of data to a sheet. I also have two buttons that are used to sort that list ascending and descending. However, when I add a new row and try to use the sort buttons it appears to hide one of the rows of information. When I try to unhide the row it wont unhide. The only way to make it visible again is if I hover the cursor over the missing row and double click on it.

The interesting thing is that it only does this row "hideing" the first time I use the userform since the workbook was last opened. I can repeatedly add rows of data with the userform and no other rows become hidden. However, if i save the file with a hidden row and open and try to add another row then there are 2 hidden rows.

Please help me figure out this dilemma! I have been trying to figure it out for days now! I have the code for the userform, which is completed when the AddButton is clicked, as well as my ascending macro.

Here is my code for my ascending macro:


	VB:
	
 Ascending() 
     
    ActiveSheet.Unprotect "agichi" 
     
    Dim r As Integer 
    r = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row 
     
    Dim c As Integer 
    c = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Column 
     
    Dim RightEndRow As Integer 
    RightEndRow = ActiveSheet.Range("C10000").End(xlUp).Row 
     
     
     
     'turns off interface to speed up makeing buttons
    On Error Resume Next 
    With Excel.Application 
        .Calculation = xlCalculationManual 
        .EnableEvents = False 
        .EnableSound = False 
        .Cursor = xlWait 
        .StatusBar = "Working..." 
        .ScreenUpdating = False 
        .DisplayAlerts = False 
    End With 
     
    Range(Cells(6, "C"), Cells(RightEndRow, "NZ")).Select 
    ThisWorkbook.ActiveSheet.Sort.SortFields.Clear 
    ThisWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range(Cells(6, c), Cells(6, c)), _ 
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ 
    xlSortTextAsNumbers 
    With ThisWorkbook.ActiveSheet.Sort 
        .SetRange Range(Cells(6, "C"), Cells(RightEndRow, "NZ")) 
        .Header = xlNo 
        .MatchCase = False 
        .Orientation = xlTopToBottom 
        .SortMethod = xlPinYin 
        .Apply 
    End With 
     
     'turns back on the interface after making buttons
    On Error Resume Next 
    With Excel.Application 
        .DisplayAlerts = True 
        .EnableEvents = True 
        .EnableSound = True 
        .Cursor = xlDefault 
        .Calculation = xlCalculationAutomatic 
        .ScreenUpdating = True 
        .StatusBar = False 
    End With 
     
    Application.Goto Reference:=ThisWorkbook.ActiveSheet.Range("A1"), Scroll:=True 
    ActiveSheet.Protect "agichi", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Here is the code for my userform:

	VB:
	
 AddButton_Click() 
     
     'sets focus on the two listboxes so that they are equal to page1 if page2 was never clicked.
    Me.MultiPage1.Value = 1 
    Me.Capacity2.SetFocus 
    Me.HourlyRate2.SetFocus 
    Me.JobTitle2.SetFocus 
    Me.Region2.SetFocus 
     
    ActiveSheet.Unprotect "agichi" 
     
    Dim NewRow As Integer 
    NewRow = ActiveSheet.Range("GC500").End(xlUp).Row + 1 
     
    Dim EditButtonColumn As Integer 
    EditButtonColumn = 8 
     
    If Len(ConsultantForm.FirstName.Value) = 0 Then 
        MsgBox "The First Name field can not be left empty!", vbOKOnly 
        ConsultantForm.MultiPage1.Value = 0 
        ConsultantForm.FirstName.SetFocus 
        Exit Sub 
    End If 
     
    If Len(ConsultantForm.LastName.Value) = 0 Then 
        MsgBox "The Last Name field can not be left empty!", vbOKOnly 
        ConsultantForm.MultiPage1.Value = 0 
        ConsultantForm.LastName.SetFocus 
        Exit Sub 
    End If 
     
    If IsNull(ConsultantForm.JobTitle) Then 
        MsgBox "A Job Title must be selected!", vbOKOnly 
        Exit Sub 
    End If 
     
    If IsNull(ConsultantForm.Region) Then 
        MsgBox "A Region must be selected!", vbOKOnly 
        Exit Sub 
    End If 
     
    If Len(ConsultantForm.Capacity.Value) = 0 Then 
        MsgBox "The Weekly Capacity field can not be left empty!", vbOKOnly 
        ConsultantForm.MultiPage1.Value = 0 
        ConsultantForm.Capacity.SetFocus 
        Exit Sub 
    End If 
     
    If Not IsNumeric(ConsultantForm.Capacity.Value) And ConsultantForm.Capacity.Value  vbNullString Then 
        MsgBox "Sorry, only numbers are allowed in the Weekly Capacity field." 
        ConsultantForm.Capacity.Value = vbNullString 
        ConsultantForm.MultiPage1.Value = 0 
        ConsultantForm.Capacity.SetFocus 
        Exit Sub 
    End If 
     
    If Len(ConsultantForm.HourlyRate.Value) = 0 Then 
        MsgBox "The Hourly Rate field can not be left empty!", vbOKOnly 
        ConsultantForm.MultiPage1.Value = 0 
        ConsultantForm.HourlyRate.SetFocus 
        Exit Sub 
    End If 
     
    If Not IsNumeric(ConsultantForm.HourlyRate.Value) And ConsultantForm.HourlyRate.Value  vbNullString Then 
        MsgBox "Sorry, only numbers are allowed in the Hourly Rate field." 
        ConsultantForm.HourlyRate.Value = vbNullString 
        ConsultantForm.MultiPage1.Value = 0 
        ConsultantForm.HourlyRate.SetFocus 
        Exit Sub 
    End If 
     
    If Len(ConsultantForm.Capacity2.Value) = 0 Then 
        MsgBox "The Weekly Capacity field can not be left empty for Period 2!", vbOKOnly 
        ConsultantForm.MultiPage1.Value = 1 
        ConsultantForm.Capacity2.SetFocus 
        Exit Sub 
    End If 
     
    If Not IsNumeric(ConsultantForm.Capacity2.Value) And ConsultantForm.Capacity2.Value  vbNullString Then 
        MsgBox "Sorry, only numbers are allowed in the Weekly Capacity field." 
        ConsultantForm.Capacity2.Value = vbNullString 
        ConsultantForm.MultiPage1.Value = 1 
        ConsultantForm.Capacity2.SetFocus 
        Exit Sub 
    End If 
     
    If Len(ConsultantForm.HourlyRate2.Value) = 0 Then 
        MsgBox "The Hourly Rate field can not be left empty for Period 2!", vbOKOnly 
        ConsultantForm.MultiPage1.Value = 1 
        ConsultantForm.HourlyRate2.SetFocus 
        Exit Sub 
    End If 
     
    If Not IsNumeric(ConsultantForm.HourlyRate2.Value) And ConsultantForm.HourlyRate2.Value  vbNullString Then 
        MsgBox "Sorry, only numbers are allowed in the Hourly Rate field." 
        ConsultantForm.HourlyRate2.Value = vbNullString 
        ConsultantForm.MultiPage1.Value = 1 
        ConsultantForm.HourlyRate2.SetFocus 
        Exit Sub 
    End If 
     
     'turns off interface to speed up makeing buttons
    On Error Resume Next 
    With Excel.Application 
        .Calculation = xlCalculationManual 
        .EnableEvents = False 
        .EnableSound = False 
        .Cursor = xlWait 
        .StatusBar = "Working..." 
        .ScreenUpdating = False 
        .DisplayAlerts = False 
    End With 
     
    Rows(NewRow).Select 
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove 
     
    Range(Cells(NewRow + 1, "C"), Cells(NewRow + 1, "EA")).Select 
    Selection.AutoFill Destination:=Range(Cells(NewRow, "C"), Cells(NewRow + 1, "EA")), Type:=xlFillDefault 
     
    Range(Cells(NewRow, "AD"), Cells(NewRow, "CD")).Select 
     
    Selection.Interior.ColorIndex = xlNone 
    Selection.Characters.Text = "" 
     
    ActiveSheet.Cells(NewRow, "GC").Value = Application.WorksheetFunction.Trim(ConsultantForm.FirstName.Value) 
    ActiveSheet.Cells(NewRow, "GD").Value = Application.WorksheetFunction.Trim(ConsultantForm.LastName.Value) 
    ActiveSheet.Cells(NewRow, "GE").Value = ConsultantForm.JobTitle.Value 
    ActiveSheet.Cells(NewRow, "GF").Value = ConsultantForm.JobTitle2.Value 
    ActiveSheet.Cells(NewRow, "GG").Value = ConsultantForm.Region.Value 
    ActiveSheet.Cells(NewRow, "GH").Value = ConsultantForm.Region2.Value 
    ActiveSheet.Cells(NewRow, "GI").Value = ConsultantForm.Capacity.Value 
    ActiveSheet.Cells(NewRow, "GJ").Value = ConsultantForm.Capacity2.Value 
    ActiveSheet.Cells(NewRow, "GK").Value = ConsultantForm.HourlyRate.Value 
    ActiveSheet.Cells(NewRow, "GL").Value = ConsultantForm.HourlyRate2.Value 
    ActiveSheet.Cells(NewRow, "GM").Value = ConsultantForm.StartMonth.Value 
    ActiveSheet.Cells(NewRow, "GN").Value = ConsultantForm.StartDay.Value 
    ActiveSheet.Cells(NewRow, "GO").Value = ConsultantForm.StartYear.Value 
    ActiveSheet.Cells(NewRow, "GP").Value = ConsultantForm.EndMonth.Value 
    ActiveSheet.Cells(NewRow, "GQ").Value = ConsultantForm.EndDay.Value 
    ActiveSheet.Cells(NewRow, "GR").Value = ConsultantForm.EndYear.Value 
    ActiveSheet.Cells(NewRow, "GS").Value = ConsultantForm.StartMonth2.Value 
    ActiveSheet.Cells(NewRow, "GT").Value = ConsultantForm.StartDay2.Value 
    ActiveSheet.Cells(NewRow, "GU").Value = ConsultantForm.StartYear2.Value 
    ActiveSheet.Cells(NewRow, "GV").Value = ConsultantForm.EndMonth2.Value 
    ActiveSheet.Cells(NewRow, "GW").Value = ConsultantForm.EndDay2.Value 
    ActiveSheet.Cells(NewRow, "GX").Value = ConsultantForm.EndYear2.Value 
     
    ActiveSheet.Cells(NewRow, "C").Value = Application.WorksheetFunction.Trim(ConsultantForm.LastName.Value) & ", " &
Application.WorksheetFunction.Trim(ConsultantForm.FirstName.Value) 
    ActiveSheet.Cells(NewRow, "D").Value = ConsultantForm.Region2.Value 
    ActiveSheet.Cells(NewRow, "E").Value = ConsultantForm.JobTitle2.Value 
    ActiveSheet.Cells(NewRow, "F").Value = ConsultantForm.Capacity2.Value 
    ActiveSheet.Cells(NewRow, "G").Value = ConsultantForm.HourlyRate2.Value 
     
     ' dimensions of Edit Button
    Dim EditTop As String 
    EditTop = ActiveSheet.Cells(NewRow, EditButtonColumn).Top + ActiveSheet.Cells(NewRow, EditButtonColumn).Height * 0.1 
    Dim EditLeft As String 
    EditLeft = ActiveSheet.Cells(NewRow, EditButtonColumn).Left + ActiveSheet.Cells(NewRow, EditButtonColumn).Width * 0.1 
    Dim EditHeight As String 
    EditHeight = ActiveSheet.Cells(NewRow, EditButtonColumn).Height * 0.8 
    Dim EditWidth As String 
    EditWidth = (ActiveSheet.Cells(NewRow, EditButtonColumn + 1).Left - ActiveSheet.Cells(NewRow, EditButtonColumn).Left) *
0.8 
     
    ActiveSheet.Buttons.Add(EditLeft, EditTop, EditWidth, EditHeight).Select 
    Selection.OnAction = "EditButton" 
    Selection.Characters.Text = "Edit" 
    With Selection.Characters(Start:=1, Length:=6).Font 
        .Name = "Calibri" 
        .FontStyle = "Bold" 
        .Size = 11 
        .Strikethrough = False 
        .Superscript = False 
        .Subscript = False 
        .OutlineFont = True 
        .Shadow = False 
        .Underline = xlUnderlineStyleNone 
        .ColorIndex = 11 
    End With 
     
     ' dimensions of Delete Button
    Dim DeleteTop As String 
    DeleteTop = ActiveSheet.Cells(NewRow, EditButtonColumn + 1).Top + ActiveSheet.Cells(NewRow, EditButtonColumn + 1).Height
* 0.1 
    Dim DeleteLeft As String 
    DeleteLeft = ActiveSheet.Cells(NewRow, EditButtonColumn + 1).Left + ActiveSheet.Cells(NewRow, EditButtonColumn + 1).Width
* 0.1 
    Dim DeleteHeight As String 
    DeleteHeight = ActiveSheet.Cells(NewRow, EditButtonColumn + 1).Height * 0.8 
    Dim DeleteWidth As String 
    DeleteWidth = (ActiveSheet.Cells(NewRow, EditButtonColumn + 2).Left - ActiveSheet.Cells(NewRow, EditButtonColumn +
1).Left) * 0.8 
     
    ActiveSheet.Buttons.Add(DeleteLeft, DeleteTop, DeleteWidth, DeleteHeight).Select 
    Selection.OnAction = "DeleteConsultantButton" 
    Selection.Characters.Text = "Delete" 
    With Selection.Characters(Start:=1, Length:=6).Font 
        .Name = "Calibri" 
        .FontStyle = "Bold" 
        .Size = 11 
        .Strikethrough = False 
        .Superscript = False 
        .Subscript = False 
        .OutlineFont = False 
        .Shadow = False 
        .Underline = xlUnderlineStyleNone 
        .ColorIndex = 11 
    End With 
     
     'turns back on the interface after making buttons
    On Error Resume Next 
    With Excel.Application 
        .DisplayAlerts = True 
        .EnableEvents = True 
        .EnableSound = True 
        .Cursor = xlDefault 
        .Calculation = xlCalculationAutomatic 
        .ScreenUpdating = True 
        .StatusBar = False 
    End With 
     
    Application.Goto Reference:=ThisWorkbook.Worksheets("Consultants").Range("A1"), Scroll:=True 
     
    ActiveSheet.Protect "agichi", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True 
     
    ConsultantForm.Hide 
    Unload Me 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for your help!!

Hi, can anyone help me please?
I use this macro:

Private Sub Workbook_Open()
Dim Value As String
Dim MyValue As String

MyValue = InputBox("pls, insert password", "msg for RIT", "digit here")

If MyValue = "mxp01" Then 'rit Aspes
ActiveWorkbook.FollowHyperlink Address:="C:LPSYSTEMCopy1.dot", _
NewWindow:=True
End If

If MyValue = "mxp02" Then 'rit Calisti
ActiveWorkbook.FollowHyperlink Address:="C:LPSYSTEMCopy2.dot", _
NewWindow:=True
End If

Application.Quit
End Sub

In this way I can open files *.dot, but it is a little bit different if I open them doing a double click on them. cause using this macro I can edit the *.dot files.
I'm looking for a way to open them in the same way as the double click on them, creating a *.doc file.

Thank you so much Claudio

I haven’t seen this particular technique for having a temporary color band appear in the current row and column, so I’m sharing what I believe is unique. This technique mimics the results of Ivan Moala’s method, but has the advantage of allowing you to copy and paste cells or ranges (Mr. Moala’s doesn’t). This technique also uses conditional formats, but doesn’t “destroy” existing ones as you move about your worksheet.

So, here is the technique that uses defined names, XL4 macro language, conditional formats, and just a touch of VBA. First, select a worksheet where you want to use this technique. Then select Insert/Name/Define from the menu, and add the following three names (one at a time):

Name..... Refers to:
RowBand =AND(GET.CELL(2,ACTIVE.CELL())=ROW(),COLUMN() button to add Condition 2, choose Formula Is, and type in the box:

=OR(RowBand,ColBand)

Press the Format button again, and choose another color to use as the primary row and column band highlight. For this example, choose light yellow. (Light yellow has the interior color of 36, which matches the IntColor ‘test’.) Then press the OK button.

Now press Alt-F11 to open the Visual Basic Editor. Locate your workbook name in the Project window, and double-click it to expand its listing, if necessary. Then double-click the item labeled Sheet1 (or whatever name is appropriate for your worksheet). Then enter the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.ScreenUpdating = True
End Sub

Switch back to the worksheet, and move around. You should have row and column bands of light yellow wherever you move the cell pointer! Finally, somewhere on your spreadsheet, change a cell to the light yellow foreground color. When you move your cell pointer to this cell, the active cell’s row and column should turn to light blue.

Applicability and limitations of this technique:
I have used this technique successfully in Excel 97 and Excel 2000, with Windows 98, Windows NT4, and Windows XP operating environments. I don’t know if it will work in Excel 2002, although I hope it does.
First limitation: you can’t use the F8 key to extend a range selection, though Shift-F8 works fine if you hold down the Shift key. You also can’t copy any cells to another worksheet (except as values). If you try, Excel issues the warning message “Cannot paste that macro formula onto a worksheet”. (Sometimes, Excel 97 on the Windows NT4 platform crashes when you try to copy to another worksheet.)

Additional Considerations:
Note that if you don’t want to have the color bands change to light blue, don’t implement Condition 1, and don’t create the Defined Name ‘IntColor’. Then, for the Conditional Formatting cells, simply enter what was Condition 2 as Condition 1 instead [Formula Is =OR(RowBand,ColBand)]. Choose your desired interior color, and you still have the moving row and column bands--they just won’t change when you select a cell already colored light yellow. This also frees up one more conditional format, if that’s important to you.

Finally, to turn off the bands, you need to make a few adjustments. For the example listed above, select all cells again, and modify Conditional Format 1 this way:

=AND($A$1””,IntColor,OR(RowBand,ColBand))

and Conditional Format 2 this way:

=AND($A$1””,OR(RowBand,ColBand))

Then adjust the VBA code to read:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next
If [A1].Value "" Then
Application.ScreenUpdating = True
End If
End Sub

Now, if cell A1 is blank, the cell highlighting is turned off, as is the screen flicker that comes with the “Application.ScreenUpdating = True” VBA instruction. (Note: if A1 contains an error value, such as you get when dividing by 0, the highlighting will be turned off, but the flicker won’t). Of course, you can choose some cell other than A1 if you want, or devise some other test. Just make sure that you change the references appropriately.

Otherwise, happy Excel computing!

Tom McClain
Round Rock, TX

Hi guys, working my way through a large worksheet of figures which I need to sum according to date, and then calculate the amount of each date's figures with tax on top.

Autosum automatically places the sum of a column underneath the bottommost cell. Is there a way to put it at the side of the bottommost cell? Up to now I've been inserting a blank row beneath, then autosumming then dragging that cell to the right. Or else I can select the cells and type the sum in manually but it would be nice to tell Excel where I want the autosum result to be, without having to manually type in =sum(h29:h31) over and over.

Also, for my tax calculation I've been Ctr-dragging the formula downwards to apply it to the subsequent amounts. It retains the value of the earlier amount until I double-click on it to force a re-calculation. I'm sure I've had other formulas automatically update when I ctr-drag them so why not this one? =K150*(1+12.5%)

Thanks in advance for advice,
Jo

Hi guys,

I'm sure this is a common problem, but I haven't found a consistent work-around to it.

I have a column of ID numbers that I want to treat as text. Examples are: AB123143, 120320, 0024432, 3434B, ABASA, etc.

So I enter them, right-click the cells, choose Format Cells, and format them as text. Then I test it using the t() function on a different cell. I made sure the format of the t() cells is general. It works for the ones that start with a letter and the ones that start with a 0. Anything that looks briefly like a number doesn't work.

The next thing to do would be to pre-format a new cell as text, then copy and paste-special the value of the original cell into the new cell. This method works sometimes*, and I'm not sure I know why. When it works, I get a green "number stored as text" error. Yay to this error!

*Sometimes here means that when I try to start fresh with new numbers, it works, but when I try the same thing on old dataset, it doesn't.

----------------------------------
**Edit: So I went back and tried the pre-format method again. Apparently, the new formatting only takes effect after I double-click the cell containing the specially-pasted values and then clicking outside somewhere. The "number stored as text" error pops up after this, meaning success. Is this some type of refresh rate problem, similar to the automatically-evaluate functions option being turned off?
----------------------------------

The last thing would be to insert a single quotation mark in front of the value. This method always works. Of course, I don't want to do this for 1000 cells. Mind you, I know of the & and the concatenate tricks, but they aren't visually pretty (you can see the ' when you click on the cell).

So my question is this: What the hell is wrong with Excel, and is there one consistent solution to this problem?

Want to copy a cell down a column to its end?

Insert text, formula, etc into the first cell
click the cell to select it and double click the little black box on the bottom right of the selected cell and BANG everything is copied.

You can even select a couple of cells to start a sequence and it copies it to the end.

Now if only I could find a shortcut to convert all the formulas in a column to their values.

I have a csv file that needs to be formatted and then saved as a csv file, and i have written code to do this but i need all of the values in the csv file to have one set of double quotes but at present all of the values do not have any quotes, how can i go about this.

Here is an example of the formatted csv file,

******** ******************** ************************************************************************>Microsoft Excel - 303000.P30299999.CSV___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEFGHI1P302999924/01/20084529812829812821PARCEL2NXTDAY2P302999928/01/200836165002650021PARCEL2NXTDAY3P302999928/01/20085777842827842821PARCEL2NXTDAY4P302999928/01/200810011236581236581PARCEL2NXTDAY5P302999928/01/200810012058362058361PARCEL2NXTDAY6P302999929/01/200810012323562323561PARCEL2NXTDAY303000.P30299999 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I need the values to look like "PARCEL"

Hey quite a complicated question......

The following macro code updates a summary sheet with data contained in other workbooks. It works by the user clicking a button, selecting the file from the open file dialogue box and double clicking it. The cells I have determined in the code are then pulled from the file to the summary sheet.

I like how this works but I wonder if instead of prompting the user to select the file it can look at column A in the summary sheet and search for file containing that value and update as before but on the same row as the cell.

The code is as follows:

Code:
Sub Summary_cells_from_Different_Workbooks_2()
'This example use the function LastRow
    Dim FileNameXls As Variant
    Dim SummWks As Worksheet
    Dim ColNum As Integer
    Dim myCell As Range, Rng As Range, fndFileName As Range
    Dim RwNum As Long, FNum As Long, FinalSlash As Long
    Dim ShName As String, PathStr As String
    Dim SheetCheck As String, JustFileName As String
    Dim JustFolder As String

    ShName = "Front_Sheet"  '

Hi,

This is probably my most difficult section for the current workbook
that I'm producing.

To set the scene, I'm a teacher and I am creating a register and
markbook in excel. The reason being is that I'm aiming to be able to
actually use the data far more than if it was written down in on
paper.

However, I face a major hurdle. The register is taken during every
lesson and I personally like to undertake this process at the very
beginning of the lesson. The process needs to be very quick and
efficient so that the lesson can get underway as soon as possible.

At the moment I work with three possible options for a pupil - ontime,
late or absent. However, the hope is that this spreadsheet may get
adopted by other members of staff and they may want to tailor the
options to meet their requirements.

I have tried simply copying and pasting from a key of symbols to record
the info, but this proved too fidly and time consuming.

Validation also turned out to be too slow and the inability to display
the actually symbols in the drop down list made it next too useless.

I have tried out creating macros and assigning them to buttons and this
has proved out to be the most successful so far.

However, I have seen (and downloaded) a spreadsheet that allows a
simple click to input a tick into a column. It was very quick and
worked well. I was wondering if the code could perhaps be adapted in
some way to meet the results I want.

Can the number of clicks determine the symbol i.e. it sort of scrolls
through the possible options with a left mouse click and a double click
moves it to the next cell down?

Anyway, here's the code I downloaded -

Code written by Nick Hodge
15/11/04
Contained in this worksheet's code module
It detects a click in columns D or E and then inserts a tick mark. It
then moves to column G in the same row. Clicking on a tick, removes it
and moves you to column G

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iOffset As Integer
On Error GoTo err_handler
Application.EnableEvents = False
If Not Application.Intersect(Target, Columns("D:E")) Is Nothing Then
If Target.Column = 4 Then
iOffset = 3
Else
iOffset = 2
End If
If IsEmpty(Target.Value) Then
With Target
.Font.Name = "Wingdings"
.Value = Chr(252)
End With
Target.Offset(0, iOffset).Select
Else
Target.Value = ""
Target.Offset(0, iOffset).Select
End If
End If
err_handler:
Application.EnableEvents = True
End Sub

Any help would be great and I know some of you enjoy a challenge!

Thanks,

Mark.

--
mevetts

------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=495852

This is quite complex, but I cannot simplify further...

I programmed 2 cascading forms. The first one is to list GroupKeys and has a
Modify button. When the user selects a GroupKey and clicks the Modify button,
then the second form shows up and allows the user to edit the selected
GroupKey. The user can alternatively double-click on any listed GroupKey in
order to edit it.

The first form (frmGroupKeys) has a listbox and the Modify button. The
second (frmGroupKey) has a couple of textboxes and the OK button. (I include
below handlers of the double-click event on the listbox, of the initialize
event of the frmGroupKey form, and of the click event of the OK button)

THE ISSUE:
When the user edits a GroupKey by double-clicking on the listbox, then the
frmGroupKeys form remains disabled, even after the frmGroupKey form is
unloaded!

Interesting is that when the user edits a GroupKey using the Modify button,
then form frmGroupKeys gets back to "enabled" when frmGroupKey is unloaded.

I suspect that the listbox object is considered as the parent of the
frmGroupKey window, while the "entire" frmGroupKeys should be the one to be
enabled instead of just the listbox. I think it's a problem with the
implementation of the listbox as VBA class.

Can someone tell me if this is experienced somewhere else (I run WinXP).

Thank you and have a nice day.

Stefano Gatto
Geneva, Switzerland

Form frmGroupKeys:
Private Sub lstGroupKeys_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
btnModify_Click
End Sub

Form frmGroupKeys:
Private Sub btnModify_Click()
If lstGroupKeys.ListIndex = -1 Then Exit Sub
If lstGroupKeys.List(lstGroupKeys.ListIndex, 0) = gstrGroupKeyNotDefined
Then
MsgBox "This Group Key cannot be modified.", vbCritical + vbOKOnly,
"Error Modifying Group Key"
Exit Sub
End If
gstrCaller = "Modify"
frmGroupKey.Show
End Sub

Form frmGroupKey:
Private Sub UserForm_Initialize()
Dim rngRegion As Range
Dim rngCell As Range

Set rngRegion = ThisWorkbook.Worksheets("Region").Range("Region")
If rngRegion.Rows.Count > 2 Then
Set rngRegion = rngRegion.Offset(1, 0).Resize(rngRegion.Rows.Count -
2, 1)
For Each rngCell In rngRegion
cmbRegion.AddItem rngCell.Value
Next rngCell
End If
If gstrCaller = "Modify" Then
txtGroupKey.Value =
frmGroupKeys.lstGroupKeys.List(frmGroupKeys.lstGro upKeys.ListIndex, 0)
cmbRegion.Value =
frmGroupKeys.lstGroupKeys.List(frmGroupKeys.lstGro upKeys.ListIndex, 1)
End If
End Sub

Form frmGroupKey:
Private Sub btnOK_Click()
Dim rngGroupKey As Range
Dim rngCell As Range
Dim inti As Integer

txtGroupKey.Value = Trim(txtGroupKey.Value)
If txtGroupKey.Value = "" Or cmbRegion.Value = "" Then
MsgBox "Please enter a valid Country and Region before saving.",
vbCritical + vbOKOnly
txtGroupKey.SetFocus
Exit Sub
End If
Set rngGroupKey = ThisWorkbook.Worksheets("GroupKey").Range("GroupKe y")
If gstrCaller = "New" Then
Set rngGroupKey = rngGroupKey.Offset(rngGroupKey.Rows.Count -
1).Resize(1, 2)
rngGroupKey.Insert Shift:=xlDown
rngGroupKey.Offset(-1).Cells(1, 1).Value = txtGroupKey.Value
rngGroupKey.Offset(-1).Cells(1, 2).Value = cmbRegion.Value
gblnNeedsSaving = True
ElseIf gstrCaller = "Modify" Then
For Each rngCell In
rngGroupKey.Offset(1).Resize(rngGroupKey.Rows.Coun t - 2, 1)
If rngCell.Value =
frmGroupKeys.lstGroupKeys.List(frmGroupKeys.lstGro upKeys.ListIndex, 0) Then
rngCell.Value = txtGroupKey.Value
rngCell.Offset(0, 1).Value = cmbRegion.Value
gblnNeedsSaving = True
Exit For
End If
Next rngCell
End If
'updating customers table, since this table duplicates Region.
UpdateCustomersRegion txtGroupKey.Value, cmbRegion.Value
frmGroupKeys.SortGroupKeys
frmGroupKeys.FillGroupKeys txtGroupKey.Value
Unload Me
End Sub

I can email the workbook to whoever is so kind to help me.

Good day. I am using Excel 2002 with Windows ME. I have
a worksheet that contains product costs supplied to me by
my vendor in the following format;

$1.00 (US)
$12.00 (US)
$123.00 (US)
$1234.00 (US)

I have inserted two columns, one to contain a fomula and
the other to copy the formula results into.

The first column contains the formula shown below to
extract the " (US)" from each of the costs.

=LEFT(C2,SEARCH(" ",C2,1))

This appears to work although it does leave a single space
character behind the actual product cost. The worksheet
contains almost 300 different products and the cost for
each one.

After executing the formula shown above, I then copy the
entire column into the second column using Paste Special
and selecting Values. This appears to work also and
leaves me with the product cost with a left-justified
value with a dollar sign and a single space behind the
cost. Eventually I would like to get rid of the space.
Both of the new columns are formatted as Currency with 2
decimal places.

All appears to be OK. However, if I double-click any
single value in the second column and then click any other
cell, the value in the double-clicked cell loses its
currency formatting and changes to number format. This
loses the dollar sign and the value changes from left-
justified to right-justified. Also, if the value contains
a zero or a double zero as part of the cents value, that
zero is no longer shown even though the number format
calls for 2 decimal places.

I am at a loss on this one. If anyone has any
suggestions, please forward them. I could also some
advice on how to get rid of the space between the cost and
the "(US)".

Thanks, Danno...

I've cloned and adapted a vba code and designed a userform that does the
following:
Displays in a userform list box all the worksheets in an active workbook and
the number of rows in each sheet that contain data. When I double click on a
sheet name in the list box, or highlight it and then click OK, it takes me to
the specified sheet.
Problem: I want the userform list box to display all the sheets in the
workbook except those that I specify in the code. For example, my workbook
contains sheet 1, sheet 2, sheet 3, sheet 4, etc. and I want the userform
list box to display all the sheets except sheet 3. What do I need to insert
and where?
Here's the code:

Option Explicit

Public OriginalSheet As Object
Private Sub CommandButton1_Click()
OriginalSheet.Activate
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim SheetData() As String
Dim ShtCnt As Integer
Dim ShtNum As Integer
Dim Sht As Object
Dim ListPos As Integer

Set OriginalSheet = ActiveSheet
ShtCnt = ActiveWorkbook.Sheets.Count
ReDim SheetData(1 To ShtCnt, 1 To 4)
ShtNum = 1

For Each Sht In ActiveWorkbook.Sheets

If Sht.Name = ActiveSheet.Name Then _
ListPos = ShtNum - 1
SheetData(ShtNum, 1) = Sht.Name

Select Case TypeName(Sht)
Case "Worksheet"

SheetData(ShtNum, 2) = _
Application.CountA(Sht.Range("a3:a65000"))
End Select

ShtNum = ShtNum + 1
Next Sht

With ListBox1
.ColumnWidths = "162 pt;9 pt"
.List = SheetData
.ListIndex = ListPos

End With

End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Call OKButton_Click
End Sub

Private Sub OKButton_Click()
Dim UserSheet As Object
Set UserSheet = Sheets(ListBox1.Value)
If UserSheet.Visible Then
UserSheet.Activate
Else
If UserSheet.Visible = False Then
UserSheet.Visible = True
UserSheet.Activate
End If
End If
UserSheet.Activate
Unload Me
End Sub

Thanks
cavasta

First off I am not expert in this area.

I am using excel 2000 on WinXP Pro SP2.

I have used a vba function for calculating like NetworkDays function except this custom function (NetworkDaysMisc) can calculate the number working week weekdays that you specify (e.g Mon to Sat). Code can be found here -> http://www.bettersolutions.com/excel...I327411311.htm

this works very well and does what I want. Then under the doubleclick subroutine of the worksheet I have code to add and remove rows depending on what the cells contents is. Here is my code;
 
Sub InsertRow()
    Dim DeleteRow As Boolean
    
    DeleteRow = False
    
    If ActiveCell = "Double click here to add SR" Then
        Selection.EntireRow.Insert
        ActiveCell = "New SR Entry"
        Application.SendKeys "{F2}"
        Application.SendKeys "{HOME}"
        Application.SendKeys "+{END}"
        'ActiveCell.Offset(1, 0).Select
        'ActiveCell.Font.Italic = True
        'ActiveCell.Font.ColorIndex = 15
    Else
        DeleteRow = True
    End If
    If ActiveCell = "New SR Entry" And DeleteRow Then
        'ActiveCell.Offset(-1, 0).Select
        ActiveCell.EntireRow.Delete 'shift:=xlShiftToLeft
        Application.SendKeys "{F2}"
        Application.SendKeys "{HOME}"
        Application.SendKeys "+{END}"
    End If
End Sub

Ok. Now on my spreadsheet I have a cell to calculate my number of working days of a date range using the vba function like I first stated. The cell formula looks like this

=module1.networkdaysmisc(K2,K3,I34:I48,J18:J24)

So now the problem I am experiencing that I hope someone can help me with please. When I double click my cell with contents of "Double click here to add SR" and new row is inserted above that cell. And the cell that uses the vba function to calculate the workdays displays correct value. But then if I double click the cell with contents "New SR Entry" that is in the newly created row, the row deletes as it should but now the cell calculating the networkdaysmisc becomes #VALID!. And I cant work out why? If I go to the cell using the networkdaysmisc function I find the reference cells and ranges are correct and so all I need to do on this cell is enter edit mode (F2 key) then press enter and is now display correctly.

I have tried moving/relocating the cell reporting the networkdays up above where the rows can be added and deleted but this make no difference. So it make me think that something like vba code needs to be restarted. I dont know really.

I figure I need to add some code, or the sheet is not refreshing/updating. Can anyone figure this out and help me?

I have though of vba code to move to this problem cell (ActiveCell.Offset(2,0).Select) and simulate the F2 and enter keys. But there must be a better way and a reason why this happen and how to avoid this problem.

Thanks in advance,

Paul.

Hi Guys,

I am trying to graph something for school. The instructions they gave me are:

1) Highlight all the values you typed in
2) Click insert, and then on chart
3) Click XY scatterplot
4) Fill in the appropriate titles
5) Double left click on the Y-axis (any number should do)
6) Goto the scale tab, and click the box that says linear scale
7) You now have a linear graph

But, when I double click the Y-Axis, and click the scale tab, there is no box that says "linear scale". All I see is "logarithmic scale", "values in reverse order", and "Value (X) crosses at maximum value.

Am I doing something wrong?

The indicated part of the following code needs to be modified in such a
way that the row of the double clicked cell is duplicated for editing.
There are no formulae involved. The new row is to be inserted directly
below the active cell.

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
On Error GoTo err_handler
With Target
If Not Intersect(Target, Range("N2:N6")) Is Nothing Then
If Not IsEmpty(.Value) Then
ThisWorkbook.FollowHyperlink ThisWorkbook.Path _
& "" & .Value
End If
End If
'------------------ This is the code of interest ------------------
If .Row > 8 Then
Range(Cells(ActiveCell.Row, "A"), _
Cells(ActiveCell.Row, "Z")).Insert xlDown
Range("B" & ActiveCell.Row).Select
On Error GoTo 0
End If
'-----------------------------------------------------------------------------
End With
Exit Sub

err_handler:
MsgBox "An error has been made" & vbCrLf _
& "File name not recognised.", _
vbExclamation, "Error Notice"
End Sub

All suggestions gratefully received.

Geoff

This is quite complex, but I cannot simplify further...

I programmed 2 cascading forms. The first one is to list GroupKeys and has a
Modify button. When the user selects a GroupKey and clicks the Modify button,
then the second form shows up and allows the user to edit the selected
GroupKey. The user can alternatively double-click on any listed GroupKey in
order to edit it.

The first form (frmGroupKeys) has a listbox and the Modify button. The
second (frmGroupKey) has a couple of textboxes and the OK button. (I include
below handlers of the double-click event on the listbox, of the initialize
event of the frmGroupKey form, and of the click event of the OK button)

THE ISSUE:
When the user edits a GroupKey by double-clicking on the listbox, then the
frmGroupKeys form remains disabled, even after the frmGroupKey form is
unloaded!

Interesting is that when the user edits a GroupKey using the Modify button,
then form frmGroupKeys gets back to "enabled" when frmGroupKey is unloaded.

I suspect that the listbox object is considered as the parent of the
frmGroupKey window, while the "entire" frmGroupKeys should be the one to be
enabled instead of just the listbox. I think it's a problem with the
implementation of the listbox as VBA class.

Can someone tell me if this is experienced somewhere else (I run WinXP).

Thank you and have a nice day.

Stefano Gatto
Geneva, Switzerland

Form frmGroupKeys:
Private Sub lstGroupKeys_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
btnModify_Click
End Sub

Form frmGroupKeys:
Private Sub btnModify_Click()
If lstGroupKeys.ListIndex = -1 Then Exit Sub
If lstGroupKeys.List(lstGroupKeys.ListIndex, 0) = gstrGroupKeyNotDefined
Then
MsgBox "This Group Key cannot be modified.", vbCritical + vbOKOnly,
"Error Modifying Group Key"
Exit Sub
End If
gstrCaller = "Modify"
frmGroupKey.Show
End Sub

Form frmGroupKey:
Private Sub UserForm_Initialize()
Dim rngRegion As Range
Dim rngCell As Range

Set rngRegion = ThisWorkbook.Worksheets("Region").Range("Region")
If rngRegion.Rows.Count > 2 Then
Set rngRegion = rngRegion.Offset(1, 0).Resize(rngRegion.Rows.Count -
2, 1)
For Each rngCell In rngRegion
cmbRegion.AddItem rngCell.Value
Next rngCell
End If
If gstrCaller = "Modify" Then
txtGroupKey.Value =
frmGroupKeys.lstGroupKeys.List(frmGroupKeys.lstGroupKeys.ListIndex, 0)
cmbRegion.Value =
frmGroupKeys.lstGroupKeys.List(frmGroupKeys.lstGroupKeys.ListIndex, 1)
End If
End Sub

Form frmGroupKey:
Private Sub btnOK_Click()
Dim rngGroupKey As Range
Dim rngCell As Range
Dim inti As Integer

txtGroupKey.Value = Trim(txtGroupKey.Value)
If txtGroupKey.Value = "" Or cmbRegion.Value = "" Then
MsgBox "Please enter a valid Country and Region before saving.",
vbCritical + vbOKOnly
txtGroupKey.SetFocus
Exit Sub
End If
Set rngGroupKey = ThisWorkbook.Worksheets("GroupKey").Range("GroupKey")
If gstrCaller = "New" Then
Set rngGroupKey = rngGroupKey.Offset(rngGroupKey.Rows.Count -
1).Resize(1, 2)
rngGroupKey.Insert Shift:=xlDown
rngGroupKey.Offset(-1).Cells(1, 1).Value = txtGroupKey.Value
rngGroupKey.Offset(-1).Cells(1, 2).Value = cmbRegion.Value
gblnNeedsSaving = True
ElseIf gstrCaller = "Modify" Then
For Each rngCell In
rngGroupKey.Offset(1).Resize(rngGroupKey.Rows.Count - 2, 1)
If rngCell.Value =
frmGroupKeys.lstGroupKeys.List(frmGroupKeys.lstGroupKeys.ListIndex, 0) Then
rngCell.Value = txtGroupKey.Value
rngCell.Offset(0, 1).Value = cmbRegion.Value
gblnNeedsSaving = True
Exit For
End If
Next rngCell
End If
'updating customers table, since this table duplicates Region.
UpdateCustomersRegion txtGroupKey.Value, cmbRegion.Value
frmGroupKeys.SortGroupKeys
frmGroupKeys.FillGroupKeys txtGroupKey.Value
Unload Me
End Sub

I can email the workbook to whoever is so kind to help me.

I am using MS Excel 2003

I have asked this question before : http://www.excelforum.com/excel-new-...-and-over.html at the time I responded but had not actually tried the suggestion. Next day I was moved in my department so I never got the chance to try the sugestion. Now I am back and being asked once again to see if I can clean up and fix most of the problems with the office spread sheets. I tried the suggestion, I enter the both by typeing it and useing the Ctrl+;. Then went to sheet 2 and entered in the cell where I want the next date =sheet1!A1+1. My formula was automaticaly fixed to read ="[Sheet 1]1"!A1+1. Now when I entered the formula and pressed enter I got a windown UPDATE VALUES SHEET 1 and was asked to point to the work sheet I was working on. I doubled clicked the work sheet and the value I got was not todays date plus one day 12/05/2008(formated to read December 5 2008) but January 1 1900.The value on sheet 1 cell A1 is simply 12/04/2008. What I am trying to do is enter a Static Date on sheet 1 which represents day 1 of the month, sheet two is day 2 of the month and so on. I will then copy the whole sheet and paste for each day of the month so in the end there are 30 or 31 sheets in the workbook. I save the workbook with no data in it just the formtting and formulas so that when next month arrives all I have to do to change the date for each sheet is enter the date 1 time on sheet 1 and the rest of the dates for that month are automaticly inserted.

Thank you
Ken

Hi everyone! I'm looking to have my stopwatch run continuously until "and only until" a command button ("Stop") is clicked. I already have a code and stopwatch in place. I have 5 command buttons: start (starts the timer), call (activates/hyperlinks to another sheet where I have more command buttons and data entry needed/), stop (stops the timer), reset (resets the timer), and record (stamps the elapsed time on the sheet1).

They work great BUT the problem is, when the cell is touched (i.e. when you type a character or double click on any cell even if it's on a different sheet, or even on a different workbook) the timer stops!

I'd think that all this would require just a simple string/line inserted in my code. I'm really, really new to VBA and doesn't know how to write my own codes yet. I have to google what I need every time and do trial and error! Just to give you more info-- I'm using this for a call simulation program, in which the user is an operator taking in a call. She would have to be aware of how much time have passed after performing some actions/data entry on the different sheets on the workbook. I hope I've explained it enough. :D Anyway's here's the code I got, which I think needs just a few edits. Thanks in advance guys!

Option Explicit

Dim timelimit
Dim remaining
Dim stopped
Dim StartTimer

Public Sub startClock()

Dim start
Range("b6").Select
    start = Timer
    
    Do While stopped = True
        DoEvents
        Worksheets("Stopwatch").Range("F3").Value = Int((Timer - start + 0.5) / 60)
        Worksheets("Stopwatch").Range("H3").Value = (Timer - start) Mod 60
        Worksheets("Stopwatch").Range("I3").Value = (Timer - start + 0.5) - (Int(Timer - start + 0.5))
    Loop
       
End Sub
Private Sub cmdStart1_Click()
    startClock
     
End Sub

Private Sub cmdStop1_Click()
    stopped = False
End Sub

Private Sub cmdReset1_Click()

    stopped = True
    Worksheets("Stopwatch").Range("F3").Value = 0
    Worksheets("Stopwatch").Range("H3").Value = 0
    Worksheets("Stopwatch").Range("I3").Value = 0

End Sub

Private Sub CommandButton1_Click()

ActiveCell.Value = Range("f3").Value
ActiveCell.Offset(0, 1).Value = Range("h3").Value
ActiveCell.Offset(0, 2).Value = Range("i3").Value
ActiveCell.Offset(1, 0).Select

End Sub

Sub CommandButton2_Click()

    With Windows("File.xls").Activate
    Sheets("Sheet1").Range("B1").Select
    
End With
End Sub



No luck finding an answer? You could always try Google.