Storing Values by clicking on them

[anubhavm10]

[TABLE="width: 500"]
<tbody>[TR]
[TD]CrankAngle[/TD]
[TD]1100 RPM[/TD]
[TD]1200 RPM[/TD]
[TD]1300 RPM[/TD]
[/TR]
[TR]
[TD]360[/TD]
[TD]0.7 Pascal[/TD]
[TD]0.5 Pa[/TD]
[TD]0.6 Pa[/TD]
[/TR]
[TR]
[TD]359[/TD]
[TD]0.6 Pa[/TD]
[TD]0.6 Pa[/TD]
[TD]0.8 Pa[/TD]
[/TR]
[TR]
[TD]358[/TD]
[TD]0.5 Pa[/TD]
[TD]0.4 Pa[/TD]
[TD]0.9 Pa[/TD]
[/TR]
[TR]
[TD]357[/TD]
[TD]0.4 Pa[/TD]
[TD]0.3 Pa[/TD]
[TD]0.3 Pa[/TD]
[/TR]
</tbody>[/TABLE]
I have a spreadsheet of pressures with rpm as columns and Crank Angles as Rows. My task is to write a macro to extract a particular set of rpm, a pressure for that rpm and corresponding crank Angle. The user should click on the required RPM cell and the RPM value gets stored . Then the user should click on the required pressure cell and the pressure value gets stored.
Then the user should click on the corresponding crank angle cell and the crank angle value gets stored in a separate variable. I have tried using the Worksheet_SelectionChange but it only works for one value ( ie the first one RPM).

Is it possible to write this code in a module , without going to worksheet code . Please help , I am new to VBA programming.

 

[MickG]

Place this code in your Worksheet module.
Select one of each value (Angle,Rev,Pressure) from data to return your selection in Msgbox.
Not sure what you want to do with them after that ???

Option Explicit
Dim Ang As Integer
Dim Rev As String
Dim Pa As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range


Set Rng = ActiveSheet.UsedRange
Set Rng1 = Rng.Offset(1).Resize(Rng.Rows.Count - 1, 1)
Set Rng2 = Rng.Offset(, 1).Resize(1, Rng.Columns.Count - 1)
Set Rng3 = Rng.Offset(1, 1).Resize(Rng.Rows.Count - 1, Rng.Columns.Count - 1)


Select Case True
    Case Not Intersect(Target, Rng1) Is Nothing: Ang = Target
    Case Not Intersect(Target, Rng2) Is Nothing: Rev = Target
    Case Not Intersect(Target, Rng3) Is Nothing: Pa = Target
End Select
If Ang <> 0 And Rev <> "" And Pa <> "" Then
    MsgBox "Angle= " & Ang & Chr(10) & "Rev = " & Rev & Chr(10) & "Pa = " & Pa
    Ang = 0: Rev = "": Pa = ""
End If
End Sub

 

[saltkev]

Hi

Same type of idea as MickG, but temp values stored on sheet and later transfered to another sheet.

Put the following code into a module, then type "Call Holding" into the sheets "Selection Change Event". Then create a command button called "CommandButton1" on sheet1. In Commandbutton1 "Click Event" type "Call Storer".

You may need to alter some of the ranges in the code to match your own, I have made comments where you might need to do this.


regards


Kev

Public Sub holding()
Application.ScreenUpdating = False
Static WS1, WS2, HoldRPM, HoldPres, HoldCrank

Set WS1 = Sheets("Active") ' Change to Reflect the names of your sheet
Set WS2 = Sheets("Storage") ' Change to Reflect the names of your sheet
WS1.Activate
HoldRPM = ""
HoldPres = ""
HoldCrank = ""

'############ Section Looks at RPM ###################
If Intersect(ActiveCell, Range("b3:d3")) Is Nothing Then 'Change to Reflect the Range you require
        'Do Nothing
    Else
        HoldRPM = ActiveCell.Value
        
        ActiveSheet.Range("f1").Value = HoldRPM  'Change to Reflect the Range you require
        
    End If

'############ Section Looks at Pressure ###################
If Intersect(ActiveCell, Range("b4:d7")) Is Nothing Then 'Change to Reflect the Range you require
        'Do Nothing
    Else
        HoldPres = ActiveCell.Value
        
        ActiveSheet.Range("g1").Value = HoldPres 'Change to Reflect the Range you require
        
    End If
    
    
