excel - VBA code for automating a sheet -

1) have created form in vba , have given connection first sheet , when click on button form getting pop enter data, want is, want assign button in first sheet , when click on button form should appear , when insert data form, should appear in second sheet.

2) inserting data sheet 4 rows , after completion of if want modify particular column or particular row of data how can that, need suggest me on , request send me code how modify.

3) , request send me code clear sheet if want enter new data.

i glad if helps me on these 3 points.

private sub cmdadd_click() dim integer  'position cursor in correct cell b9. range("b9:p40").select = 1 'set first id  'validate first 4 controls have been entered... if me.txtfname.text = empty 'firstname     msgbox "please enter firstname.", vbexclamation     me.txtfname.setfocus 'position cursor try again     exit sub 'terminate here - why continue? end if  if me.txtsname.text = empty 'surname     msgbox "please enter surname.", vbexclamation     me.txtsname.setfocus 'position cursor try again     exit sub 'terminate here - why continue? end if  if me.txtfuname.text = empty 'fullname     msgbox "please enter fullname.", vbexclamation     me.txtfuname.setfocus 'position cursor try again     exit sub 'terminate here - why continue? end if  if me.txtdname.text = empty 'designation     msgbox "please enter designation.", vbexclamation     me.txtdname.setfocus 'position cursor try again     exit sub 'terminate here - why continue? end if  'if above false (ok) carry on. 'check see next available blank row start @ cell b9... until activecell.value = empty     activecell.offset(1, 0).select 'move down 1 row     = + 1 'keep count of id later use loop  'populate new data values 'data' worksheet. activecell.value = 'next id number activecell.offset(0, 1).value = me.txtfname.text 'set col b activecell.offset(0, 2).value = me.txtsname.text 'set col c activecell.offset(0, 3).value = me.txtfuname.text 'set col d activecell.offset(0, 4).value = me.txtdname.text 'set col e  'clear down values ready next record entry... me.txtfname.text = empty me.txtsname.text = empty me.txtfuname.text = empty me.txtdname.text = empty  me.txtfname.setfocus 'positions cursor next record entry  end sub  private sub cmdclose_click()     'close form (itself)     unload me end sub 

a) place button in sheet one.

if using form control place code in module , right click on button , click on assign macro , link below code

sub launch()     userform1.show end sub 

if using activex control, double click in design mode , use code

private sub commandbutton1_click()     userform1.show end sub 

regarding showing data in sheet2, in "ok" button of form, link 2nd sheet. example.

private sub commandbutton1_click()     dim ws worksheet      set ws = thisworkbook.sheets("sheet2")      ws                    '~~> assuming want write 1st 4 rows mentioned         .range("a1").value = textbox1.text         .range("a2").value = textbox2.text         .range("a3").value = textbox3.text         .range("a4").value = textbox4.text     end end sub 

---

b) this, recommend taking user's input , based on show relevant form.

if user selects first option show data entry userform else show edit userform data entry userform have userform_initialize() pull data sheet depending on whether user wants edit row or column. how choose take input leave you. example

private sub userform_initialize()     dim ws worksheet      set ws = thisworkbook.sheets("sheet2")      ws         textbox1.text = .range("a1").value         textbox2.text = .range("a2").value         textbox3.text = .range("a3").value         textbox4.text = .range("a4").value     end end sub 

---

c) clear sheet can use code.

private sub commandbutton2_click()     thisworkbook.sheets("sheet2").cells.clearcontents end sub