excel - VBA on change event ruins the ListObjectTable on extending rows -
my code works listobject.table , intended allow editing prices , calculating discounts or vice versa... on entering cell editing turns formula therein value , pastes formula in column.
it works charm when user editing cells. if user tries add rows listobject.table, macro ruins table. adds couple of columns , headers replaced.
is possible make macro somehow disregard operation of adding new rows or extending range of data.table?
here macro, thank friends advice:
private olistobj listobject
private sub worksheet_beforedoubleclick(byval target range, cancel boolean) set olistobj = worksheets("quotation").listobjects("tblproforma") application.enableevents = true if not intersect(target, olistobj.listcolumns("price").databodyrange) nothing application.enableevents = false application.autocorrect.autofillformulasinlists = false target.formula = target.value application.enableevents = true end if if not intersect(target, olistobj.listcolumns("discount").databodyrange) nothing application.enableevents = false application.autocorrect.autofillformulasinlists = false target.formula = round(target.value, 5) application.enableevents = true end if end sub private sub worksheet_change(byval target range) dim pricediscountoffset integer: pricediscountoffset = activesheet.range("tblproforma[[#all],[price]:[discount]]").columns.count - 1 set olistobj = worksheets("quotation").listobjects("tblproforma") application.enableevents = true if not intersect(target, olistobj.listcolumns("price").databodyrange) nothing application.enableevents = false application.autocorrect.autofillformulasinlists = false target.offset(0, pricediscountoffset).formula = "=if([@[price]]<>"""", -([@[price]]-[@[pricelist]])/[@[price]],"""")" application.enableevents = true end if if not intersect(target, olistobj.listcolumns("discount").databodyrange) nothing application.enableevents = false application.autocorrect.autofillformulasinlists = false target.offset(0, -pricediscountoffset).formula = "=[@[pricelist]]-([@[pricelist]]*[@[discount]])" application.enableevents = true end if end sub
Comments
Post a Comment