+ Reply to Thread
Results 1 to 16 of 16

Using VBA with userform to edit parts of formulas

  1. 03-29-2013, 03:38 PM #1
    SChapman is offline
    Forum Contributor
    Join Date
    02-01-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    124

    Using VBA with userform to edit parts of formulas

    Hi,

    I am trying to create a userform to edit parts of formulas within a range of cells. I have similar formulas below in a range (X19:X30 'Adherence'), for example when the month is chosen in the combo box i.e April within the userform & the Performance Area is chosen as Adherence it changes the 0313 to 0413 within the current formula for that particular range (X19:X30). I have created the userform already & will appear when the 'Data Link' command button is clicked but I need someone to help me with the code to edit the formulas.

    I've attached the worksheet to make it easier to understand.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-29-2013, 05:23 PM #2
    xlbiznes is offline
    Valued Forum Contributor xlbiznes's Avatar
    Join Date
    02-22-2013
    Location
    Bahrain
    MS-Off Ver
    Excel 2007
    Posts
    1,223

    Re: Using VBA with userform to edit parts of formulas

    Hi,

    attached is my solution , hope this helps.
    Attached Files Attached Files
    Happy Computing ,

    Xlbiznes.

    To show your appreciation please click *
    Reply With QuoteRegister To Reply
  3. 03-30-2013, 03:06 AM #3
    SChapman is offline
    Forum Contributor
    Join Date
    02-01-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Using VBA with userform to edit parts of formulas

    At first glance this seems to be exactly what I was looking for, I'll check to make sure the links work correctly later today. Thanks a lot for your help!
    Reply With QuoteRegister To Reply
  4. 03-30-2013, 08:48 AM #4
    SChapman is offline
    Forum Contributor
    Join Date
    02-01-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Using VBA with userform to edit parts of formulas

    Is it possible it could be done for the 'Productivity' (AA19:AA30) column also? I need this to change '03-2013' to the month that has been selected in the userform & '2703-2013' to change to the date the formula is changed i.e if it was updated today 3003-2013 or tomorrow 3103-2013 etc.
    Reply With QuoteRegister To Reply
  5. 03-30-2013, 01:33 PM #5
    xlbiznes is offline
    Valued Forum Contributor xlbiznes's Avatar
    Join Date
    02-22-2013
    Location
    Bahrain
    MS-Off Ver
    Excel 2007
    Posts
    1,223

    Re: Using VBA with userform to edit parts of formulas

    Hi,

    it could be done. I hope the earlier one is doing what you wanted.
    Reply With QuoteRegister To Reply
  6. 03-30-2013, 01:48 PM #6
    SChapman is offline
    Forum Contributor
    Join Date
    02-01-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Using VBA with userform to edit parts of formulas

    Yes, what you did before was excellent, thanks again! If you have the time, do you think you could code what I need for Productivity?
    Reply With QuoteRegister To Reply
  7. 03-30-2013, 02:28 PM #7
    xlbiznes is offline
    Valued Forum Contributor xlbiznes's Avatar
    Join Date
    02-22-2013
    Location
    Bahrain
    MS-Off Ver
    Excel 2007
    Posts
    1,223

    Re: Using VBA with userform to edit parts of formulas

    @SChapman ,

    attached is the updated file to handle the second requirement. Let me know if it works fine.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 03-31-2013, 02:50 AM #8
    SChapman is offline
    Forum Contributor
    Join Date
    02-01-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Using VBA with userform to edit parts of formulas

    What you have done is great, I just need another part of the 'Productivity' formula changing. I require [3103-2013 Team & Div Report Cov.xls] that part to be changed also with the current date & month, I've added another combobox labelled 'Day' so the user can choose the date of the month. Unless you can code it so that it would know the date of the month automatically?

    Thanks again xlbiznes!
    Reply With QuoteRegister To Reply
  9. 03-31-2013, 07:46 AM #9
    xlbiznes is offline
    Valued Forum Contributor xlbiznes's Avatar
    Join Date
    02-22-2013
    Location
    Bahrain
    MS-Off Ver
    Excel 2007
    Posts
    1,223

    Re: Using VBA with userform to edit parts of formulas

    Hi,

    my code does that, it changes [3103 to the current day and current month in the column AA. Please check and let me know.
    Reply With QuoteRegister To Reply
  10. 03-31-2013, 12:54 PM #10
    SChapman is offline
    Forum Contributor
    Join Date
    02-01-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Using VBA with userform to edit parts of formulas

    No it doesn't appear to edit that part of the formula :/
    Reply With QuoteRegister To Reply
  11. 04-02-2013, 12:51 PM #11
    SChapman is offline
    Forum Contributor
    Join Date
    02-01-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Using VBA with userform to edit parts of formulas

    I've just re-checked this & it does appear to work correctly, I'm really sorry for my previous post. I was looking at it as if it will change to April,May etc but it's the current date it is changing to doh!

    Thanks for your work & sorry for the confusion!
    Reply With QuoteRegister To Reply
  12. 04-18-2013, 05:28 AM #12
    SChapman is offline
    Forum Contributor
    Join Date
    02-01-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Using VBA with userform to edit parts of formulas

    Hi xlbiznes,

    I've made a change to the formula's in column 24 & 27 (rows 19 to 30), you helped me previously with changing the forumlas automatically using a macro as above however I now don't require the full path to the file. I only require '[0413 Adherence.xlsx]' in the formula being changed to the current month & year, for column 24 I only require [1704-2013 Team & Div Report Cov.xls] being changed to the current date & month as opposed to the full path. Your code is looking to change the month/date in the full path which is causing a run time error in the code below....

    Please Login or Register  to view this content.
    I'm assuming it is due to the fact the month/date isn't being found in the full path anymore as it's now been changed. Is it possible you could take a look at this for me again or anyone else? I've attached the workbook.

    Thanks a lot!
    Attached Files Attached Files
    Last edited by SChapman; 04-18-2013 at 05:32 AM.
    Reply With QuoteRegister To Reply
  13. 04-19-2013, 12:25 PM #13
    SChapman is offline
    Forum Contributor
    Join Date
    02-01-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Using VBA with userform to edit parts of formulas

    Can anyone help with this? :/
    Reply With QuoteRegister To Reply
  14. 04-21-2013, 08:53 AM #14
    SChapman is offline
    Forum Contributor
    Join Date
    02-01-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Using VBA with userform to edit parts of formulas

    Bump......
    Reply With QuoteRegister To Reply
  15. 04-24-2013, 09:21 AM #15
    xlbiznes is offline
    Valued Forum Contributor xlbiznes's Avatar
    Join Date
    02-22-2013
    Location
    Bahrain
    MS-Off Ver
    Excel 2007
    Posts
    1,223

    Re: Using VBA with userform to edit parts of formulas

    @SChapman,

    Sorry , was totally out of this forum action as i was preoccupied with lot of other things. Where you able to resolve this ?
    Reply With QuoteRegister To Reply
  16. 04-25-2013, 11:20 AM #16
    SChapman is offline
    Forum Contributor
    Join Date
    02-01-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Using VBA with userform to edit parts of formulas

    Hi xlbiznes,

    No I still haven't resolved it, thanks for getting back to me. I appreciate you will have other things to do so it's no problem. Any help is much appreciated
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1