'############ Section Looks at Crank Angle ###################
If Intersect(ActiveCell, Range("a4:a7")) Is Nothing Then 'Change to Reflect the Range you require
        'Do Nothing
    Else
        HoldCrank = ActiveCell.Value
        
        ActiveSheet.Range("h1").Value = HoldCrank 'Change to Reflect the Range you require
        
    End If
    
    
  If WS1.Range("f1").Value > "" And WS1.Range("g1").Value > "" And WS1.Range("H1").Value > "" Then 'Change to Reflect the Range you require
    
  WS1.CommandButton1.Visible = True
  
  Else
  
  WS1.CommandButton1.Visible = False
    
  End If
End Sub
Public Sub storer()

Application.ScreenUpdating = False
Static WS1, WS2
Set WS1 = Sheets("Active") ' Change to Reflect the names of your sheet
Set WS2 = Sheets("Storage") ' Change to Reflect the names of your sheet
WS1.Activate
WS1.Range("f1:h1").Cut 'Change to Reflect the Range you require
WS2.Activate
If ActiveSheet.Range("a3").Value = "" Then  'Change to Reflect the Range you require
        ActiveSheet.Range("a3").Select
        ActiveSheet.Paste
            ElseIf ActiveSheet.Range("a3").Value > "" And ActiveSheet.Range("a4").Value = "" Then 'Change to Reflect the Range you require
                ActiveSheet.Range("a4").Select
                ActiveSheet.Paste

                    ElseIf ActiveSheet.Range("a3").Value > "" And ActiveSheet.Range("a4").Value > "" Then 'Change to Reflect the Range you require
                            ActiveSheet.Range("a3").Select
                            Selection.End(xlDown).Select
                            ActiveCell.Offset(1, 0).Select
                            ActiveSheet.Paste

End If
WS1.Activate
WS1.CommandButton1.Visible = False
 
 
End Sub

 

[anubhavm10]

Thank you MickG. You set me on the right path . I also want to ensure a check that the crank angle and the pressure selected are from the same row. For this I put a condition but it's not working. Please advise. I also want to make sure that the selection is always in the same order that is 1. RPM 2.Pressure 3. Crank Angle. If the crank angle row is not same as pressure row , it should show error message. The row and column numbers of both the selected pressure cell and angle cell are to be passed to a module code , where everything under them will written to a text file. Can data be passed from worksheet code to module ( will i have to use public variables) .

Option Explicit
Dim Ang As Integer
Dim Rev As String
Dim Pa As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim pressure_row As Integer
Dim angle_row As Integer


Set Rng = ActiveSheet.UsedRange
Set Rng1 = Rng.Offset(1).Resize(Rng.Rows.Count - 1, 1)
Set Rng2 = Rng.Offset(, 1).Resize(1, Rng.Columns.Count - 1)
Set Rng3 = Rng.Offset(1, 1).Resize(Rng.Rows.Count - 1, Rng.Columns.Count - 1)




Select Case True
    Case Not Intersect(Target, Rng1) Is Nothing: Ang = Target
    angle_row = Target.Row
    
    Case Not Intersect(Target, Rng2) Is Nothing: Rev = Target
    
    Case Not Intersect(Target, Rng3) Is Nothing: Pa = Target
   pressure_row = Target.Row
End Select
If Ang <> 0 And Rev <> "" And Pa <> "" And pressure_row = angle_row Then
    MsgBox "Angle= " & Ang & Chr(10) & "Rev = " & Rev & Chr(10) & "Pa = " & Pa
    Ang = 0: Rev = "": Pa = ""
End If
End Sub

 

[MickG]

Try this:-
This code should limit the way you select the data.
Firsts "RPM" then "Angle". NB:- Once you select these two values the pressure is automatically selected from the cross reference.
Not sure why you ned to send data to a Module ??

