Posts by corkant

I was given an issue with the simple problem of working out when a combination of parts had been completed.

Column A is the date of manufacture

Column B is the first component: PSU

Column C is the second component: Top Board

Column D is the third component: Fan

50 of each of the 3 components can be manufactured each day, but it's more likely that only 50 of one, or two, components will actually be produced.

I need to mark the completion of 50 of each component when it occurs with a 1 in column E.

I have found a solution in the attached but there must be a better methodology?

In the attached, I’ve used columns H, I, and J to get the cumulative values which shows the change of totals per column.

In columns K, l, and M, I’ve got the change of values from the cumulative columns H, I, and J.

Column N returns the unique values from K, L, and M.

Column O returns the last row number of the unique values across columns K, L, and M. (This is where each of the 3 columns obtains a unique 50, 50, and 50)

Finally, we can return to column E to display a 1 to match the current row number minus 1, because the data starts on Row 2 of the worksheet, and if there’s no match leave the cell empty, otherwise it matches and returns a 1 in the correct row.

I also created the same idea with a formatted table to allow expansion of extra rows which I like better but I still feel there must be something less complicated.

Any ideas appreciated.

Multi-Parts Log.xlsx

Re: Hiding an Excel 2007 Table Name from the Name Box

Quote from pulsar777;767902

Hi, anyway, is there a way to hide the table names from a name box and name manager? Excel.Name object seems to work only for defined names.

I went with RoyUK's idea to:

Quote

If you don't want users to use the Table then put it on a hidden sheet

Re: Hiding an Excel 2007 Table Name from the Name Box

I'll go with the hidden sheet idea. It's simple and it works.

Thanks for the ideas.

Quote from royUK;676857

Where are you seeeing the name of the table? It does not appear in the Name Box.

Hiding the name anywhere will not stop people navigating to it. As i said in my first post if you don't want them to do that then put it on a hiddene sheet.

Re: Hiding an Excel 2007 Table Name from the Name Box

I've tried that in Office 2007 and 2010 and it works in neither.

Re: Hiding an Excel 2007 Table Name from the Name Box

I'm afraid that doesn't work. I tried a few variations of looping though ListObjects and hiding, setting visible to false, etc but nothing seems to work that I am aware of.

I just need that one line of code that says:

Table 1 name hide it from the name box or go to dialog box!

I appreciate the reply though.

Quote from royUK;676503

You can hide the name from the nameBox

Code

Option Explicit


Sub Hidename()
 Dim objName As Excel.Name
   
        For Each objName In Application.ActiveWorkbook.Names
        If objName = "Table1" Then objName.Visible = False
        Next objName
   
End Sub

Display More

If you don't want users to use the Table then put it on a hidden sheet

Re: Hiding an Excel 2007 Table Name from the Name Box

Quote from cytop;676390

A ListObject does not have a VISIBLE property...

I noticed that. I was hoping to find a way to make it not seen though.

I can hide named ranges easily enough but I'd rather use a table for my current project.

Any ideas please?

Is it possible to hide an Excel 2007 Table Name from appearing in the Name Box.

(I want to prevent users navigating to my table by selecting it there)

I tried ideas like:

ListOjects("Table1").Name.Visible = False

But had no success and I can't seem to find any help anywhere about it.

Re: Extracting a date out of cells

I found the solution!

CountLarge is what is needed in Excel 2007/2010 so I added in the following code at the start of my code:

If Selection.Cells.CountLarge > 1048576 Then
    MsgBox "Too Many cells selected - 1,048,576 cells is the maximum allowed.)"
Exit Sub
End If

This limits the selection to more than enough for the user!

I hope this helps someone else in the future.

Hi there, I'm having a problem with my code which extracts a date out of a cell.

It seems to work OK until a user very lazily selects all the cells on an Excel 2007 or 2010 worksheet in which case the macro crashes despite my error handler.
(I get a Run-time Error '1004' Application-defined or object-defined error.)

My code:

Option Explicit




Sub FixDatesForKatie()




Dim MyDate As Long
Dim MyRange As Range
Dim CellCount As Long
On Error GoTo ProblemCell




Set MyRange = ActiveSheet.Range(ActiveWindow.Selection.Address)




    For CellCount = 1 To MyRange.Cells.Count
        If MyRange.Cells(CellCount).Value = "" Then GoTo RESUMEHEREAFTERERROR 'checks for empty cell
        If MyRange.Cells(CellCount).Value < 1100000 Then GoTo ProblemCell 'checks for date before 2010
        If Not MyRange.Cells(CellCount).HasFormula Then 'If it's not a formula proceed
            MyDate = MyRange.Cells(CellCount).Value
                MyRange.Cells(CellCount).Value = DateValue(Right(MyDate, 2) & "/" & Mid(MyDate, 4, 2) & "/20" & Mid(MyDate, 2, 2))
                'MyRange.Cells(CellCount).Value = MyDate
                MyRange.Cells(CellCount).NumberFormat = "dd/mm/yyyy"
        End If
        
RESUMEHEREAFTERERROR:
    Next CellCount
    Set MyRange = Nothing
    Exit Sub
    
ProblemCell:
    MsgBox "There was a problem with cell " & MyRange.Cells(CellCount).Address
    Resume RESUMEHEREAFTERERROR:




End Sub

In the problem cells, the date is in the format of a 1 followed by 2 digits for the year, 2 digits for the month and 2 digits for the day:
1130124 is 24th Jan 2013
Any cells that are not in that format should be left alone.

Can anyone spot how to handle if the user selects too many cells please?

Re: Combobox That Can't Be Deleted

Thanks a million folks for looking into it - it's driven me quite insane.

I've removed it with the help of jolivanes macros rather than deleting columns, rows, or cells.

The file that I needed to fix was originally created in Excel 2003 on Windows XP and has subsequently evolved into the 52nd version of the workbook which is now using Excel 2007 on Windows 7.

Someone down the line must have inserted / copied & pasted something into the file but kept quiet about it!

Problem solved but I'd still love to know exactly what the shape/control is and where it came from!

Hi there,
In an Excel file that I've received there is a drop-down control that I cannot delete/remove.
It's not data validation, and it doesn't seem to be an activeX or form control.
I've created a copy, attached. The control is in Cell C5 and does nothing except display a wide empty box.

Any help please to remove it?

(deleting the column removes it but I really don't want to do that in my work book)

Thanks for any thoughts.

Re: Prevent Someone From Entering A Formula In A Cell

Quote from royUK

So what prevents them from using a different worksheet or a calculator?

Well nothing I guess, but I intend to use this in a classroom environment so I can supervise a few issues... I just want to test out whether a user actually understands what a formula is calculating and the order by which it calculates.

Re: Prevent Someone From Entering A Formula In A Cell

Quote from zimitry

Try changing your Cell format to text that should prevent an answer showing... Remember to protect your sheet so they can't change the format of the cell

HTH

z

I love simple solutions - thank you for the fast reply! That worked superbly...

I'm creating a maths workbook in Excel. I want the user to workout the answer in his/her head and enter the answer into Excel. The only problem is, of course, that there is nothing stopping a user from simply typing a formula to obtain the answer! I have tried all data validation, apart from Custom which I can't figure out.

Is there a way to prevent a user entering a formula on a worksheet?