Posts by alring

Hi there

I have aproblem that I do hope someone can help me with:
In Cell A1 I have a part of a path as text: C\mads\private\
In Cell A2 I have the rest of the path still as text: [min 2015.xlsm]Feb'!$C$9. This part of the path is getting information from cell C9 on Sheet Feb.
In A3 I want to write the two parts of the path as a formula so I can get the informaion from document min 2015.xlsm ceel C9 on sheet Feb. Is that possible??

Thank you in advance.

Regards
Mads Alring

Re: Constant Date In Adjacent Cell On Enter

Hi Dave

I'm not sure what you mean but as I understand it then I can overrule that Excel write a new date (in cell B1) the day after when the document is opened again. But I don't know how many times the document have to open. As i understand you then I have to use VBA.

Best Regards
Alring

Re: Constant Date In Adjacent Cell On Enter

Hi There

Thank you for your help. It seems that I have to use VBA to make it work. I don't like that but that's not your fall.

Anyway thank you for your help.

Best regards
Alring

Re: Constant Date In Adjacent Cell On Enter

Thank you PCI for your reply. It's working but I was hoping that it was possible to make it in a formula and not in VBA.

Any way thank you for your help.

Regards Alring

Hi there

In Cell A1 I want to write a name, then Excel shall write the date for today in cell B1. I know how to do that. The problem is that the formula I use in cell B1 is:
[bf]=IF(ISTEXT(A1);TODAY();" ")[/bf]
This formula will today write 28/2-08 but tomorrow the cell will change to 29/2-08 without changing the name in cell A1. How can make it so that cell B1 don't change tomorrow. Meaning how to make the date in cell B1 to be constant or fixed or what to call it.

Thank you in advarce for the help.
Alring

Re: Disable Cells

You can also try this code here:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R As Range
    Set R = Application.Intersect(Target, Range("A1,A2"))
    If R Is Nothing Then Exit Sub
    ActiveSheet.Unprotect
    If Range("A1").Value > "" Then
        Range("A2").Select
        Selection.Locked = False
        Selection.FormulaHidden = False
    End If


        ActiveSheet.Protect
End Sub

This code should unlock cell A2 when you type in A1. Just remember to have cell A1 unlocked.
Alring

Re: More Than 24 Hour In Userform

Hi Andy

Thank you for the help. It works perfect.

Alring

Hi

I have made a userform where I calculate how long time an operation takes. If the time fe.g. is 25 hours and 24 minutes then I get the result 1:24.

I have attached my userform as it looks now. If you write 540 in the bar and 550 in volume and press "Beregn" then "Norm tid + 10%" will write 1:24 and not 25:24.

Is it possible to have the Userform to write 25:24 or 1day and 1 hour and 24minutes?

Thank you in advance.
Alring

Re: Finding First Empty Cell

Hi Krishnakumar

See that one was a very good one.

Thank you for that one. I will use that one.

Thank you.

Alring

Re: Finding First Empty Cell

Hi pangolin

Thank you for the help. It works perfect when I changed the macro a little bit. I wanted that the value from cell C1 was going to be wroted in the empty cell. So I changed the macro to:

Dim mycell As Range
    For x = 1 To 11
        Set mycell = Sheets("sheet1").Range("B9").Offset(x, 0)
        If IsEmpty(mycell.Value) = True Then
            mycell = Range("C1").Value
            Exit Sub
        End If
    Next x

Krishnakumar: Because I wantet to have the value from Cell C1 then I can't use your code. But thank you for your time anyway.

Alring

Hi

I want to use VBA to find the first empty cell in a colum. But I want VBA to start look for this empty cell after cell B10 and not from the top of the colum. I also want that the VBA stop looking after cell B20. The last part I already have (see below) but I don't know how to have VBA to start the search in cell B10 and not in the top.

Range("B20").End(xlUp).Offset(1#).Value = Range("C1").Value

This is what i have so fare. Can someone please help me with my question?

Thank you in advance.

Alring

Re: Web Query of numbers

Hi Nashkat

I'm not sure want you want. But I think it's this you want.

Right click on the cells were you want to have thoose numbers and under Custom you wright 0000###############0

Try this.

Alring

Re: Query with If Statement

If it's text you write in cell E4 then try

=IF(ISTEXT(E4);E3*H4;" ")

if it's numbers you write in cell E4 then try

=IF(ISNUMBER(E4);E3*H4;" ")

You can also use

=IF(OR(ISTEXT(E4);ISNUMBER(E4));E3*H4;" ")

Re: Query with If Statement

Hi alic

Try this:

a = Range("E3")
    b = Range("H4")
    If Range("E4") > "" Then
    Range("I4") = a * b
    End If
    
End Sub

Alring

Re: Unlock a cell acording to another cell

Hi DOC

Thank you for the help. Now my boss likes me again. You have saved me.

Thank you so much.

Regards
Alring

Re: Unlock a cell acording to another cell

Sorry I forgot one thing. There will be orther cells on the sheet with text. And I don't want that is't possible for the user to change anything in those cells. That's why all cells have to be locked until the cell above have be filled out. It's not nessesary that the cells that the user have filled out is locked after the cell have be filled out.

Re: Unlock a cell acording to another cell

Hi,

The cells are empty. I want that the user have to fill in cell C3 before he/she can write in cell C4. And C4 have to filled before they can write in cell C5. And so on and so on.

Regards
Alring

Re: Unlock a cell acording to another cell

It's me again.

I have done what Doc wrote to me. And it's working perfect. I thoght it then was plug and play to do it with the next cells. But it's not. Now i have this code here:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A As Range
    Set A = Application.Intersect(Target, Range("C3"))
    If A Is Nothing Then Exit Sub
    ActiveSheet.Unprotect
    Range("C4").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    
    Dim B As Range
    Set B = Application.Intersect(Target, Range("C4"))
    If B Is Nothing Then Exit Sub
    ActiveSheet.Unprotect
    Range("C5").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
   
    Dim C As Range
    Set C = Application.Intersect(Target, Range("C5"))
    If C Is Nothing Then Exit Sub
    ActiveSheet.Unprotect
    Range("C6").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True


End Sub

It will only onlock cell C4. When I then try to wite in Cell C5 then Excel tells me that the cell is protected.

What am I doing wrong??

Alring

Re: Unlock a cell acording to another cell

Hi DOC

Thank you for the help. You have just saved my job.

Thnak you.

regards
Mads

Hi there

I am trying to make a sheet that is protected. It's protected in that way that all cells are protected and locked except cell C3. When someone then type anything in cell C3 and leave the cell then cell D3 automatic unlock. I know how to do it with a macro. But I don't want that the user have to press the macro-buttom to unlock cell D3. It should unlock cell D3 automatic.

Is there someone that can help me with this "small" problem.

Thank you in advance.

Regards
Alring