Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
edit command button
hihi~~ newbie here, i been looking over the place for an excel code and decided to use this functio http://www.excelforum.com/showthread...edit+data+form the add, delete, cancel and save seems to work fine but i had hug problems with the edit i amend it so that it can work together with my codes and adjust it t fit 31 textboxes. the problem is that the EDIT went slightly bonker after a few tries, it paste a new record instead of overriding it.. am slightly desperate to replace the edit button with a new macro but only have superfical vb skills can anyone help me? -- jean ----------------------------------------------------------------------- jeana's Profile: http://www.excelforum.com/member.php...fo&userid=3267 View this thread: http://www.excelforum.com/showthread.php?threadid=52480 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
edit command button
Hi Jeana,
Application.Onkey "{F2}", "your new macro" HTH Carim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
edit command button
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
edit command button
er~ what does -Application.Onkey "{F2}", "your new macro"- do? actually i did have the code for the my processing and i check i already but it still paste in a new row instead of an original. Thi is my entire code, i subsitute listbox with combobox. my new row star at a7 so i change the code to start at a7. I alternate color each su 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).Ent ireRow.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 -- jeana ------------------------------------------------------------------------ jeana's Profile: http://www.excelforum.com/member.php...o&userid=32673 View this thread: http://www.excelforum.com/showthread...hreadid=524802 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I edit a command icon? | Excel Discussion (Misc queries) | |||
where to reset microsoft query allow edit command DDE | Setting up and Configuration of Excel | |||
The edit - find command | Excel Worksheet Functions | |||
using command button instead of edit function | Excel Programming | |||
VBA command for edit cell mode | Excel Programming |