Excel VBA: Dynamically update a Charts X-Axis Min, Max, & Unit Values

%3CLINGO-SUB%20id%3D%22lingo-sub-194227%22%20slang%3D%22en-US%22%3EExcel%20VBA%3A%20Dynamically%20update%20a%20Charts%20X-Axis%20Min%2C%20Max%2C%20%26amp%3B%20Unit%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-194227%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20%3CSTRONG%3E**profoundly%20appreciate**%3C%2FSTRONG%3E%20assistance%20from%20anyone%20regarding%20dynamically%20updating%20the%20X-Axis%20value%20of%20an%20Excel%20Bar-Chart%20via%20EITHER%20in-sheet%20formulae%20OR%20via%20VBA-code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E**I've%20unsuccessfully%20tried%20the%20following**%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E---Created%20a%20named-range%20on%20the%203%20in-sheet%20cells%20(Q2%2C%20R2%2C%20%26amp%3B%20S2)%20which%20will%20always%20contain%20the%20occassionally%20updated%20values%20for%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EX-Axis-Minimum%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EX-Axis-Maximum%2C%20and%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EX-Axis-Major-Units.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20typed%20the%20following%20formula%20into%20each%20of%20the%20respective%20%2FFormat.Axis%2FAxis.Options%20dialog-interface%20data-boxes%20for%20all%20of%20those%203%20variables...%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DMAIN!XMIN%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DMAIN!XMAX%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DMAIN!XUNITS%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E...respectively%2C%20where%20%22MAIN%22%20is%20the%20name%20I've%20assigned%20to%20Sheet1.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20the%20dialog-interface%20data-boxes%20do%20not%20retain%20the%20formulas%2C%20but%20simply%20revert%20back%20to%20whatever%20data%20was%20previously%20in%20there.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E**Alternatively%2C%20I've%20tried%20to%20solve%20this%20via%20VBA%20using%20the%20following%20algorithm%20variations%20tied%20to%20an%20ActiveX-Control%20button%20named%20%22ReCalibrateButton%22.**%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%20me%20determine%20which%20of%20the%20following%20algorithm%20iterations%20is%20most%20efficient%20and%20closest%20to%20accurate%3B%20as%20well%20as%20what's%20missing%20or%20wrong%26nbsp%3B%20and%20preventing%20it%20from%20working%20successfully%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E**(Algorithm%20%231)**%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3EOption%20Explicit%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20ReCalibrateButton_Click()%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20wsChart%20As%20Chart%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20wsInput%20As%20Worksheet%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Set%20wsChart%20%3D%20EAMPVPMSChart%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Set%20wsInput%20%3D%20ThisWorkbook.Sheets(%22MAIN%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20With%20wsChart%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20With%20.Axes(xlCategory)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.MinimumScale%20%3D%20wsInput.Range(%22Q2%22).Value%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.MaximumScale%20%3D%20wsInput.Range(%22R2%22).Value%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.MajorUnit%20%3D%20wsInput.Range(%22S2%22).Value%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20With%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20With%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3EWhen%20run%2C%20this%20algorithm%20unfortunately%20yields%20the%20following%20error%3D%20%22%3CSTRONG%3ECompile%20Error%3A%20Variable%20not%20defined%3C%2FSTRONG%3E%22%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3EWhat%20have%20i%20missed%20or%20done%20wrong%20within%20this%20algorithm%3F%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E**(Algorithm%20%232)**%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3EOption%20Explicit%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20ReCalibrateButton_Click()%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20Dim%20objCht%20As%20ChartObject%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20For%20Each%20objCht%20In%20ActiveSheet.ChartObjects%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20With%20objCht.Chart%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20'%20Value%20(X)%20Axis%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20With%20.Axes(xlCategory)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.MinimumScale%20%3D%20ActiveSheet.Range(%22Q2%22).Value%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.MaximumScale%20%3D%20ActiveSheet.Range(%22R2%22).Value%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.MajorUnit%20%3D%20ActiveSheet.Range(%22S2%22).Value%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BEnd%20With%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20With%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20Next%20objCht%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3EWhen%20run%2C%20this%20algorithm%20unfortunately%20yields%20the%20following%20error%3D%20%22%3CSTRONG%3ERun-time%20error%20'-2147467259%20(80004005)'%20Method%20'MinimumScale'%20of%20object%20'Axis'%20failed%3C%2FSTRONG%3E%22%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3EWhat%20have%20i%20missed%20or%20done%20wrong%20within%20this%20algorithm%3F%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E**(Algorithm%20%233)**%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3EOption%20Explicit%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20ReCalibrateButton_Click()%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20wsChart%20As%20Chart%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20wsInput%20As%20Worksheet%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Set%20wsChart%20%3D%20ThisWorksheet.Charts(%22EAMPVPMSChart%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Set%20wsInput%20%3D%20ThisWorkbook.Sheets(%22MAIN%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20With%20wsChart%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20With%20.Axes(xlCategory)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.MinimumScale%20%3D%20wsInput.Range(%22Q2%22).Value%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.MaximumScale%20%3D%20wsInput.Range(%22R2%22).Value%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.MajorUnit%20%3D%20wsInput.Range(%22S2%22).Value%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20With%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20With%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3EWhen%20run%2C%20this%20algorithm%20unfortunately%20yields%20the%20following%3C%2FP%3E%3CP%3Eerror%3D%20%22%3CSTRONG%3ECompile%20Error%3A%20Variable%20not%20defined%3C%2FSTRONG%3E%22%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3EWhat%20have%20i%20missed%20or%20done%20wrong%20within%20this%20algorithm%3F%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E**(Algorithm%20%234)**%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3EOption%20Explicit%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20ReCalibrateButton_Click()%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20wksCharts%20As%20Worksheet%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20oChrtObj%20As%20ChartObject%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Set%20wksCharts%20%3D%20Worksheets(%22MAIN%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20With%20wksCharts.ChartObjects(%22EAMPVPMSChart%22).Chart%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20oChrtObj.Chart.Axes(xlCategory).MinimumScale%20%3D%20ActiveSheet.Range(%22Q2%22).Value%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20oChrtObj.Chart.Axes(xlCategory).MaximumScale%20%3D%20ActiveSheet.Range(%22R2%22).Value%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20oChrtObj.Chart.Axes(xlCategory).MaximumScale%20%3D%20ActiveSheet.Range(%22S2%22).Value%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20With%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3EWhen%20run%2C%20this%20algorithm%20unfortunately%20yields%20the%20following%3C%2FP%3E%3CP%3Eerror%3D%20%22%3CSTRONG%3ERun-time%20error%20'91'%3A%20Object%20variable%20or%20With%20block%20variable%20not%20set%3C%2FSTRONG%3E%22%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3EWhat%20have%20i%20missed%20or%20done%20wrong%20within%20this%20algorithm%3F%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E**(Algorithm%20%235)**%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3EOption%20Explicit%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20ReCalibrateButton_Click()%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActiveSheet.ChartObjects(%22EAMPVPMSChart%22).Activate%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20With%20Application.ActiveChart.Axes(xlCategory%2C%20xlPrimary)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.MinimumScale%20%3D%20wsInput.Range(%22Q2%22).Value%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.MaximumScale%20%3D%20wsInput.Range(%22R2%22).Value%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.MajorUnit%20%3D%20wsInput.Range(%22S2%22).Value%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20With%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3EWhen%20run%2C%20this%20algorithm%20unfortunately%20yields%20the%20following%3C%2FP%3E%3CP%3Eerror%3D%20%22%3CSTRONG%3ECompile%20Error%3A%20Variable%20not%20defined%3C%2FSTRONG%3E%22%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3EWhat%20have%20i%20missed%20or%20done%20wrong%20within%20this%20algorithm%3F%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-194227%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Desktop%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20%26amp%3B%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-290900%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VBA%3A%20Dynamically%20update%20a%20Charts%20X-Axis%20Min%2C%20Max%2C%20%26amp%3B%20Unit%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-290900%22%20slang%3D%22en-US%22%3E%3CP%3EAlg%202%3A%20Works%20for%20me%2C%20make%20sure%20you%20set%20the%20xaxis%20to%20accept%20dates%20(numbers).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-194323%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VBA%3A%20Dynamically%20update%20a%20Charts%20X-Axis%20Min%2C%20Max%2C%20%26amp%3B%20Unit%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-194323%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jack%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlg.%201%3A%20I%20expect%20there%20is%20no%20sheet%20having%20a%20codename%20%22EAMPVPMSChart%22.%20A%20worksheet%20can%20have%20two%20names%2C%20the%20one%20you%20see%20on%20the%20tab%20(shown%20as%20%22Name%22%20property)%20and%20the%20one%20you%20can%20use%20in%20code%20directly%20(shown%20as%20%22(Name)%22%20property).%3C%2FP%3E%0A%3CP%3EAlg.%202%3A%20Not%20sure%2C%20it%20appears%20to%20work%20for%20me%3C%2FP%3E%0A%3CP%3EAlg.%203%3A%20ThisWorksheet%20is%20not%20an%20existing%20object%2C%20you're%20looking%20for%20ActiveSheet%3C%2FP%3E%0A%3CP%3EAlg.4%3A%20Not%20sure%3C%2FP%3E%0A%3CP%3EAlg.%205%3A%20I%20suspect%20wsInput%20is%20the%20problem%2C%20it%20is%20treated%20as%20a%20variable%20and%20was%20not%20declared.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Regular Visitor

I would **profoundly appreciate** assistance from anyone regarding dynamically updating the X-Axis value of an Excel Bar-Chart via EITHER in-sheet formulae OR via VBA-code.

 

**I've unsuccessfully tried the following**:

 

---Created a named-range on the 3 in-sheet cells (Q2, R2, & S2) which will always contain the occassionally updated values for:

 

X-Axis-Minimum,

 

X-Axis-Maximum, and

 

X-Axis-Major-Units.

 

Then typed the following formula into each of the respective /Format.Axis/Axis.Options dialog-interface data-boxes for all of those 3 variables...:

 

 

=MAIN!XMIN

 

=MAIN!XMAX

 

=MAIN!XUNITS

 

 

...respectively, where "MAIN" is the name I've assigned to Sheet1.

 

However, the dialog-interface data-boxes do not retain the formulas, but simply revert back to whatever data was previously in there.

 

**Alternatively, I've tried to solve this via VBA using the following algorithm variations tied to an ActiveX-Control button named "ReCalibrateButton".**

 

Please help me determine which of the following algorithm iterations is most efficient and closest to accurate; as well as what's missing or wrong  and preventing it from working successfully:

 

**(Algorithm #1)**

==============================================================

Option Explicit

 

Private Sub ReCalibrateButton_Click()

    Dim wsChart As Chart

    Dim wsInput As Worksheet

 

    Set wsChart = EAMPVPMSChart

    Set wsInput = ThisWorkbook.Sheets("MAIN")

 

    With wsChart

        With .Axes(xlCategory)

            .MinimumScale = wsInput.Range("Q2").Value

            .MaximumScale = wsInput.Range("R2").Value                                      

            .MajorUnit = wsInput.Range("S2").Value

        End With

    End With

End Sub

==============================================================

When run, this algorithm unfortunately yields the following error= "Compile Error: Variable not defined"

==============================================================

What have i missed or done wrong within this algorithm?

==============================================================

 

**(Algorithm #2)**

==============================================================

Option Explicit

 

Private Sub ReCalibrateButton_Click()

 

   Dim objCht As ChartObject

 

   For Each objCht In ActiveSheet.ChartObjects

 

      With objCht.Chart

 

         ' Value (X) Axis

 

         With .Axes(xlCategory)

 

            .MinimumScale = ActiveSheet.Range("Q2").Value

 

            .MaximumScale = ActiveSheet.Range("R2").Value

 

            .MajorUnit = ActiveSheet.Range("S2").Value

 

         End With

 

      End With

 

   Next objCht

 

End Sub

==============================================================

When run, this algorithm unfortunately yields the following error= "Run-time error '-2147467259 (80004005)' Method 'MinimumScale' of object 'Axis' failed"

==============================================================

What have i missed or done wrong within this algorithm?

==============================================================

 

**(Algorithm #3)**

==============================================================

Option Explicit

 

Private Sub ReCalibrateButton_Click()

    Dim wsChart As Chart

    Dim wsInput As Worksheet

 

    Set wsChart = ThisWorksheet.Charts("EAMPVPMSChart")

    Set wsInput = ThisWorkbook.Sheets("MAIN")

 

    With wsChart

        With .Axes(xlCategory)

            .MinimumScale = wsInput.Range("Q2").Value

            .MaximumScale = wsInput.Range("R2").Value                                      

            .MajorUnit = wsInput.Range("S2").Value

        End With

    End With

End Sub

==============================================================

When run, this algorithm unfortunately yields the following

error= "Compile Error: Variable not defined"

==============================================================

What have i missed or done wrong within this algorithm?

==============================================================

 

**(Algorithm #4)**

==============================================================

Option Explicit

 

Private Sub ReCalibrateButton_Click()

 

    Dim wksCharts As Worksheet

    Dim oChrtObj As ChartObject

   

    Set wksCharts = Worksheets("MAIN")

   

    With wksCharts.ChartObjects("EAMPVPMSChart").Chart

                oChrtObj.Chart.Axes(xlCategory).MinimumScale = ActiveSheet.Range("Q2").Value

                oChrtObj.Chart.Axes(xlCategory).MaximumScale = ActiveSheet.Range("R2").Value

                oChrtObj.Chart.Axes(xlCategory).MaximumScale = ActiveSheet.Range("S2").Value

    End With

End Sub

==============================================================

When run, this algorithm unfortunately yields the following

error= "Run-time error '91': Object variable or With block variable not set"

==============================================================

What have i missed or done wrong within this algorithm?

==============================================================

 

**(Algorithm #5)**

==============================================================

Option Explicit

 

Private Sub ReCalibrateButton_Click()

 

ActiveSheet.ChartObjects("EAMPVPMSChart").Activate

 

    With Application.ActiveChart.Axes(xlCategory, xlPrimary)

 

                .MinimumScale = wsInput.Range("Q2").Value

 

                .MaximumScale = wsInput.Range("R2").Value

 

                .MajorUnit = wsInput.Range("S2").Value

 

    End With

 

End Sub

==============================================================

When run, this algorithm unfortunately yields the following

error= "Compile Error: Variable not defined"

==============================================================

What have i missed or done wrong within this algorithm?

==============================================================

 

2 Replies
Highlighted

Hi Jack,

 

Alg. 1: I expect there is no sheet having a codename "EAMPVPMSChart". A worksheet can have two names, the one you see on the tab (shown as "Name" property) and the one you can use in code directly (shown as "(Name)" property).

Alg. 2: Not sure, it appears to work for me

Alg. 3: ThisWorksheet is not an existing object, you're looking for ActiveSheet

Alg.4: Not sure

Alg. 5: I suspect wsInput is the problem, it is treated as a variable and was not declared.

Highlighted

Alg 2: Works for me, make sure you set the xaxis to accept dates (numbers).