Excel Excel: how to set a default value to a cell without using formula?

[devik]

I want to set a default value to the cell without using formula.
I also do not want to use a VB script.
if the cell is blank/empty it should automatically take a value for it. if user mentions a value then it should take user's value.

Can this be done?

Thank you for your help.

 

[bodhi]

Im not sure you'll manage to do this without a formula to be honest mate.

 

[devik]

Thank you, for the note.

The problem using formula is, once you have entered any value, the formula goes off and in this case it has to be entered every time manually. And I want that cell itself to check if there is any value entered manually in it, if not then a default value should appear in it...

 

[MalcolmW]

Perhaps I am missing the point but have to wonder if protecting the cell would fulfil what you need? In other words a user would not be able to alter the cantents of a cell thereby protecting its contents be it a formula or a manual entry prior to protection.

 

[devik]

Thank you for your input, Malcolm.

Actually the thing is that I want users to edit the value in the cell if they want. You can get the idea about what exactly I want to do, from the points below.

• When the worksheet gets opened there is a default value in the cell
  
• if user wants to change the value, should be able to do so (so we can not protect the cell)
  
• If user thinks that the value he/she entered in the cell is not correct and deletes changed value to get the default value, then the cell should show the value assigned. (here we can not use formula because if you type in something, it will get erased)

 

[MalcolmW]

Unable to add anything I asked your question in Woody's Lounge http://www.wopr.com/cgi-bin/w3t/wwwthreads.pl?Cat=. The response from HansV follows:

One option is to create a macro that restores the default value. Users can run this macro when they need it. Say that the cell is B3 and the default value is 37.

Sub RestoreDefault
Range("B3") = 37
End Sub

Another option is to use three cells.
Say B1 contains the default. This cell is locked.
The user can enter a value in B2.
Cell B3 is locked again, and this contains the formula =IF(B2="",B1,B2). If the user enters a value in B2, this is returned, otherwise the default value from B1 is returned. You can use B3 in further calculations.

The attached JPG image refers.

HTH

 

[devik]

Thank you so much for helping me to resolve this problem.

I can use the second option; and if I do not find the solution I will definitely use this.

Just to give you an idea, I am creating a time sheet which can be used to track the time that one spends on different activities in a day/week/month/year.

Please see the attached screenshot. It is not a complete sheet; it is a very small part of it.

The red – selected cell requires default dynamic value to it. User is supposed to mention login time there which varies every day.

Thanks again!

 

[Win2Kuser]

Not quite sure what you are asking, but if you require a macro/VBA code to insert data into a cell, you can use the following;

With Worksheets("Sheet1").cells(y,x)
  .value= [i]default value[/i]
End With

This will insert the default value in cells y,x on the worksheet named 'Sheet1'

The benefits of doing this means that you can do loops etc whcih change the value of Y each loop so that entire ranges can have the same default data written to it, or insert the default value if the cell is blank etc...

You could even use a simple for...next loop as follows;

For Y = [i]first value[/i] TO [i]second value[/i]
With Worksheets("Sheetname").Cells(Y,X)
  If .value="" Then
    With Worksheets("Sheetname").Cells(y,x)
      .value=[i]default value[/i]
    End With
  End If
End With
Next Y

The downfalls are though that the macro would have to be run each time to check that data had been placed into this cell and insert the default value if it hasn't.

Hope that helps

 

[MalcolmW]

Thank you so much for helping me to resolve this problem. I can use the second option; and if I do not find the solution I will definitely use this.

Another reply to my enquiry on your behalf follows:
If you want the reversion to the default value to be automatic, then you could put the following code in the worksheet change event routine:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2")) Is Nothing Then
If Range("C2").Value = "" Then
Range("C2").Value = 1234
End If
End If
End Sub

 

[devik]

Thank you for your response, Win2Kuser and Malcolm.

The thing is most of the people who are going to use the sheet are not that familiar with Excel. Hence, I am not much in favor of using macro/VBA code, and trying to create the sheet without it. Also I think, from usability perspective using macro/VBA code is not that good option for a user-friendly interface.

I think I might go with the option that Malcolm suggested earlier.

I will post it here incase I find some other solution.

 

[Win2Kuser]

The other way of doing it perhaps although it does require the use of a macro is to simply have a single macro that runs when the worksheet is opened that simply puts the default value in the applicable cell. The user is then free to change it if required, and it doesn't mean that you have to hide 2 rows/columns either side of the cell in question. This would happen behind the scenes and the user would be none the wiser

To autostart a macro in Excel btw, you need to go into the VB editor and right click on the 'ThisWorksheet' on the left hand side and click show code, a blank box will then open. Next, click the drop down that has (General) in it, and select 'Workbook', the sub heading will then automatically be created called 'Private Sub Workbook_Open()' All's you need to do then is put the relevant code in there. This way,as soon as the workbook is loaded, that code will be ran before anything else happens; there are no forms popping up, no code shown or anything, it will simply do that before anything else.

Hope that helps...

 

[farha]

Not quite sure what you are asking, but if you require a macro/VBA code to insert data into a cell, you can use the following;

With Worksheets("Sheet1").cells(y,x)
  .value= [i]default value[/i]
End With

This will insert the default value in cells y,x on the worksheet named 'Sheet1'

The benefits of doing this means that you can do loops etc whcih change the value of Y each loop so that entire ranges can have the same default data written to it, or insert the default value if the cell is blank etc...

You could even use a simple for...next loop as follows;

For Y = [i]first value[/i] TO [i]second value[/i]
With Worksheets("Sheetname").Cells(Y,X)
  If .value="" Then
    With Worksheets("Sheetname").Cells(y,x)
      .value=[i]default value[/i]
    End With
  End If
End With
Next Y

The downfalls are though that the macro would have to be run each time to check that data had been placed into this cell and insert the default value if it hasn't.

Hope that helps

Hi,
can you please explain me this code

 

[farha]

Hi,
i got a task first sheet having all the field names with the length, start position, end position and column name of second sheet (like a,b,c...etc) and second sheet customer send the data only some fields(suppose i have 100 fields in sheet 1 customer will send the 50 fields data) we need copy the data based on column name in sheet 1 to another excel sheet can you please help me with the code i am new to excel macros.

Thanks in advance