Question:
Hi,
I need to insert collapse bar for Product hierarchy in my planning layout.
Could you suggest the procedure to insert the collapse bar in Excel layout in BPS.
I appreciate your help
Thanks
kvr
Answer:
Sorry, my question is not clear may be. I need to hide and unhide few columns in Excel when i click one button. This button i have to provide using excel functions. if i click that button few columns should unhide and hide basically. I dont know what to use in Excel to achieve this. Did anyone try this before. Basically, this is for navigation purpose. It is an excel functionality as far i know. Looking for your suggestions.
I appreciate your help.
Thanks in advance.
kvr
Answer:
you need to use VBA to achieve this. create a button on your worksheet and write code to select the column you want and hide/unhide it. use the VBA help, it's very useful.
also, make sure your code unprotects the worksheet first as it will not work whilst the sheet is protected. there is already SAP sample code in "modul1" that shows you how to do this.
tht
Answer:
Hi tht
So far, i have inserted two buttons recording a macros, one for hiding and another for unhiding and then customizing those two macros with a button. I didnt write any code for this. The problem with this is, since i am customizing, it is appearing in all sheets. How can i make use of these buttons applicable to one specific worksheet. Do i need to write a VB code for this. where can i find that sample code. Do you mean , all worksheets in SEM should be unprotected. can you give me that sample code. Yeah, now i am wondering why my macros are also not working. Please guide me with that code as i am not familiar with VBA. could you send me that code and let me know the procedure to do that.
Thanks
kvr
Answer:
kvr
look at the code in "modul1". this VBA code is provided by SAP and used when data is loaded in the layout and when data is saved to the database.
you will see in the functions: Sub SAPAfterDataPut() and
Sub SAPBeforeDataGet() the code that unprotects the worksheet. you will also notice that it is only applicable for the worksheet "SEM-BPS 1". so if you have created your own worksheet then the protection does not apply to these.
therefore, if you are putting your buttons on the "SEM-BPS 1" worksheet make sure you add the code to unprotect and protect the worksheet in your macro that you have assigned to the buttons. if you are putting your buttons on your own worksheet then it is not necessary.
if you are planning to user macros for other purposes then i suggest you read up the documentation on the two functions i mentioned above. it is important to understand when these functions are called so you know where to put your code.
tht
Answer:
hi tht,
I have read those two modules and i have a password to protect and unprotect the workbook. I went through tool --> protecte sheet and gave password and unprotect the sheet. I recorded the macros and when i try customizing that marcos with a button using toolbar---> customize---> macros--> commands---> button, i am not able to assign the macros to it
in BPS layout. but i am able to do that in Excel sheet other than BPS.
Do you know the reason behind this and how i should approach . I have a similat functionality in other bps layouts. I have to implement this in my layout. I have seen macros for the other layouts. There is no code related to this hiding and unhiding columns using buttons.
I appreciate your help and suggestions
thanks in advance
kvr