Option Explicit
[COLOR=Navy]Dim[/COLOR] Ang [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Rev [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] str [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] StrA [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
Private [COLOR=Navy]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR=Navy]As[/COLOR] Range)
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Rng1 [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Rng2 [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Set[/COLOR] Rng = ActiveSheet.UsedRange
[COLOR=Navy]Set[/COLOR] Rng1 = Rng.Offset(1).Resize(Rng.Rows.Count - 1, 1)
[COLOR=Navy]Set[/COLOR] Rng2 = Rng.Offset(, 1).Resize(1, Rng.Columns.Count - 1)
[COLOR=Navy]If[/COLOR] Intersect(Target, Union(Rng1, Rng2)) [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR]
    [COLOR=Navy]If[/COLOR] Rev [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR] str = "RPM": StrA = Rng1.Address
        MsgBox "Please  select from Data:- " & str & ", from " & StrA
    [COLOR=Navy]ElseIf[/COLOR] Rev [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR]
        [COLOR=Navy]If[/COLOR] Not Intersect(Target, Rng2) [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR]
            [COLOR=Navy]Set[/COLOR] Rev = Target
            str = "Angle"
            StrA = Rng1.Address
            MsgBox Rev.Value
        [COLOR=Navy]End[/COLOR] If
    [COLOR=Navy]ElseIf[/COLOR] Not Rev [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR]
        [COLOR=Navy]If[/COLOR] Not Intersect(Target, Rng1) [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR]
            [COLOR=Navy]Set[/COLOR] Ang = Target
            MsgBox Ang.Value
        [COLOR=Navy]End[/COLOR] If
   [COLOR=Navy]End[/COLOR] If


[COLOR=Navy]If[/COLOR] Not Ang [COLOR=Navy]Is[/COLOR] Nothing And Not Rev [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR]
    MsgBox "Angle= " & Ang & Chr(10) & "Rev = " & Rev & Chr(10) & "Pa = " & Cells(Ang.Row, Rev.Column).Value
    [COLOR=Navy]Set[/COLOR] Ang = Nothing
    [COLOR=Navy]Set[/COLOR] Rev = Nothing
[COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[anubhavm10]

Thank you MickG. My boss has updated me that the format of the rows and cell will not be same always. So the code you have given me will not work as it uses fixed ranges. What he wants is 1. A message box which displays "Select RPM" 2. The user selects rpm 3. After this and only this another message box asking "Select Pressure" 4. The user selects required pressure. 5. Then another message box with "Select Angle" 6. The user selects angle and a check is performed to check whether it is corresponding angle or not . 7. If the angle is wrong , he is prompted to select Angle again. 8. If angle is correct , a new message box appears stating all the values ( rpm, pressure, angle) selected.

I had written a code but I had implemented it in a way where you select a cell and then you input in an input box the type of value. Eg. If i clicked on an rpm cell , I input that this value is of rpm type. And similarly for pressure and angle. But my boss wants it in reverse. To first tell the user to select pressure and then select. I am having trouble implementing this , as any code after the message box executes immediately, giving the user no time to select the required cell. How to rectify this.

Can this be done without using worksheet_Selection Change ?Iwould prefer the entire code is in a module , and nothing in the sheet code. As for these values , they will be used to generate a text file. All the entries below the selected pressure and crank angle are written to a text file which is used by a software. Thank, you again for helping a first time intern.

 

[MickG]

Thankyou MickG. My boss has updated me that the format of the rows and cell will notbe same always. So the code you have given me will not work as it uses fixedranges. What he wants is 1. A message box which displays "Select RPM"2. The user selects rpm 3. After this and only this another message box asking"Select Pressure" 4. The user selects required pressure. 5. Thenanother message box with "Select Angle" 6. The user selects angle anda check is performed to check whether it is corresponding angle or not . 7. Ifthe angle is wrong , he is prompted to select Angle again. 8. If angle iscorrect , a new message box appears stating all the values ( rpm, pressure,angle) selected.
" things:-

If you select a value you need to know where that value has come from, in order to know that your selection is a correct selection. If your Table Format is not constant how can you do that.
Also, If your table is basically as per your original thread, then by selecting 2 of the 3 variables , you are also (by crossreference) selecting the third, so there is only need to select 2 variables , do you agree ????
There is no great problem with making a selection in an "InputBox", but the points above need clarifying
Please confirm what your table will look like , if not as per original thread !!!
Please elaborate

 

[anubhavm10]

The main reason is that my boss doesn't want the end user to have much freedom . He should be done in three clicks . First on rpm , then on pressure , then on crank angle. Yes I agree there is only a need to select 2 variables as the third one can be selected by cross referencing. Are you sure that there is no way to implement for Eg: 1 .A message Box with "Choose Rpm" 2. The user clicks on the rpm cell and it gets stored.

As for format of the table , it can differs . Each pressure column can have it's own column of crank angles.

 

[MickG]

There is no problem with selecting a specific option (i.e "RPM) in an "input box", but you do need to know where the data is in order for the code to know if the selection is correct.
Please supply an example of the data, that is set out so that ranges relating to individual Variable can be identified.

 

[anubhavm10]

There are broadly two types of data formats.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Speed[/TD]
[TD]1000[/TD]
[TD]1100[/TD]
[TD]1200[/TD]
[TD]1300[/TD]
[TD]1400[/TD]
[TD]1500[/TD]
[TD]1600[/TD]
[TD]1700[/TD]
[TD]1800[/TD]
[/TR]
[TR]
[TD]Random Data[/TD]
[TD]Random Data[/TD]
[TD]Random Data[/TD]
[TD]Random Data[/TD]
[TD]Random Data[/TD]
[TD]Random Data[/TD]
[TD]Random Data[/TD]
[TD]Random Data[/TD]
[TD]Random Data[/TD]
[TD]Random Data[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Speed[/TD]
[TD]1000[/TD]
[TD]1100[/TD]
[TD]1200[/TD]
[TD]1300[/TD]
[TD]1400[/TD]
[TD]1500[/TD]
[TD]1600[/TD]
[TD]1700[/TD]
[TD]1800[/TD]
[/TR]
[TR]
[TD]Crank Angle[/TD]
[TD] P1[/TD]
[TD]P2[/TD]
[TD]P3[/TD]
[TD]P4[/TD]
[TD]P5[/TD]
[TD]P6[/TD]
[TD]P7[/TD]
[TD]P8[/TD]
[TD]P9[/TD]
[/TR]
[TR]
[TD]360[/TD]
[TD]231 Pa[/TD]
[TD]456 Pa[/TD]
[TD]234 Pa[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]359[/TD]
[TD]12323[/TD]
[TD]5345[/TD]
[TD]45345[/TD]
[TD]567[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]358[/TD]
[TD]5765[/TD]
[TD]47656[/TD]
[TD]789[/TD]
[TD]890[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]357[/TD]
[TD]6534[/TD]
[TD]234324[/TD]
[TD]465[/TD]
[TD]546[/TD]
[TD]456[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]356[/TD]
[TD]1232[/TD]
[TD]324[/TD]
[TD]7987[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Assume that the rest of the empty cells under the pressure columns are also filled with pressure values.

Type 2:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Speed[/TD]
[TD] 10[/TD]
[TD]00[/TD]
[TD]11[/TD]
[TD]00[/TD]
[TD]12[/TD]
[TD]00[/TD]
[TD]13[/TD]
[TD]00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CA[/TD]
[TD]P1[/TD]
[TD]CA[/TD]
[TD]P2[/TD]
[TD]CA[/TD]
[TD]P3[/TD]
[TD]CA[/TD]
[TD]P4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]360[/TD]
[TD]123 Pa[/TD]
[TD]360[/TD]
[TD]456[/TD]
[TD]360[/TD]
[TD]234[/TD]
[TD]360[/TD]
[TD]657[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]359[/TD]
[TD]2342 Pa[/TD]
[TD]359[/TD]
[TD]567[/TD]
[TD]359[/TD]
[TD]345[/TD]
[TD]359[/TD]
[TD]567[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]358[/TD]
[TD]465 Pa[/TD]
[TD]358[/TD]
[TD]678[/TD]
[TD]358[/TD]
[TD]345[/TD]
[TD]358[/TD]
[TD]589[/TD]
[/TR]
</tbody>[/TABLE]
Assume that the cell of the RPMs are merged ( B1 and C1 are merged , that is they are one cell and so on). I could not merge cells here so I resorted to this. CA is crank angle.

My boss wants the same code regardless of the table format. I need to know how to solve this simple problem ( if it is possible or not).

1. Message Box says "Select RPM". User clicks ok.
2. User selects the rpm. The value gets stored.
3. Next a message box says "Select Pressure". User clicks ok.
4.User selects the pressure. The value gets stored.
5. Same steps for storing crank angle. And checking if corresponding crank angle is selected.
6. If not then user is prompted to select angle again

Is this possible or not. My boss does not want the freedom for the user to click where ever he wants and then to enter which type of data it is. Sorry If I sound flustered. I want a code to be independent of range types. It should work regardless of whatever the format of the table is.