re: edit command button

[jeana]

hihi~~

newbie here, i been looking over the place for an excel code and i decided to use this function http://www.excelforum.com/showthread...edit+data+form


the add, delete, cancel and save seems to work fine but i had huge problems with the edit

i amend it so that it can work together with my codes and adjust it to fit 31 textboxes. the problem is that the EDIT went slightly bonkers after a few tries, it paste a new record instead of overriding it.. i am slightly desperate to replace the edit button with a new macro but i only have superfical vb skills

can anyone help me??

[Carim]

Hi Jeana,

Application.Onkey "{F2}", "your new macro"

HTH
Carim

[Tom Ogilvy]

If you go back to the original code, it uses one procedure for writing the
data. That procedure check the Myprocessing variable to see where to place
the data. In the original edit routine, this was set with this code

If myProcessing = "" Then
myProcessing = "Edit"
End If

If you aren't setting it in your code, then you need to.

--
Regards,
Tom Ogilvy




"jeana" wrote:

>
> hihi~~
>
> newbie here, i been looking over the place for an excel code and i
> decided to use this function
> http://www.excelforum.com/showthread...edit+data+form
>
>
> the add, delete, cancel and save seems to work fine but i had huge
> problems with the edit
>
> i amend it so that it can work together with my codes and adjust it to
> fit 31 textboxes. the problem is that the EDIT went slightly bonkers
> after a few tries, it paste a new record instead of overriding it.. i
> am slightly desperate to replace the edit button with a new macro but i
> only have superfical vb skills
>
> can anyone help me??
>
>
> --
> jeana
> ------------------------------------------------------------------------
> jeana's Profile: http://www.excelforum.com/member.php...o&userid=32673
> View this thread: http://www.excelforum.com/showthread...hreadid=524802
>
>

[jeana]

er~

what does Application.Onkey "{F2}", "your new macro" do?

actually i did have the code for the my processing and i check it already but it still paste in a new row instead of an original. This is my entire code, i subsitute listbox with combobox. my new row start at a7 so i change the code to start at a7. I alternate color each sub to make it easier to read


________________________________________________________________


Option Explicit
Dim myInputRange As Range
Dim myProcessing As String
Dim blkProc As Boolean

Private Sub CmdNew_Click()
ComboBox1.ListIndex = -1
ComboBox2.Enabled = True

Dim iCtr As Long
For iCtr = 1 To 31
Me.Controls("textbox" & iCtr).Value = ""
Next iCtr

myProcessing = "New"

Call cmdEdit_Click

End Sub

Private Sub CmdCancel_Click()
Call sortsortnames1
Call sortsortic12

If Me.CmdCancel.Caption = "Cancel" Then
ComboBox2.Enabled = False
ComboBox3.Visible = False
ComboBox4.Visible = False
CommandButton5.Visible = False
CommandButton6.Visible = False
Unload Me
Else
ComboBox2.Enabled = False
ComboBox3.Visible = False
ComboBox4.Visible = False
CommandButton5.Visible = False
CommandButton6.Visible = False 'cancel edit
Call UserForm_Initialize
End If

End SubSub

sortsortnames1()
'
' sortsortnames1 Macro
' Macro recorded 19/03/2006 by SMU
'

