Solved: Probably a quick solution to this! - finding rows

[nickirvine]

Hi all,

I'm sure there is a quick and easy to solution to this but your help would be very appreciated.

I have created a code then runs off a button in excel. Basically what it does is you click the button it opens a form which pulls in the data from the cell above where the button is, allows you to edit the cell above, removes the little square character then pops it back into the cell above. All works fine.

I want to use this code in numerous places so is there is way of getting the VB to work out what the cell above is.

In this example, the button is in B13, and the cell I want to edit is B12.

Rather than writing the code out loads of times, is there a quick way, of just doing one code which works it out?

My code is here:

[VBA]
Private Sub cmdAdd_Click()
Dim sData As String
Dim lRowNum As Long

'pulls in data currently in cell to the text box on the form
sData = txtData.Text
Sheets("sheet1").Range("B12").Value = sData

' removes square character
Dim MyChar
MyChar = Chr(13)
Sheets("sheet1").Columns("B:C").Replace _
What:=MyChar, Replacement:=" ", _
SearchOrder:=xlByColumns, MatchCase:=True

End Sub

Private Sub UserForm_Initialize()
UserForm1.txtData.Text = Sheets("sheet1").Range("B12").Value
End Sub

[/VBA]

Thanks for your time.

[p45cal]

If it's a button from the Controls Toolbox toolbar (activex):[vba]Private Sub CommandButton1_Click()
With CommandButton1.TopLeftCell
MsgBox .Address
.Offset(-1).Value = "I'm above the button"
End With
End Sub
[/vba]It's very similar if it's a button from the forms toolbar.

[nickirvine]

Hi, Thanks for your help.

I have put a command button from the control toolbar into the cell below where I want to edit (B13). However when I press it, it returns B13. I would like it to return B12. It doesn't seem to be taking the 1 away.

Also could you help me on how I would put this into the code:

[VBA]Sheets("sheet1").Range("B12").Value = sData [/VBA]

Thanks,

Nick

[p45cal]

The topleftcell property returns the cell that the topleft corner of the button is on. The cell above that cell can be returned by offsetting it 1 row up, .offset(-1).

[vba]commandbutton1.topleftcell.offset(-1).value =sdata
[/vba]

Hi, Thanks for your help.

I have put a command button from the control toolbar into the cell below where I want to edit (B13). However when I press it, it returns B13. I would like it to return B12. It doesn't seem to be taking the 1 away.

Also could you help me on how I would put this into the code:

[vba]Sheets("sheet1").Range("B12").Value = sData [/vba]
Thanks,

Nick

[nickirvine]

Thank you for your help. That works perfectly.

The only problem I have is that as I want to use several of these command buttons, they will be called commandbutton1, commandbutton2 etc I can only use the same code once, as it will always run off commandbutton1.

I have put the code that you helped me with into the form which I have created. Ideally I just want one form - i dont want to create a form for every button.

I guess what I need is a bit of code which works out the value based on what was clicked..ie a bit of IF code, so if commandbutton1 clicked in cell B13 then take value as B12, if commandbutton2 in cell C13 clicked then take value as C12 etc.

The code I have in the form is

[vba]Private Sub cmdAdd_Click()
Dim sData As String
Dim lRowNum As Long
sData = txtData.Text

' copies what is in the text box into the field above
With Sheet2.CommandButton1.TopLeftCell
Sheet2.CommandButton1.TopLeftCell.Offset(-1).Value = sData
End With

' removes square new line character, but keeps new line
Dim MyChar
MyChar = Chr(13)
Sheets("sheet2").Columns("B:C").Replace _
What:=MyChar, Replacement:=" ", _
SearchOrder:=xlByColumns, MatchCase:=True
End Sub

Private Sub UserForm_Initialize()
' gets what is in the cell above and puts into the form to be edited
UserForm1.txtData.Text = Sheet2.CommandButton1.TopLeftCell.Offset(-1).Value
End Sub[/vba]

On the sheet I tried, unsuccesfully!, to work out a code that would record the cell (ie record B12), so then on the form it could copy whats typed into the text box into that cell:

[vba]Private Sub CommandButton1_Click()
Dim cell As String
With Sheet2.CommandButton1.TopLeftCell
Sheet2.CommandButton1.TopLeftCell.Offset(-1).Value = cell
UserForm1.Show
End With
End Sub [/vba]

I was thinking of on the form using something like:

[VBA]sdata=cell[/VBA]

But that doesnt work.

Thanks for all your help on this it is really appreciated.

Hope I havent lost you there. My experience is in PHP coding and not excel so I'm very new to this.

Nick

[p45cal]

What, and where, is/are the code(s) which bring(s) up the userform?

We'll have to do something there.

[nickirvine]

The userform code is on the sheet and is simply:

[vba]

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

[/vba]

I would repeat this code for every command button, for commandbutton2, commandbutton 3 etc

Thanks.

[p45cal]

Yes, you'll have to repeat it for every button (how many are there? If there are an awful lot, then we could move to another, more complicatesd and advanced solution.), but to keep it short we'll only try and have one line which calls another sub.

So this is the sort of theing you have in the sheet's code module behind the sheet buttons:[vba]Private Sub CommandButton1_Click()
blah CommandButton1
End Sub

Private Sub CommandButton2_Click()
blah CommandButton2
End Sub

Private Sub CommandButton3_Click()
blah CommandButton3
End Sub[/vba]
Lower down in the same module could be:[vba]Sub blah(button)
Set cll = button.TopLeftCell.Offset(-1)
UserForm1.TextBox1.Text = cll.Value
UserForm1.Show
cll.Value = Replace(UserForm1.TextBox1.Text, Chr(13), " ")
Unload UserForm1
End Sub
[/vba]In the userform's code module, remove the initialise event code, and for the Add button's click_event (I think that's the button) have:[vba]Private Sub cmdAdd_Click()
Me.Hide
End Sub
[/vba]and remove/comment out anything else you have in that click_event's code. Now you only need to tweak blah for changes there to affect as many of the buttons on the sheet as you want.

[nickirvine]

Thanks loads for your help with this it works a treat and your time is really appreciciated.

Thanks again