Automatically insert a 1 if a cell is selected

[griffym]

I need to be able to automatically insert a 1 when a cell is selected is there a way of doing this. Cells that are not selected remain blank.

I need to apply this to a particular set of cells only so rest of worksheet acts as normal

 

[Joe4]

Yes, this can be done with a Worksheet_SelectionChange event procedure. An Event Procedure is VBA that is automatically run upon some event happening (like the selection of a cell).

Here is a helpful link: Worksheet Selection Change Event, Excel VBA

If you want to see if a cell within a particular range is selected, you can use "Intersect", as shown in some of the examples here: Worksheet Change Event, Excel VBA

If you have any issues applying this, please respond back with more details (i.e. what your range is).

 

[griffym]

Hi I have applied a Worksheet Selection Change Event which will now change any cell to a 1 if selected but I cant figure out how to make it just to my range which is Cells B3:N33
my code thus far is (this was based on getting column 2 to be a 1 when selected I now need to expand it (if i do xx instead of 2 i get the whole sheet but ideally I need just a the cells identified above)
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Columns.Count = 1 And Target.Rows.Count = 1 And Target.Column = 2 Then
Target.Value = 1
End If
End Sub</code>

 

[Joe4]

Try this:

Private [COLOR=#00008B][FONT=Consolas]Sub[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] Worksheet_SelectionChange[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]([/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]ByVal[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] Target [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]As[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] Excel[/FONT][/COLOR][COLOR=#000000][FONT=Consolas].[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]Range[/FONT][/COLOR][COLOR=#000000][FONT=Consolas])[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]
[/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]
    If[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] Target[/FONT][/COLOR][COLOR=#000000][FONT=Consolas].[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]Count[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]=[/FONT][/COLOR][COLOR=#800000][FONT=Consolas]1 [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]And[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] Not (Intersect(Target, Range("B3:N33")) Is Nothing) [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]Then
[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]        Target[/FONT][/COLOR][COLOR=#000000][FONT=Consolas].[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]Value[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]=[/FONT][/COLOR][COLOR=#800000][FONT=Consolas]1[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]
[/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]    End[/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]If
[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]
[/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]End [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]Sub[/FONT][/COLOR]

 

[griffym]

Hi That's great works fantastic!! and is easy for me to adjust the range when required!! many thanks. Do you know of a way of converting excel files to work as part of a website or an app on a tablet device??

 

[Joe4]

Do you know of a way of converting excel files to work as part of a website or an app on a tablet device??

I don't. Also, it is best to post entirely new questions in a new thread.

If you already have, and haven't gotten any responses, you can "bump" your post by replying to it. We just recommend that you do not "bump" more than once a day (and wait 24 hours from your initial posting before bumping).