'
Range("A8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("AL8").Select
ActiveSheet.Paste
Range("AL8").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("AL8"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Sub sortsortic12()
'
' sortsortic12 Macro
' Macro recorded 19/03/2006 by SMU
'

'
Range("F8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("AO8").Select
ActiveSheet.Paste
Range("AP6").Copy
Range("AO8", Selection.End(xlDown)).Select
Selection.pastespecial Paste:=xlPasteValues, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False

Range("AO8").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("AO8"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub


Private Sub CmdDelete_Click()
Dim lastrow As Integer

Dim msb As Integer
msb = MsgBox("Do you want to delete entire Record?", vbOKCancel)
If msb = vbOK Then
If Me.ComboBox1.ListIndex > -1 Then
myInputRange(1).Offset(Me.ComboBox1.ListIndex).EntireRow.Delete
lastrow = CInt(Mid(Sheet1.ComboBox2.ListFillRange, InStr(1, Sheet1.ComboBox2.ListFillRange, ":") + 3, Len(Sheet1.ComboBox2.ListFillRange)))
Sheet1.ComboBox2.ListFillRange = "'Cus'!AO7:AO" & lastrow
Sheet1.CboName.ListFillRange = "'Cus'!AL7:AL" & lastrow

Call UserForm_Initialize
If Application.CountA(myInputRange) = 0 Then
Me.CmdSave.Enabled = False
Me.CmdCancel.Enabled = True
Me.CmdNew.Enabled = True
Me.CmdEdit.Enabled = False
Me.CmdDelete.Enabled = False
End If
End If
End If
End Sub

Private Sub cmdEdit_Click()

TextBox10.Enabled = True
TextBox11.Enabled = True
TextBox12.Enabled = True
TextBox13.Enabled = True
Dim iCtr As Long
ComboBox2.Enabled = True
ComboBox3.Visible = True
ComboBox4.Visible = True
CommandButton5.Visible = True
CommandButton6.Visible = True

For iCtr = 1 To 31
Me.Controls("textbox" & iCtr).Enabled = True
Next iCtr

Me.CmdCancel.Caption = "Cancel Change"

Me.ComboBox1.Enabled = False
Me.CmdSave.Enabled = True
Me.CmdCancel.Enabled = True
Me.CmdNew.Enabled = False
Me.CmdEdit.Enabled = False
Me.CmdDelete.Enabled = False

If myProcessing = "" Then
myProcessing = "Edit"
End If

End Sub

Private Sub CmdSave_Click()
Dim lastrow As Integer
Dim iCtr As Long
Dim DestCell As Range
With myInputRange
If myProcessing = "New" Then
Set DestCell = .Cells(1).Offset(.Rows.Count)
lastrow = CInt(Mid(Sheet1.ComboBox2.ListFillRange, InStr(1, Sheet1.ComboBox2.ListFillRange, ":") + 3, Len(Sheet1.ComboBox2.ListFillRange)) + 1)
Sheet1.ComboBox2.ListFillRange = "'Cus'!AO7:AO" & lastrow
Sheet1.CboName.ListFillRange = "'Cus'!AL7:AL" & lastrow


Else
Set DestCell = .Cells(1).Offset(Me.ComboBox1.ListIndex)
End If
End With

blkProc = True
For iCtr = 1 To Me.ComboBox1.ColumnCount
DestCell.Offset(0, iCtr - 1).Value = Me.Controls("textbox" & iCtr)
Next iCtr
blkProc = False
myProcessing = ""
ComboBox2.Enabled = False
ComboBox3.Visible = False
ComboBox4.Visible = False
CommandButton5.Visible = False
CommandButton6.Visible = False
Call UserForm_Initialize
End SubPrivate Sub Combobox1_Click()


Private Sub Combobox1_Click()
Dim iCtr As Long
If ComboBox1.Text = "" Then
Exit Sub
End If

If blkProc Then Exit Sub
With Me.ComboBox1
If .ListIndex > -1 Then
For iCtr = 1 To 31
Me.Controls("textbox" & iCtr).Value = .List(.ListIndex, iCtr - 1)
Next iCtr
End If
End With
End Sub

Private Sub UserForm_Activate()
ComboBox2.Clear

ComboBox2.AddItem "Prulink Protected Global Titans Fund"
ComboBox2.AddItem "Prulink Adapt 2015 Fund"
ComboBox2.AddItem "Prulink America Fund"
ComboBox2.AddItem "Prulink International Bond Fund"
ComboBox2.AddItem "Prulink Asian Reach Managed Fund"
ComboBox2.AddItem "Prulink Pan European Fund"
ComboBox2.AddItem "Prulink Singapore Managed Fund"
ComboBox2.AddItem "Prulink Asian Equity Fund"
ComboBox2.AddItem "Prulink Adapt 2035 Fund"
ComboBox2.AddItem "Prulink Adapt 2025 Fund"
ComboBox2.AddItem "Prulink Global Equity Fund"
ComboBox2.AddItem "Prulink China-India Fund"
ComboBox2.AddItem "Prulink Emerging Markets Fund"
ComboBox2.AddItem "Prulink Global Technology Fund"
ComboBox2.AddItem "Prulink Singapore Cash Fund"
ComboBox2.AddItem "Prulink Global Managed Fund"
End Sub


Private Sub UserForm_Initialize()

Dim iCtr As Long

Me.ComboBox1.ColumnCount = 31
Me.ComboBox1.RowSource = ""
With Worksheets("Cus")
If .Cells(2).Value = "No Entries" Then
.Rows(1).Delete
End If
Set myInputRange = .Range("a7:AZ" _
& .Cells(.Rows.Count, "A").End(xlUp).Row)
If Application.CountA(myInputRange) = 0 Then
myInputRange(1).Value = "No Entries"
End If
Me.ComboBox1.RowSource = myInputRange.Address(external:=True)
End With

For iCtr = 1 To 31
Me.Controls("textbox" & iCtr).Enabled = False
Next iCtr

Me.CmdCancel.Caption = "Cancel"
Me.ComboBox1.Enabled = True
Me.ComboBox1.ListIndex = 0 'prime the pump
Me.CmdSave.Enabled = False
Me.CmdCancel.Enabled = True
Me.CmdNew.Enabled = True
Me.CmdEdit.Enabled = True
Me.CmdDelete.Enabled = True
End Sub